"Unwrap" Excel list into table, to facilitate pivot

[SriramNA]

I have long lists compiled by users in rows, which look like this:
UserName1 Costcentre1, Costcentre2
UserName2 Costcentre3, Costcentre4, Costcentre5
UserName3 Costcentre6
UserName4 Costcentre7, Costcentre8, Costcentre9, Costcentre10
and so on.

I need to reconstruct this data so that I have this in tabular form like so:
Costcentre1 Username1
Costcentre2 Username1
Costcentre3 Username2
Costcentre4 Username2
etc., so that I can perform further pivot analysis.

What's the good way of doing this? I have more than a thousand rows.

 

[James Ravenswood]

Is Costcentre1, Costcentre2 in a single cell or two cells??

 

[SriramNA]

Single cell, separated by commas and perhaps spaces, but that's the least of
the problems, since I can always use "Text to columns" to split the entries
to different cells.

--
Sriram

"James Ravenswood" wrote in message

Is Costcentre1, Costcentre2 in a single cell or two cells??

 

[James Ravenswood]

This macro will take the source data in Sheet1 and re-organize it in Sheet2:

Sub ReOrganizer()

Dim s1 As Worksheet, s2 As Worksheet
Dim I As Long, J As Long, N As Long

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
N = s1.Cells(Rows.Count, 1).End(xlUp).Row
I = 1
J = 1

For I = 1 To N
u = s1.Cells(I, 1).Value
v = s1.Cells(I, 2).Value
ary = Split(v, ",")
For K = LBound(ary) To UBound(ary)
s2.Cells(J, 1) = ary(K)
s2.Cells(J, 2) = u
J = J + 1
Next
Next

End Sub

So if the data in Sheet1 is:
U1 asd,gdg,wrwe,yrtr
U2 cs,nd,tsr,zuy
U3 qe
U4 bgsg,uytde,bs,tyi

The data in Sheet2 would become:

asd U1
gdg U1
wrwe U1
yrtr U1
cs U2
nd U2
tsr U2
zuy U2
qe U3
bgsg U4
uytde U4
bs U4
tyi U4

 

[SriramNA]

Worked fast and like a charm! Thanks.

"James Ravenswood" wrote in message

This macro will take the source data in Sheet1 and re-organize it in Sheet2:

Sub ReOrganizer()