Grouping in one row

  • Thread starter Thread starter Johan Ibrahim
  • Start date Start date
J

Johan Ibrahim

Hi

I have following problem, i have 5 columns of data, the first column
is ID number. There can be many rows with the same ID. What I want to
achieve is to group values from one ID in one row.

For example I have:

a,cat,excel,12, ,4
a,2,4, ,fire
a, ,fire,543,12
b,qwerty,six,alpha,3
b,34,enter, ,3
c,with,sober,lax,23
c,2, ,4,3

and want to get:

a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
b,qwerty,six,alpha,3,34,enter, 3
c,with,sober,lax,23,2, 4,3

Can this be done in excel or vba?
 
Hi

I have following problem, i have 5 columns of data, the first column
is ID number. There can be many rows with the same ID. What I want to
achieve is to group values from one ID in one row.

For example I have:

a,cat,excel,12, ,4
a,2,4, ,fire
a, ,fire,543,12
b,qwerty,six,alpha,3
b,34,enter, ,3
c,with,sober,lax,23
c,2, ,4,3

and want to get:

a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
b,qwerty,six,alpha,3,34,enter, 3
c,with,sober,lax,23,2, 4,3

Can this be done in excel or vba?
Click to expand...

It could certainly be done in VBA. How difficult it would be
depends on if the sorting in your example is dependable.
That is, is the ID column always sorted? It's a fairly
straightforward thing then. You just move the cells up
when the ID is the same as the previous row. This would
seem to produce more than five columns. Is that OK?
And it seems to not bother about duplicates. Is that OK?
Socks
 
Hi

I have following problem, i have 5 columns of data, the first column
is ID number. There can be many rows with the same ID. What I want to
achieve is to group values from one ID in one row.

For example I have:

a,cat,excel,12, ,4
a,2,4, ,fire
a, ,fire,543,12
b,qwerty,six,alpha,3
b,34,enter, ,3
c,with,sober,lax,23
c,2, ,4,3

and want to get:

a,cat,excel,12, ,4,2,4, ,fire, ,fire,543,12
b,qwerty,six,alpha,3,34,enter, 3
c,with,sober,lax,23,2, 4,3

Can this be done in excel or vba?
Click to expand...

Sub columnstorowsSAS()
Dim i As Long
Dim slc As Long
Dim dlc As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i + 1, 1) = Cells(i, 1) Then
slc = Cells(i + 1, Columns.Count).End(xlToLeft).Column
'MsgBox slc
dlc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
'MsgBox dlc
Cells(i + 1, 1).Resize(, slc).Copy Cells(i, dlc)
Rows(i + 1).Delete
End If
Next i
End Sub
 
Back
Top