Help? with extracting unsorted data from a worksheet, no blank lin

[srg_rector]

I am using Office 2003. I have a large Excel data file that I need to pull
specific info from.

I want to be able to have a worksheet that automatically pulls
specific/limited data from the Main Worksheet to a number of Result
Worksheets. There can be no blank rows in the Result Worksheets. The
result worksheets can have ONLY the data from Columns M-Q in them.

I have looked at: lookup, vlookup, index, match, and other functions, but I
cannot figure out how to get this to work.

The NGP/CNF/CNT codes will not be grouped in the Main Worksheet; the Main
Worksheet MUST be sorted by Date: Descending.
The Result Worksheets must update themselves whenever the Main Worksheet
data changes.

Result Worksheet NGP:
Criteria to select data record: Main Worksheet Column G must have the text
"NGP"
Data to write to Result Worksheet NGP: Main Worksheet same data record
Columns M-Q
There can be no blank rows in the Result Worksheets.

Result Worksheet CNF:
Criteria to select data record: Main Worksheet Column H must have the text
"CNF"
Data to write to Result Worksheet CNF: Main Worksheet same data record
Columns M-Q
There can be no blank rows in the Result Worksheets.

Result Worksheet CNT:
Criteria to select data record: Main Worksheet Column I must have the text
"CNT"
Data to write to Result Worksheet CNT: Main Worksheet same data record
Columns M-Q
There can be no blank rows in the Result Worksheets.

Please tell me how to write these worksheets!

Thanks!!!
Sheri

 

[Gary''s Student]

The general idea is to first identify all the rows to be extracted and
extract the data. Something like VLOOKUP() (as you are aware) will find
first the FIRST occurance on data like "GNP" on the Main sheet and return the
associated data, but you need all the rows matching "GNP"

See:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

for a description of the techniques to accomplish this.

 

[Ziggy]

Sheri,

I insert a test colum into the data table that indentifies whether or
not that data row contains the criteria. (Test column is A, Data
Column is D) . A1 = Required data extraction (CNT) I also add a
counter.

If(D5 = $A$1, Max($A2:A4)+1,"")

This puts a numbering sequence into column A that meets all of the
criteria.

On the Result sheet you have numbers 1.... XXX and either INDEX/MATCH
or VLOOKUP to pull in the results. Eliminate blanks with
IF(ISERROR(...

There are probably better ways but this is my way.

Hope it helps.

 

[srg_rector]

I went to the page you linked:
http://office.microsoft.com/en-us/excel/HA012260381033.aspx

The page referred to the command: enter the following array formula
(CTRL+SHIFT+ENTER)
However, nothing happened when I pressed those keys together. The formulas
could not be entered without Excel saying there were errors in them.

What is this (CTRL+SHIFT+ENTER) ???

 

[srg_rector]

Thank you for your help. I wasn't able to figure out exactly what you ment
by Eliminate blanks with IF(ISERROR(...

but I eventually got it to work by creating a intermediate worksheet between
the result worksheet and the origional.
intermediate worksheet: I gathered the data fields and used the first
column as an index. There were blank lines in this worksheet.
=IF('All Articles'!$H2 = $A$2, MAX(A$3:$A3)+1,"")
=IF('All Articles'!$H2 = $A$2,'All Articles'!N2,"") etc.
Then fill down.

result worksheet: I used lookup on each index number to remove the blank
lines
=IF(MAX(Separate!$A$2:Separate!$A$301)=MAX($A$1:$A1),"",LOOKUP((MAX($A$1:$A1)+1),Separate!$A$3:$A$300,Separate!A$3:Separate!A$300)
=IF(MAX(Separate!$A$2:Separate!$A$301)=MAX($A$1:$A1),"",LOOKUP((MAX($A$1:$A1)+1),Separate!$A$3:$A$300,Separate!B$3:Separate!B$300)) etc.
Then fill down.

Thanks for the help!