| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup last value that does not equal certain value
ok I have a data sheet that i need to work with that is exported from
another program, the only problem is the format of the data is useless. eg: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 MTR000000047 AGGC00 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 MTR000000392 ALL100 MTR000000392 what i need is a formula that will give me the second value, eg i want it to look like this: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 7TL00829 MTR000000047 AGGC00 7TL00829 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 this data is all on one sheet, and there is always 3 blank rows between each set, does anyone know of a look up or index fn that would give me this effect? Cheers |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup last value that does not equal certain value
For the rows to the right of where you want to include the data from the
second line - will it always begin w/ "MTR" (I'm assuming yes). First, backup your data. If your data is not in 2 columns, I would split it into 2 columns using Data/Text To Columns, select fixed width and split it so that ALCA00 and MTR000000048 are in separate columns (it looks like your data will split easily). Then, insert a column. Ensure the data begins on row 1 (I will assume the data starts in A1, so column B is empty, and column C contains the data w/"MTR" in it). In B1 use this formula (using Cntrl+Shift+Enter - if done properly excel will put braces { } around the formula): =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,IF(COUN TBLANK($A$1:A1),LOOKUP(ROW(),IF($A$1:A1="",ROW($A$ 1:A1),"")),0)+2),"") Then copy down your table. Then, if you want, copy column B and click Edit/Paste Special Values, which will hardcode the data. If needed, you could then concatenate your data back into one column using =TRIM(A1&" "&B1&" "&C1) copied down, then Copy/Paste Special/Values to hardcode it. Then you could delete whatever you don't need. " wrote: ok I have a data sheet that i need to work with that is exported from another program, the only problem is the format of the data is useless. eg: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 MTR000000047 AGGC00 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 MTR000000392 ALL100 MTR000000392 what i need is a formula that will give me the second value, eg i want it to look like this: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 7TL00829 MTR000000047 AGGC00 7TL00829 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 this data is all on one sheet, and there is always 3 blank rows between each set, does anyone know of a look up or index fn that would give me this effect? Cheers |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup last value that does not equal certain value
Actually, that mess can be shortened to:
=IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,MAX(($A $1:A1="")*(ROW($A$1:A1)))+2),"") "JMB" wrote: For the rows to the right of where you want to include the data from the second line - will it always begin w/ "MTR" (I'm assuming yes). First, backup your data. If your data is not in 2 columns, I would split it into 2 columns using Data/Text To Columns, select fixed width and split it so that ALCA00 and MTR000000048 are in separate columns (it looks like your data will split easily). Then, insert a column. Ensure the data begins on row 1 (I will assume the data starts in A1, so column B is empty, and column C contains the data w/"MTR" in it). In B1 use this formula (using Cntrl+Shift+Enter - if done properly excel will put braces { } around the formula): =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,IF(COUN TBLANK($A$1:A1),LOOKUP(ROW(),IF($A$1:A1="",ROW($A$ 1:A1),"")),0)+2),"") Then copy down your table. Then, if you want, copy column B and click Edit/Paste Special Values, which will hardcode the data. If needed, you could then concatenate your data back into one column using =TRIM(A1&" "&B1&" "&C1) copied down, then Copy/Paste Special/Values to hardcode it. Then you could delete whatever you don't need. " wrote: ok I have a data sheet that i need to work with that is exported from another program, the only problem is the format of the data is useless. eg: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 MTR000000047 AGGC00 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 MTR000000392 ALL100 MTR000000392 what i need is a formula that will give me the second value, eg i want it to look like this: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 7TL00829 MTR000000047 AGGC00 7TL00829 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 this data is all on one sheet, and there is always 3 blank rows between each set, does anyone know of a look up or index fn that would give me this effect? Cheers |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup last value that does not equal certain value
JMB wrote: Actually, that mess can be shortened to: =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,MAX(($A $1:A1="")*(ROW($A$1:A1)))+2),"") "JMB" wrote: For the rows to the right of where you want to include the data from the second line - will it always begin w/ "MTR" (I'm assuming yes). First, backup your data. If your data is not in 2 columns, I would split it into 2 columns using Data/Text To Columns, select fixed width and split it so that ALCA00 and MTR000000048 are in separate columns (it looks like your data will split easily). Then, insert a column. Ensure the data begins on row 1 (I will assume the data starts in A1, so column B is empty, and column C contains the data w/"MTR" in it). In B1 use this formula (using Cntrl+Shift+Enter - if done properly excel will put braces { } around the formula): =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,IF(COUN TBLANK($A$1:A1),LOOKUP(ROW(),IF($A$1:A1="",ROW($A$ 1:A1),"")),0)+2),"") Then copy down your table. Then, if you want, copy column B and click Edit/Paste Special Values, which will hardcode the data. If needed, you could then concatenate your data back into one column using =TRIM(A1&" "&B1&" "&C1) copied down, then Copy/Paste Special/Values to hardcode it. Then you could delete whatever you don't need. " wrote: ok I have a data sheet that i need to work with that is exported from another program, the only problem is the format of the data is useless. eg: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 MTR000000047 AGGC00 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 MTR000000392 ALL100 MTR000000392 what i need is a formula that will give me the second value, eg i want it to look like this: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 7TL00829 MTR000000047 AGGC00 7TL00829 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 this data is all on one sheet, and there is always 3 blank rows between each set, does anyone know of a look up or index fn that would give me this effect? Cheers Absolutly awesome, thank you very much. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup last value that does not equal certain value
You're welcome.
" wrote: JMB wrote: Actually, that mess can be shortened to: =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,MAX(($A $1:A1="")*(ROW($A$1:A1)))+2),"") "JMB" wrote: For the rows to the right of where you want to include the data from the second line - will it always begin w/ "MTR" (I'm assuming yes). First, backup your data. If your data is not in 2 columns, I would split it into 2 columns using Data/Text To Columns, select fixed width and split it so that ALCA00 and MTR000000048 are in separate columns (it looks like your data will split easily). Then, insert a column. Ensure the data begins on row 1 (I will assume the data starts in A1, so column B is empty, and column C contains the data w/"MTR" in it). In B1 use this formula (using Cntrl+Shift+Enter - if done properly excel will put braces { } around the formula): =IF(ISNUMBER(FIND("MTR",C1)),INDEX($A$1:A1,IF(COUN TBLANK($A$1:A1),LOOKUP(ROW(),IF($A$1:A1="",ROW($A$ 1:A1),"")),0)+2),"") Then copy down your table. Then, if you want, copy column B and click Edit/Paste Special Values, which will hardcode the data. If needed, you could then concatenate your data back into one column using =TRIM(A1&" "&B1&" "&C1) copied down, then Copy/Paste Special/Values to hardcode it. Then you could delete whatever you don't need. " wrote: ok I have a data sheet that i need to work with that is exported from another program, the only problem is the format of the data is useless. eg: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 MTR000000047 AGGC00 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALCA00 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 MTR000000392 ALL100 MTR000000392 what i need is a formula that will give me the second value, eg i want it to look like this: AGGC00 AGGCON - WEE WAA NSW 7TL00829 AGGC00 7TL00829 MTR000000047 AGGC00 7TL00829 MTR000000047 ALCA00 ALCAN GOVE Gove NT 3PR00566 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALCA00 3PR00566 MTR000000048 ALL100 Alliance - Whim Creek Via Pt Hedland WA 2TW00159 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 ALL100 2TW00159 MTR000000392 this data is all on one sheet, and there is always 3 blank rows between each set, does anyone know of a look up or index fn that would give me this effect? Cheers Absolutly awesome, thank you very much. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Boxes equal to a value and totalled | Excel Discussion (Misc queries) | |||
| Lookup "greater than or equal to" in lookup array | New Users to Excel | |||
| lookup more than one cell | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
