Various data in a row for dulicate fields in column

  • Thread starter Thread starter SAIKAT
  • Start date Start date
S

SAIKAT

Hi,

I have a set of data like below-

Col A COL B
aaa 1
aaa 10
aaa 19
aaa 37
aaa 46
aaa 55
bgyhu 29
bgyhu 65
cxs 35
cxs 71
ddbfh 2
ddbfh 11
ddbfh 20
ddbfh 38
ddbfh 47
ddbfh 56
edcsx 30
edcsx 66
hgf 28
hgf 64
jnhgv 8
jnhgv 17
jnhgv 26
jnhgv 44
jnhgv 53
jnhgv 62
kjds 6
kjds 15
kjds 24
kjds 42
kjds 51
kjds 60


Now I want to represent the data like this-

COL A COL B
aaa 1 10 19 37 46 55
bgyhu 29 29 65
cxs 35 35 71

I mean duplicates in col A should come only once with all its different
values by its side in a single row.Can this be done other than paste special,
transpose function, because for a huge array of data it is not possible to do
paste special and then transpose for a number of times
 
Hi,

I have a set of data like below-

Col A   COL B
aaa     1
aaa     10
aaa     19
aaa     37
aaa     46
aaa     55
bgyhu   29
bgyhu   65
cxs     35
cxs     71
ddbfh   2
ddbfh   11
ddbfh   20
ddbfh   38
ddbfh   47
ddbfh   56
edcsx   30
edcsx   66
hgf     28
hgf     64
jnhgv   8
jnhgv   17
jnhgv   26
jnhgv   44
jnhgv   53
jnhgv   62
kjds    6
kjds    15
kjds    24
kjds    42
kjds    51
kjds    60

Now I want to represent the data like this-

COL A    COL B
aaa     1       10      19      37      46      55
bgyhu   29      29      65                      
cxs     35      35      71                      

I mean duplicates in col A should come only once with all its different
values by its side in a single row.Can this be done other than paste special,
transpose function, because for a huge array of data it is not possible to do
paste special and then transpose for a number of times
Click to expand...

Here is a macro that will do the trick.

Press ALT+F11
Insert a User Module and paste the code below.

Sub Transpose()

Dim LastUsedRowinColA As Integer
Dim CurrentItem As String
Dim ColARange As Range
Dim tmpRange As Range
Dim ConsolidatedRow As Integer
Dim ConsolidatedColumn As Integer

ConsolidatedRow = 0
ConsolidatedColumn = 1
CurrentItem = ""
LastUsedRowinColA = Range("a65536").End(xlUp).Row

Set ColARange = Range("a2:a" & LastUsedRowinColA)

For Each tmpRange In ColARange

If CurrentItem <> tmpRange Then
ConsolidatedRow = ConsolidatedRow + 1
ConsolidatedColumn = 2
CurrentItem = tmpRange
End If
Worksheets("sheet2").Cells(ConsolidatedRow, 1) = CurrentItem
Worksheets("sheet2").Cells(ConsolidatedRow,
ConsolidatedColumn) = Cells(tmpRange.Row, 2)
ConsolidatedColumn = ConsolidatedColumn + 1
Next

End Sub

hth

Regards

David
 
This simple formulas play will also deliver the desired tranform-summarize
results

Assume source data as posted in sheet: x, in A2:B2 down.
Source data need not be sorted by col A

In D2: =IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",ROW()))
Copy down to cover the max expected extent of source data

In E1: =INDEX($A:$A,SMALL($D:$D,COLUMNS($A:A)))
Copy across as far as required to list all the uniques in col A

In E2: =IF($A2="","",IF($A2=E$1,ROW(),""))
Copy across/fill down to populate

The above creates the criteria table for the ensuing transformation

In another sheet, to extract the required results
In A2: =INDEX(x!$E$1:$IV$1,ROWS($1:1))
Copy down as far as required

In B2
=IF(COLUMNS($A:A)>COUNT(OFFSET(x!$D:$D,,MATCH($A2,x!$E$1:$IV$1,0))),"",INDEX(x!$B:$B,SMALL(OFFSET(x!$D:$D,,MATCH($A2,x!$E$1:$IV$1,0)),COLUMNS($A:A))))
Copy B2 across / fill down as far as required
This returns the exact results that you seek

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Back
Top