Listing Multiple Rows from Match
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

Sean Larkin wrote:
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

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'!"&CHOOS E(MATCH($A$1,Master!$I$1:$
K$1,0),"I:I","J:J","K:K")),ROW(A1)),INDIRECT("'Mas ter'!"&CHOOSE(MATCH($A$1,M
aster!$I$1:$K$1,0),"I:I","J:J","K:K")),0)),"",OFFS ET(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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sean Larkin" wrote in message
...
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

Sean Larkin wrote:
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

"Max" wrote...
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
....
southwest Dealer 3424 200 2 45664 67898
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'!"&CHOO SE(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:
....

Brilliant alternative, Harlan !
And many thanks for the refinement bits, too,
points well taken ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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


On 12/18/04 7:15 AM, in article , "Max"
wrote:

Brilliant alternative, Harlan !
And many thanks for the refinement bits, too,
points well taken ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----