Transpose? based on common values

[63falcondude]

Hey guys, I am looking to make a list of this form:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product Number[/TD]
[TD="align: center"]Avail Colors[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]62114[/TD]
[TD="align: center"]Blue[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]62114[/TD]
[TD="align: center"]Orange[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]62114[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]62895[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]62951[/TD]
[TD="align: center"]Purple[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]62951[/TD]
[TD="align: center"]Green[/TD]
[/TR]
</tbody>[/TABLE]


Into a list of this form:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Product Number[/TD]
[TD="align: center"]Avail Colors[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]62114[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Orange[/TD]
[TD="align: center"]Red[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]62895[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]62951[/TD]
[TD="align: center"]Purple[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Basically doing a transpose of the Avail Colors for each Product Number.

Any help with this is much appreciated.

 

[Rick Rothstein]

What you wanted to do is physically change your existing table to the transposed table in place (that is, the new table overwrites the existing table), correct? If so, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub RearrangeAvailableColors()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("A2:A" & LastRow) = Evaluate("IF(A2:A" & LastRow & "=A1:A" & LastRow - 1 & ","""",A2:A" & LastRow & ")")
  For Each Ar In Range("A2:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar.Offset(-1, 1).Resize(, Ar.Count + 1) = Application.Transpose(Ar.Offset(-1, 1).Resize(Ar.Count + 1))
  Next
  Range("A2:A" & LastRow).SpecialCells(xlBlanks).EntireRow.Delete
End Sub[/td]
[/tr]
[/table]

 

[63falcondude]

Wow... It worked great. Thank you!