Ideas to group data

[suryaanem]

Hi,

I have data that is not organized properly. I am trying to use excel to organize it in a way that will ease my analysis and have difficulty organizing it. Can anyone help

Category 	   Alphabets

Apples	         a,b
Oranges              a
Peaches 	         b
Bananas              a,b,c,d
Kiwis 	         b,c

I want to have the data sorted in the below way

a        Apples, Oranges,Bananas
b        Apples, Peaches, Bananas, Kiwis
c        Bananas, Kiwis
d        Bananas

and also want it arranged like this (I meant apples under apples, Kiwis under kiwis..etc.,)

a        Apples, Bananas,                        Oranges,
b        Apples, Bananas,Peaches, Kiwis
c                   Bananas              Kiwis
d                   Bananas

I first tried to separate a,b,c into different columns then tried using a pivot table but I thought thats not the right way. Can someone help.

[JBeaucaire]

Attach a sample workbook that has BEFORE / AFTER examples in the workbook to show the exact process you're trying to complete or automate from the actual style of starting data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[suryaanem]

Hi Jerry,

I have added the attachment with before and after examples.

Thanks,
Surya

[JBeaucaire]

Clip1.jpg

The formula in F2, then copied down/across:
=IF(ISNUMBER(SEARCH($E2, INDEX($B:$B, MATCH(F$1,$A:$A, 0)))), F$1, "")

[suryaanem]

Thank you so much, it worked.

I thought we might need a macro and all that to get this.

Is there a good book you could suggest so I learn the intermediate and advanced level of excel ?

[JBeaucaire]

Heh, I use the internet/forums to research and learn on any particular needed Excel topic.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.