Import entire row of data to other worksheet based on one lookup v

[mae_bear22]

I have a large set of data in one worksheet and I want to extract out
specific rows into another tab based on an identified value for each row.
The master worksheet is all customer orders from all over the USA. The rest
of the tabs are by state. For the Michigan Tab i'd like to use a lookup
statement that would copy the entire row of data for any orders from the
master onto the Michigan tab which have "MI" in the state column.

Thanks,

Stephanie

 

[Bernie Deitrick]

Stephanie,

There is no need to do that. The best way is to simply use data filters, and
filter your data based on the column with states - show MI only, and it is
the same as what you want, with the added benefit of smaller file size, no
worry about correct updating of data / formulas, etc.

HTH,
Bernie
MS Excel MVP

 

[mae_bear22]

If there is a way to do it, I'd like to know. I dont separate it by all 50
states, just by about 10 or so keeping the file size reasonable.

I know how to use filters, however I really need this data on separate tabs.

Any advice is appreciated.

 

[Bernie Deitrick]

You could:

1) Apply the filter, then copy and paste the values onto a new sheet

2) use formulas like this array formula in cell A2

=INDEX(Data!$A$1:$Z$1000,SMALL(IF(Data!$E$2:$E$1000=$E$1,ROW(Data!$E$2:$E$1000)),ROWS(A$2:A2)),COLUMN(A$1))

where you enter MI into cell E1. Copy it across for as many as 26 columns
(or increase the Z reference) and down until it returns errors.

3) use a macro

Bernie

 

[mae_bear22]

Thank you!!!!

You could:

1) Apply the filter, then copy and paste the values onto a new sheet

2) use formulas like this array formula in cell A2

=INDEX(Data!$A$1:$Z$1000,SMALL(IF(Data!$E$2:$E$1000=$E$1,ROW(Data!$E$2:$E$1000)),ROWS(A$2:A2)),COLUMN(A$1))

where you enter MI into cell E1. Copy it across for as many as 26 columns
(or increase the Z reference) and down until it returns errors.

3) use a macro

Bernie