Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from one worksheet to another? | Excel Discussion (Misc queries) | |||
Extracting data into another worksheet | Excel Discussion (Misc queries) | |||
Extracting data from large worksheet | Excel Worksheet Functions | |||
Extracting names from an unsorted list. | Excel Worksheet Functions | |||
Extracting data from rows where one field is blank | Excel Discussion (Misc queries) |