Lookup last value that does not equal certain value

[collis47]

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

 

[JMB]

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(COUNTBLANK($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.

 

[JMB]

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),"")

 

[collis47]

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),"")

Absolutly awesome, thank you very much.

 

[JMB]

You're welcome.

Absolutly awesome, thank you very much.