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

Try using a pivot table.

On May 19, 8:45 am, Twisty1980
wrote:
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

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

"Tim879" wrote:

Try using a pivot table.

On May 19, 8:45 am, Twisty1980
wrote:
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

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

--
Gary''s Student - gsnu200787


"Twisty1980" wrote:

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

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200787


"Twisty1980" wrote:

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