how to re-group the cells from vertical to horizontal

[Landa]

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!

 

[Teethless mama]

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,SMALL(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