Excel formula to get all values

[Closed]
Report
Posts
1
Registration date
Thursday November 12, 2009
Status
Member
Last seen
November 13, 2009
-
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
-
Hi Gurus,

My head is really spinning on this one I can't seem to make it work! I have tried the lookup functions and none seemed to work. Please do help me! Its better if I can do it by formula, but if it can't, I'm very much open to macros too, just please help me in making this work!!

Scenario:
I have 2 worksheets.
Sheet1 : Raw Data (10,000 entries)
Sheet2 : Summary

In Sheet1 I have 2 columns
Column A is the Name of Room Column B is the amenities
I have 400 room names but for this example, let us just take 2, Sycamore and Pearl
Ex.
Column A                            Column B
Sycamore                            Radio
Sycamore                            TV
Sycamore                            Bathtub
Sycamore                            Dining
Pearl                               Bathtub
Pearl                               Smoking Area
Pearl                               Living Room


Now in Sheet2, I have to have this format:
     Column A                      Column B
1    Sycamore                      Pearl
2    Radio                         Bathtub 
3    TV                            Smoking Area 
4    Bathtub                       Living Room
5    Dining


Please help me on this!!
Thank you so much in advance!

Cheers,
Marie

1 reply

Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
182
have a look at pivot tables.
highlight the data, using the top row as the column titles
click on the insert tab on the ribbon. Click on the pivot table on the extreme left.
this will create a pivot table of your data. have a play with the results to see if it will display to your requirements.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!