Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Import entire row of data to other worksheet based on one lookup v


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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Import entire row of data to other worksheet based on one lookup v

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" wrote in message
...

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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Import entire row of data to other worksheet based on one look

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" wrote:

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" wrote in message
...

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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default Import entire row of data to other worksheet based on one look

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$100 0=$E$1,ROW(Data!$E$2:$E$1000)),ROWS(A$2:A2)),COLUM N(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" wrote in message
...
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" wrote:

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" wrote in message
...

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




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Import entire row of data to other worksheet based on one look

Thank you!!!!

"Bernie Deitrick" wrote:

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$100 0=$E$1,ROW(Data!$E$2:$E$1000)),ROWS(A$2:A2)),COLUM N(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" wrote in message
...
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" wrote:

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" wrote in message
...

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






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 915
Default Import entire row of data to other worksheet based on one lookupv

mae_bear22 wrote:
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


Another option is to use the seldom-leveraged MSQUERY tool, which can be
revealed in the Data | Import External Data menu. With this you can
write a SQL statement against the main table where a parameter criterion
filters on "state", then on each state page point the parameter to a
control cell that contains the desired filter value.

The beauties of the technique a
- You never have to worry about where and when to fill a formula. When
you refresh (which can be done with one click), the data will fill the
ranges as needed.
- No added formulas consuming recalc cycles in your workbook. It's all
static data until you initiate the refresh.

And it isn't hard to do (when you know how of course). I set one up in
one minute. I can post a workbook that demonstrates this if anyone is
interested.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Specify a row based on data from a cell to delete entire row..... Fadedmartinikiss Excel Discussion (Misc queries) 0 March 11th 09 09:39 PM
Move entire row to another worksheet based on drop list selection Coyote Excel Worksheet Functions 0 February 2nd 07 06:52 PM
Conditional Formating based on data in an entire row ked Setting up and Configuration of Excel 1 January 9th 07 07:35 PM
import worksheet based on a selection John Casteel Excel Discussion (Misc queries) 0 June 15th 06 06:48 AM
lookup single value in entire worksheet [email protected] Excel Worksheet Functions 2 April 13th 06 08:18 PM


All times are GMT +1. The time now is 07:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"