Extracting rows from an array

  • Thread starter Thread starter rbrychckn
  • Start date Start date
R

rbrychckn

I have an array (A3:K100) that i'd like to extract rows from.
Specifically, if J3:J100="Discontinued", I want to list all of these
rows (and all of the row's contents from A to K) on a different sheet.


I've been able to get an array with something like
{"","","","","Discontinued","","Discontinued",..} but the ""'s are
stumping me... I don't know how to just list these rows.

Any help is appreciated!
 
Use an advanced filter (Data | Filter > Advanced Filter...) or use MS
Query (Data | Import External Data > New database query...) to query
the table in the first sheet and extract only those rows where
J='discontinued'. For an example of using MS Query with an XL data
source see the Excel/Tutorials/'RDBMS in Excel' page of my web site.


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Dave,
That is exactly what I was looking for. This works well, but I wonder
if there is a way to do this without having that intermediate column in
your sheet (the listing of flagged row numbers). I'll try to work
through that. Thanks a bunch.

Tom
 
Maybe. I don't know. My attitude to such things is that XL allows 256
columns and a large number of worksheets. Use an extra column and, if
necessary for aesthetic appeal, hide it or make it a column "way out
there."

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
A followup question:

Now that I've established this listing on each of my worksheets, is
there a way to consolidate all "discontinued" entries onto one sheet?

Example:
Sheet1
Discontinued A 1 2
Discontinued A 4 12

Sheet2
Discontinued C 3 3
Discontinued D 4 4
Discontinued E 5 5

Output Sheet
Discontinued A 1 2
Discontinued A 4 12
Discontinued C 3 3
Discontinued D 4 4
Discontineud E 5 5
 
Back
Top