HI,

I have following data which is rowwise

CLIENT_DEMAT A/C ISIN QUANTITY
13448047 INE245C01019 268
13448047 INE271B01025 63
13448047 INE703B01027 178
13448047 INE703B01027 137
13451500 INE090A01013 13
13451500 INE245C01019 12
13448055 INE090B01013 23
13448055 INE245D01019 32

Since the first four demat account number is same, the other colums should
repeat on same row and then in second row next account number should appear
and so on so forth..
I would like to have the above data rearranged as under;

CLIENT_DEMAT A/C ISIN QUANTITY ISIN QUANTITY ISIN QUANTITY ISIN QUANTITY
13448047 INE245C01019 268 INE271B01025 63 INE703B01027 178 INE703B01027 137
13451500 INE090A01013 13 INE245C01019 12
13448055 INE090B01013 23 INE245D01019 32

(data is not shown correctly the same needs to be copied to notepad)

How to write a program in VBA to arrive at this, please help

Sub CombineClients()

RowCount = 2
NewCol = 4
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then
ISIN = Range("B" & (RowCount + 1))
QUANTITY = Range("C" & (RowCount + 1))
Cells(RowCount, NewCol) = ISIN
Cells(RowCount, NewCol + 1) = QUANTITY
If Cells(1, NewCol) = "" Then
Cells(1, NewCol) = "A/C ISIN"
Cells(1, NewCol + 1) = "QUANTITY"
End If
Rows(RowCount + 1).Delete
NewCol = NewCol + 2
Else
NewCol = 4
RowCount = RowCount + 1
End If
Loop

End Sub


"Vijay Kotian" wrote:

HI,

I have following data which is rowwise

CLIENT_DEMAT A/C ISIN QUANTITY
13448047 INE245C01019 268
13448047 INE271B01025 63
13448047 INE703B01027 178
13448047 INE703B01027 137
13451500 INE090A01013 13
13451500 INE245C01019 12
13448055 INE090B01013 23
13448055 INE245D01019 32

Since the first four demat account number is same, the other colums should
repeat on same row and then in second row next account number should appear
and so on so forth..
I would like to have the above data rearranged as under;

CLIENT_DEMAT A/C ISIN QUANTITY ISIN QUANTITY ISIN QUANTITY ISIN QUANTITY
13448047 INE245C01019 268 INE271B01025 63 INE703B01027 178 INE703B01027 137
13451500 INE090A01013 13 INE245C01019 12
13448055 INE090B01013 23 INE245D01019 32

(data is not shown correctly the same needs to be copied to notepad)

How to write a program in VBA to arrive at this, please help