Solved: Match Transpose data to a column Using VBA

[simora]

After running the Unique-Makes macro,I am trying to get each model on sheet1 to match the make on sheet2 so that eg.. for Ford I will get F150, Focus, & Mustang listed under Column 3 Headings.
See attached sheet.
Thanks

[simora]

OOPS!
I forgot to include the sheet. Now included.

[p45cal]

try this which includes making a unique list:
Sub blah()
Set DestSht = Sheets("Sheet2")
Set SourceSht = Sheets("Sheet1")
With SourceSht
Set SourceRng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With 'SourceSht
With DestSht
SourceRng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("A1"), Unique:=True
With .Range(.Cells(2, "A"), .Cells(.Rows.Count, 1).End(xlUp))
.Copy
DestSht.Range("B1").PasteSpecial Transpose:=True
.ClearContents
End With '.Range(.Cells(2, "A"), .Cells(.Rows.Count, 1).End(xlUp))
With .Range(.Cells(1, "B"), .Cells(1, .Columns.Count).End(xlToLeft))
For Each cll In .Cells
myOffset = 1
For Each celle In SourceRng.Cells
If celle.Value = cll.Value Then
cll.Offset(myOffset) = celle.Offset(, 1).Value
myOffset = myOffset + 1
End If
Next celle
Next cll
End With '.Range(.Cells(1, "B"), .Cells(1, .Columns.Count).End(xlToLeft))
End With 'DestSht
End Sub
A pivot table can give you similar results, in a different layout, quite quickly and without code.

[simora]

Thanks p45cal:
The code worked great, and as requested. I am not familiar with pivot tables. Have never worked with them.
When you said " in a different layout " does that mean that I would need to reconfigure my worksheet to accommodate the pivot table.

[p45cal]

It's easy to get the following layout:
8698 using…

[p45cal]

…this pivot table layout:
8699
which uses the range Sheet1!$A$1:$B$19 as the source data for the pivot.

Now hide the rightmost column of the pivot table, all subtotals etc. You're using xl2003 right?

[simora]

p45cal:
Thanks for the insight into using Pivot Tables. I will try this as time allows. Your explanation was very helpful and informative.
I AM using xl2003.