Column Listing of Items from Table Data

[skydiver]

I am trying to create a report out of a table of data.
My data looks like this:

Item name | Price Level
Apple | Fruit
Orange | Fruit
Coke | Soda
Sprite |Soda
Budweiser | Dom Beer
Heineken | Import Beer

I need the report to look like this:

Fruit | Soda | Dom Beer | Import Beer
Apple | Coke |Budweiser | Heineken
Orange | Sprite

The data data has around 1000 items with 80+ price levels

I tried a pivot table but I can't get items to list to list inder the level instead of on the rows.
I tried doing index lookups but it quickly got very complicated to fill the rows under the price level heading because I didn't know how to easily vlookup or index lookup the required qty of items that had the price level for that column.
Any suggestions? I need to quickly be able to put this data from a DBF database into a worksheet table and automatically have the columnar report display. Filtering the source table is not a solution I want to visit because the report needs to be output multiple times and having to filter/copy/paste repeat 80 times will not work.

 

[sheetspread]

Try this:

http://www.mrexcel.com/forum/excel-questions/629202-search-help.html

 

[skydiver]

I am not understanding what you mean. If you are not trying to be helpful then why don't you keep your comments to yourself.
This is not an easy problem. I have searched and found others looking to solve similar problems. the crux of this is how do I create a dynamic list or an array from a subset of table data based the value of a column on a row then step through that array and display values from other columns from the matching rows and do this progressing through the rows on the dynamic array.
if you are so good, then please educated me.

 

[sheetspread]

Click on the link, it has a macro and formula solution for exactly what you've described

 

[skydiver]

Click on the link, it has a macro and formula solution for exactly what you've described

Sorry sheetspread, I didn't realize that was a link. I will check it our and see how it works.

 

[mirabeau]

I am trying to create a report out of a table of data.
My data looks like this:

Item name | Price Level
Apple | Fruit
Orange | Fruit
Coke | Soda
Sprite |Soda
Budweiser | Dom Beer
Heineken | Import Beer

I need the report to look like this:

Fruit | Soda | Dom Beer | Import Beer
Apple | Coke |Budweiser | Heineken
Orange | Sprite

The data data has around 1000 items with 80+ price levels

I tried a pivot table but I can't get items to list to list inder the level instead of on the rows.
I tried doing index lookups but it quickly got very complicated to fill the rows under the price level heading because I didn't know how to easily vlookup or index lookup the required qty of items that had the price level for that column.
Any suggestions? I need to quickly be able to put this data from a DBF database into a worksheet table and automatically have the columnar report display. Filtering the source table is not a solution I want to visit because the report needs to be output multiple times and having to filter/copy/paste repeat 80 times will not work.

macro, no need for fruits etc to be in consecutive order

Sub layouts()
Dim d As Object, a, c(), e()
Dim rws As Long, i As Long, q As Long, x As Long

Set d = CreateObject("scripting.dictionary")
a = Cells(1).CurrentRegion
rws = UBound(a, 1)
ReDim c(1 To rws, 1 To 1), e(1 To rws)

For i = 2 To rws
    a(i, 2) = Trim(a(i, 2))
    If d(a(i, 2)) = vbNullString Then
        d(a(i, 2)) = d.Count
        If d.Count > UBound(c, 2) Then ReDim Preserve c(1 To rws, 1 To d.Count)
        c(1, d.Count) = a(i, 2)
        c(2, d.Count) = a(i, 1)
        e(d.Count) = 2
     Else
        e(d(a(i, 2))) = e(d(a(i, 2))) + 1
        If e(d(a(i, 2))) > x Then x = e(d(a(i, 2)))
        c(e(d(a(i, 2))), d(a(i, 2))) = a(i, 1)
     End If
Next i

Range("D1").Resize(x, d.Count) = c

End Sub