M
MeatLightning
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
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