Formatting a table
 

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

Formatting a table
 

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

Formatting a table
 

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

Formatting a table
 

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

Formatting a table
 

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