Listing Multiple Rows from Match

[Sean Larkin]

I have a workbook, and the master sheet has data in many columns and rows.
Column B is the type field, and every row has been given a type of either,
Direct, National, or Dealer. The master sheet will be updated regularly
(weekly or monthly). I want to minimize the work required to update the
workbook, so the ideal solution is for just the master sheet to be updated
manually, and the rest of the sheets update themselves accordingly.

I want to have 3 additional sheets, one for each type. I'd like each sheet
to automatically list rows of their corresponding type from the master
sheet.

For example, the Direct sheet will show all rows from the master sheet that
have the type, Direct.

Master sheet:
midwest Direct 12000 500 0 500 12500
northeast National 315 1354 0 454 55521
southwest Dealer 14540 200 2 11250 111250
midwest National 13245 321 0 312 748678
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234
northeast National 315 1354 0 454 24234
southwest National 4342 200 2 33423 234423
southwest Dealer 3424 200 2 45664 67898

-----------


Direct Sheet should be:
midwest Direct 12000 500 0 500 12500
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234


------

This example is very minimal, compared to the actual amount of data on the
master sheet. I have been looking up information of Lookups, Index/Match
and Advanced Filtering, but I just can't make it happen.

Does anyone have any ideas?

Thanks.

-Sean

 

[Frank Kabel]

Hi
if you have many rows of data I wouldn't use a formula approach (too slow).
Maybe try the following addin:
http://www.rondebruin.nl/easyfilter.htm

 

[Max]

One way to set-it up ..

In sheet named: Master
-------------------
Assume the sample table below is in cols A to G
data from row2 down
(with the key column "Type" in col B), viz:

Region___Type
midwest Direct 12000 500 0 500 12500
northeast National 315 1354 0 454 55521
southwest Dealer 14540 200 2 11250 111250
midwest National 13245 321 0 312 748678
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234
northeast National 315 1354 0 454 24234
southwest National 4342 200 2 33423 234423
southwest Dealer 3424 200 2 45664 67898

etc

List across in say, I1:K1
the 3 "Type"s: Direct, National, Dealer

Put in I2: =IF($B2="","",IF($B2=I$1,ROW(),""))

Copy I2 across to K2, then fill down by a safe "max"
number of rows that data is ever expected in cols A to G,
say, down to I10000?

In a new sheet named: Direct
---------------------------------------
Let's reserve cell A1 for the "Type" name

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: Direct into A1.
But you need to save the file first)

