Hi, I have a table like this:
column A(category) column B(items)
Fruit Apple
Fruit Orange
Fruit Lemon
Drinks Tea
Drinks Coffee
Snacks Candy
Snacks Chips
Snacks Chocolate

And I want to change the format of the table to
column A(category) column B(item 1) column C(item 2) column D (item 3) .....
Fruit Apple Orange Lemon
Drinks Tea Coffer
Snacks Candy Chips Chocolate

Is there any formula to do this? Your help is much appreciated!

Let's say your data from A1:B9
Header in row 1
Create name ranges
Category (name range) A2:A9
Items (name range) B2:B9

Data Filter Advanced Filter select Copy to another location List
range: $A$1:$a$9 Copy to: I choose loaction in $D$1 check the Unique
records only OK

You will have
D1: Category
D2: Fruit
D3: Drinks
D4: Snacks

In E2:
=IF(ISERR(SMALL(IF(Category=$D2,ROW(INDIRECT("1:"& ROWS(Items)))),COLUMNS($A:A))),"",INDEX(Items,SMAL L(IF(Category=$D2,ROW(INDIRECT("1:"&ROWS(Items)))) ,COLUMNS($A:A))))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy across and down


"Landa" wrote:

Hi, I have a table like this:
column A(category) column B(items)
Fruit Apple
Fruit Orange
Fruit Lemon
Drinks Tea
Drinks Coffee
Snacks Candy
Snacks Chips
Snacks Chocolate

And I want to change the format of the table to
column A(category) column B(item 1) column C(item 2) column D (item 3) .....
Fruit Apple Orange Lemon
Drinks Tea Coffer
Snacks Candy Chips Chocolate

Is there any formula to do this? Your help is much appreciated!