Filter / Lookup: Display record on new sheet based on category...
 

Hey all -
This one might be a little tricky. I need a formula that looks at data
on another sheet and displays records that match a given category (skipping
blank cells). Here's the format of sheet 1:
A B
item1 category1
item2 category2
item1 category1
item3 category3

My 2nd sheet has a header bar for each category with every record for that
category listed below it. I'd like to have a formula in the cells below the
header for each category that does all the heavy lifting (looks at sheet 1,
matches records to the category, skips blanks, and lists each individual
matching record - no dupes).

I've used the following formula before (in another workbook) to do all of
the above except search for / match a specific category (criteria)... i
haven't been able to edit it for this use:

=IF(ROWS($1:2)<=COUNTA(Sched!B:B),INDEX(Sched!B:B, SMALL(IF(Sched!B$1:B$999<"",ROW(Sched!B$1:B$999)-MIN(ROW(Sched!B$1:B$999))+1),ROWS($1:2))),"")

I don't want to use macros or filters if possible.

thanks in advance!
meat

Filter / Lookup: Display record on new sheet based on category...
 

One option which delivers ..

Assume source data (sample below) is in sheet: x*,
cols A and B, data from row2 down
item1 category1
item2 category2
item1 category1
etc

*I'd use very short source sheetnames, makes formulas look lean

In D1 across are listed the unique categories: category1, category2, etc
Put in D2:
=IF(OR($B2="",D$1=""),"",IF($B2=D$1,IF(SUMPRODUCT( ($A$2:$A2=$A2)*($B$2:$B2=$B2))1,"",ROW()),""))
Copy D2 across/fill down as far as required to cover the max expected extent
of source data

Then in a new sheet (your 2nd sheet),
In A1: =IF(x!D1="","",x!D1)
Copy A1 across as far as required to bring in all the categories from x

In A2:
=IF(ROWS($1:1)COUNT(OFFSET(x!$C:$C,,MATCH(A$1,x!$ D$1:$IV$1,0))),"",INDEX(x!$A:$A,SMALL(OFFSET(x!$C: $C,,MATCH(A$1,x!$D$1:$IV$1,0)),ROWS($1:1))))
Copy A2 across/fill down as far as required to cover the max expected number
of items per any single category. This will return the required results under
each category, with all results neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MeatLightning" wrote:
This one might be a little tricky. I need a formula that looks at data
on another sheet and displays records that match a given category (skipping
blank cells). Here's the format of sheet 1:
A B
item1 category1
item2 category2
item1 category1
item3 category3

My 2nd sheet has a header bar for each category with every record for that
category listed below it. I'd like to have a formula in the cells below the
header for each category that does all the heavy lifting (looks at sheet 1,
matches records to the category, skips blanks, and lists each individual
matching record - no dupes).

I've used the following formula before (in another workbook) to do all of
the above except search for / match a specific category (criteria)... i
haven't been able to edit it for this use:

=IF(ROWS($1:2)<=COUNTA(Sched!B:B),INDEX(Sched!B:B, SMALL(IF(Sched!B$1:B$999<"",ROW(Sched!B$1:B$999)-MIN(ROW(Sched!B$1:B$999))+1),ROWS($1:2))),"")

I don't want to use macros or filters if possible.

thanks in advance!
meat