how to re-group the cells from vertical to horizontal
 

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!

how to re-group the cells from vertical to horizontal
 

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!