Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/ INDEX and ROW reference using OR statements
I am working on a worksheet that has been imported into excel from an
antiquated mainframe and so the resulting excel worksheet carries the headers and footer from the mainframe. Basically, these header and footer prevent the worksheet from functioning like a data sheet. I want to filter out the rows with meaningful data. Each of the rows that contain meaningful data has a text field of 4 characters in column B. This is what Ive been working with: =INDEX(A$15:E$51, SMALL(IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN, ROW($1:$37)), ROW(15))) I dont understand how the second ROW call (ROW(15)) functions. If the data in column B is on the list (SDXS or BNDX or UNVI or BEER or WAGE or UBEN) I want to return all the data for that same row. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/ INDEX and ROW reference using OR statements
Why don't you create a helper column in which you test for the presence of
these four-character strings, and filter on that? If your data goes from column A to E, then in column F enter the formula =IF(OR(B1="SDXS",B1="BNDX",B1="UNVI",B1="BEER",B1= "WAGE"),1,"") Filter on the 1, copy the filtered records, and paste in a new spreadsheet? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "emonz" wrote: I am working on a worksheet that has been imported into excel from an antiquated mainframe and so the resulting excel worksheet carries the headers and footer from the mainframe. Basically, these header and footer prevent the worksheet from functioning like a data sheet. I want to filter out the rows with meaningful data. Each of the rows that contain meaningful data has a text field of 4 characters in column B. This is what Ive been working with: =INDEX(A$15:E$51, SMALL(IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN, ROW($1:$37)), ROW(15))) I dont understand how the second ROW call (ROW(15)) functions. If the data in column B is on the list (SDXS or BNDX or UNVI or BEER or WAGE or UBEN) I want to return all the data for that same row. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/ INDEX and ROW reference using OR statements
Assuming your formula was syntactically correct (which it isn't):
Whe IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN = TRUE An array of row numbers is passed to the SMALL function via: ROW($1:$37) Then: ROW(15) Would pass the 15th smallest row number to the INDEX function. Biff "emonz" wrote in message ... I am working on a worksheet that has been imported into excel from an antiquated mainframe and so the resulting excel worksheet carries the headers and footer from the mainframe. Basically, these header and footer prevent the worksheet from functioning like a data sheet. I want to filter out the rows with meaningful data. Each of the rows that contain meaningful data has a text field of 4 characters in column B. This is what I've been working with: =INDEX(A$15:E$51, SMALL(IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN, ROW($1:$37)), ROW(15))) I don't understand how the second ROW call (ROW(15)) functions. If the data in column B is on the list (SDXS or BNDX or UNVI or BEER or WAGE or UBEN) I want to return all the data for that same row. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/ INDEX and ROW reference using OR statements
Good idea - thanks much!
"Dave F" wrote: Why don't you create a helper column in which you test for the presence of these four-character strings, and filter on that? If your data goes from column A to E, then in column F enter the formula =IF(OR(B1="SDXS",B1="BNDX",B1="UNVI",B1="BEER",B1= "WAGE"),1,"") Filter on the 1, copy the filtered records, and paste in a new spreadsheet? Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "emonz" wrote: I am working on a worksheet that has been imported into excel from an antiquated mainframe and so the resulting excel worksheet carries the headers and footer from the mainframe. Basically, these header and footer prevent the worksheet from functioning like a data sheet. I want to filter out the rows with meaningful data. Each of the rows that contain meaningful data has a text field of 4 characters in column B. This is what Ive been working with: =INDEX(A$15:E$51, SMALL(IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN, ROW($1:$37)), ROW(15))) I dont understand how the second ROW call (ROW(15)) functions. If the data in column B is on the list (SDXS or BNDX or UNVI or BEER or WAGE or UBEN) I want to return all the data for that same row. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/ INDEX and ROW reference using OR statements
Let's assume that you want your results in Column G, starting at G15,
try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER... G15, copied down and across: =INDEX(A$15:A$51,SMALL(IF(ISNUMBER(MATCH($B$15:$B$ 51,{"SDXS","BNDX","UNVI ","BEER","WAGE","UBEN"},0)),ROW($B$15:$B$51)-ROW($B$15)+1),ROWS(G$15:G15) )) Hope this helps! In article , emonz wrote: I am working on a worksheet that has been imported into excel from an antiquated mainframe and so the resulting excel worksheet carries the headers and footer from the mainframe. Basically, these header and footer prevent the worksheet from functioning like a data sheet. I want to filter out the rows with meaningful data. Each of the rows that contain meaningful data has a text field of 4 characters in column B. This is what Ive been working with: =INDEX(A$15:E$51, SMALL(IF(B$15:B$51 = SDXS or BNDX or UNVI or BEER or WAGE or UBEN, ROW($1:$37)), ROW(15))) I dont understand how the second ROW call (ROW(15)) functions. If the data in column B is on the list (SDXS or BNDX or UNVI or BEER or WAGE or UBEN) I want to return all the data for that same row. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
INDEX function need to have col reference to be formula | Excel Worksheet Functions | |||
Now With Index -- IF Function Does Not Work With Cell Reference | Excel Worksheet Functions | |||
IF Statements-Circular Reference | Excel Worksheet Functions | |||
Data Validation w/ If, Match & Index Statements | Excel Worksheet Functions |