Copy > Paste the same col headers from "Master"
into row2 (Region, Type, ... etc in A2, B2 ...)

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$
K$1,0),"I:I","J:J","K:K")),ROW(A1)),INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,M
aster!$I$1:$K$1,0),"I:I","J:J","K:K")),0)),"",OFFSET(Master!$A$1,MATCH(SMALL
(INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$K$1,0),"I:I","J:J","K:K
")),ROW(A1)),INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$K$1,0),"I:I
","J:J","K:K")),0)-1,COLUMN(A1)-1))

Note: You'd need to correct / restore the couple of inadvertent line wraps /
line breaks when you copy > paste the above formula into A3

Copy A3 across to G3, fill down by as many rows as was done
in "Master"'s cols I:K, i.e. down to G10000 thereabouts

You'll see that cols A to G (in row3 down)
will auto-return the "filtered" rows from "Master"
for the Type: Direct
i.e. for the sample data-set above, it'll appear as:

midwest Direct 12000 500 0 500 12500
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234

Now, just duplicate / make a copy of the sheet: Direct,
rename it as: National
and you'll get the "filtered" rows for National:

northeast National 315 1354 0 454 55521
midwest National 13245 321 0 312 748678
northeast National 315 1354 0 454 24234
southwest National 4342 200 2 33423 234423

Repeat the sheet duplicating and renaming
for: Dealer and you'll get:

southwest Dealer 14540 200 2 11250 111250
southwest Dealer 3424 200 2 45664 67898

Data entered into "Master" will auto-appear
in each of the Types' sheet: Direct, National and Dealer

Adapt / extend to suit

 

[Alan Beban]

I have a workbook, and the master sheet has data in many columns and rows.
Column B is the type field, and every row has been given a type of either,
Direct, National, or Dealer. The master sheet will be updated regularly
(weekly or monthly). I want to minimize the work required to update the
workbook, so the ideal solution is for just the master sheet to be updated
manually, and the rest of the sheets update themselves accordingly.

I want to have 3 additional sheets, one for each type. I'd like each sheet
to automatically list rows of their corresponding type from the master
sheet.

For example, the Direct sheet will show all rows from the master sheet that
have the type, Direct.

Master sheet:
midwest Direct 12000 500 0 500 12500
northeast National 315 1354 0 454 55521
southwest Dealer 14540 200 2 11250 111250
midwest National 13245 321 0 312 748678
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234
northeast National 315 1354 0 454 24234
southwest National 4342 200 2 33423 234423
southwest Dealer 3424 200 2 45664 67898

-----------


Direct Sheet should be:
midwest Direct 12000 500 0 500 12500
midwest Direct 23546 324 0 123 67898
northeast Direct 315 1354 0 454 55521
northeast Direct 343 2678 0 433 234234


------

This example is very minimal, compared to the actual amount of data on the
master sheet. I have been looking up information of Lookups, Index/Match
and Advanced Filtering, but I just can't make it happen.

Does anyone have any ideas?

Thanks.

-Sean

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then
assuming that your data is in Columns A1:G1000 on a sheet named
"Master", array enter into A1:G1000 of each of the sheets "Direct",
"National", and "Dealer", respectively, the following:

=ArrayRowFilter1(Master!A1:G1000,2,"Direct")
=ArrayRowFilter1(Master!A1:G1000,2,"National")
=ArrayRowFilter1(Master!A1:G1000,2,"Dealer")

Alan Beban

 

[Harlan Grove]

One way to set-it up ..

In sheet named: Master
-------------------
Assume the sample table below is in cols A to G
data from row2 down
(with the key column "Type" in col B), viz:

etc

Don't need headers in these columns.

List across in say, I1:K1
the 3 "Type"s: Direct, National, Dealer

Put in I2: =IF($B2="","",IF($B2=I$1,ROW(),""))

Alternatively,

I2:
=IF($B2=I$1,COUNTIF(I$1:I1,">0")+1,"")

Then I2 filled right into J2:K2, then I2:K2 filled down as far as needed.
Name the entire range spanned by columns A through K from row 1 though the
bottommost record in columns A through G something like AMT.

The $B2="" test is pointless, btw.

In a new sheet named: Direct
---------------------------------------
Let's reserve cell A1 for the "Type" name

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: Direct into A1.
But you need to save the file first)

....

Alternatively, define the name WSN referring to

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

Put in A3:

=IF(ISERROR(MATCH(SMALL(INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,
Master!$I$1:$K$1,0),"I:I","J:J","K:K")),ROW(A1)),
INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$K$1,0),
"I:I","J:J","K:K")),0)),"",OFFSET(Master!$A$1,
MATCH(SMALL(INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,
Master!$I$1:$K$1,0),"I:I","J:J","K:K")),ROW(A1)),
INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$K$1,0),
"I:I","J:J","K:K")),0)-1,COLUMN(A1)-1))

....

INDIRECT("'Master'!"&CHOOSE(MATCH($A$1,Master!$I$1:$K$1,0),
"I:I","J:J","K:K"))

?!

OFFSET(Master!$H:$H,0,MATCH($A$1,Master!$I$1:$K$1,0))

is shorter and more efficient.

But continuing with my alternative setup,

A2:
=IF(COUNTIF(INDEX(AMT,0,MATCH(WSN,INDEX(AMT,1,0),0)),">0")>=ROW()-1,
LOOKUP(2,1/(INDEX(AMT,0,MATCH(WSN,INDEX(AMT,1,0),0))=ROW()-1),
INDEX(AMT,0,COLUMN())),"")

Fill A2 right into B2:G2, then fill A2:G2 down until it evaluates to "".

Now, just duplicate / make a copy of the sheet: Direct,
rename it as: National
and you'll get the "filtered" rows for National:

....

 

[Max]

Brilliant alternative, Harlan !
And many thanks for the refinement bits, too,
points well taken ..

 

[Sean Larkin]

Thank you Frank, Max, Alan and Harlan for the assistance!

 

[Max]

You're welcome !