Returning consolidated values from indexing.

nosaj · #1 07-09-2009, 10:47 PM

Dear Gurus,

I would like to seek your advise on how to auto conlidate the data below into a summary sheet? I have data in column A and B. data in column a is repeated a few times. How am I able to consolidate the data into the results table? In the results section, "tb" will be manually keyed in and john and sarah will be the return values. Please help!! Thank you!

tb john
tb john
tb sarah
as ben
as ben
as jean
as jean
as jean

Results
tb john
sarah

as ben
jean

**rylo** · #2 07-09-2009, 11:08 PM

Hi

One way to do this is to use an advanced filter. You don't give any details on your structure, or where you want to output the data, but try this.

1) Your example data is in the range sheet1!A2:B9
2) Sheet1!A1:B1 has your header data (eg H1, H2)
3) Sheet2!A1: H1
4) Sheet2!A2: tb
5) Sheet2!A3: H2

Select Sheet2!A1:A2
Data, Filter, Advancedfilter.
Copy to another location
List Range: Sheet1!$A$1:$B$9
Criteria Range: Sheet2!$A$1:$A$2
Copy To: $A$4
Unique records Only: selected
OK.

This will bring back the entries for tb.

HTH

rylo

nosaj · #3 07-09-2009, 11:45 PM

Dear rylo,

Thank you for the answers! I tried using the pivot table to consolidate the answers. Thank you again!

**rylo** · #4 07-10-2009, 12:07 AM

Hi

OK. If that worked, can you please mark the post as SOLVED.

rylo

Welcome to the Excel Forum

Forum Statistics