Formatting a table

[Twisty1980]

Hello,

Currently I have data in the form:

a ball
b hat
a cat
c dog

I want to show this using a macro/vba code:

item1 item2
a ball cat
b hat
c dog

Either excel or access would be fine, just can't figure out an automatic way
to do this.

Thanks,

Tristan

 

[Tim879]

Try using a pivot table.

 

[Twisty1980]

Have done won't work, pivot table doesn't know to assign item 1 etc to each
instance of a new item

 

[Gary''s Student]

Assuming that the source data is in a sheet called "s1" and the destination
in a sheets called "s2":

Sub reOrganize()
'
' gsnuxx
'
Set s1 = Sheets("s1")
Set s2 = Sheets("s2")
s2.Range("A1").Value = s1.Range("A1").Value
na = s1.Cells(Rows.Count, "A").End(xlUp).Row

n2 = 2
For i = 2 To na
v = s1.Cells(i, 1).Value
Set r = s1.Range("A1:A" & i)
If Application.WorksheetFunction.CountIf(r, v) = 1 Then
s2.Cells(n2, 1).Value = v
n2 = n2 + 1
End If
Next

For i = 1 To n2 - 1
j = 2
v = s2.Cells(i, 1).Value
For k = 1 To na
w = s1.Cells(k, 1).Value
If v = w Then
s2.Cells(i, j).Value = s1.Cells(k, 2).Value
j = j + 1
End If
Next
Next
End Sub

 

[Twisty1980]

Thanks very much,

last question if I wanted to put in a criteria to group when both columns a
and b are the same. how should I go about modifying the code:

eg

first name second name item
john G ball
john c cat
john G mouse

result:

first name second name item1 item2
john G ball cat

Thanks again

Tristan