Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Help? with extracting unsorted data from a worksheet, no blank lin

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Help? with extracting unsorted data from a worksheet, no blank lin

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/ex...260381033.aspx

for a description of the techniques to accomplish this.
--
Gary''s Student - gsnu201001


"srg_rector" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Help? with extracting unsorted data from a worksheet, no blanklin

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Help? with extracting unsorted data from a worksheet, no blank

I went to the page you linked:
http://office.microsoft.com/en-us/ex...260381033.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) ???


"Gary''s Student" wrote:

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/ex...260381033.aspx

for a description of the techniques to accomplish this.
--
Gary''s Student - gsnu201001


"srg_rector" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Help? with extracting unsorted data from a worksheet, no blank

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:$A 1),"",LOOKUP((MAX($A$1:$A1)+1),Separate!$A$3:$A$30 0,Separate!A$3:Separate!A$300))
=IF(MAX(Separate!$A$2:Separate!$A$301)=MAX($A$1:$A 1),"",LOOKUP((MAX($A$1:$A1)+1),Separate!$A$3:$A$30 0,Separate!B$3:Separate!B$300)) etc.
Then fill down.

Thanks for the help!

"Ziggy" wrote:

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.

.

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
Extracting data from one worksheet to another? onedub Excel Discussion (Misc queries) 0 March 5th 08 03:47 PM
Extracting data into another worksheet louiscourtney Excel Discussion (Misc queries) 9 July 20th 07 12:18 PM
Extracting data from large worksheet Louise Excel Worksheet Functions 4 April 27th 07 03:17 PM
Extracting names from an unsorted list. Brian H Excel Worksheet Functions 7 March 3rd 07 10:42 PM
Extracting data from rows where one field is blank BillBurton57 Excel Discussion (Misc queries) 5 August 11th 06 04:57 PM


All times are GMT +1. The time now is 09:47 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"