Parse a Text Based Report so Data can be used in VLookup

[jonwite]

I have a text based inventory report (43,000 items) that I have imported into excel, with all the data going into column A. The inventory item is on one row and the counts and values are on 2 other rows, each row without item number identification. Many of the items are serialized so the number of rows per item can be variable. The item number row contains the text "ITEM:" and the Totals row contains the text "TOTAL VALUE". Using DATA/FILTER/AUTOFILTER I added a formula to column k that would pick up the total for ITEM 1, for instance +a11, and then copied that down to all rows. Again using Autofilter I searched for all rows that did not contain the words "TOTAL VALUE" and ended up with hundreds of rows that had misc. data due the random number of serialized items in some of the inventory items, or just from page breaks.

How do I correct my formula to pick up only the next row with "TOTAL VALUE" in it for each item?

I tried using =IF(ISNUMBER(SEARCH("Total",A11)),A11,"") but it is only returning a blank.

Any help or new approaches would definitely be appreciated.

[TMS]

=--ISNUMBER(SEARCH("total value",A11))

gives a 1 if total value is found in cell A11, and gives a 0 if it is not.


Regards, TMS

[jonwite]

If that is how the formula responds than that formula is not what I need. I need a way of searching the following rows for "TOTAL VALUE" and than copying the cell from that row, column a, into the current row Col k. And then doing that for each succeeding row that has an "ITEM:" in it.

[TMS]

I think it may encourage suggestions if you post a sample workbook.

Regards, TMS

[jonwite]

I have uploaded a portion of my file as suggested. In column K I have added a simple formula to copy the "total values" data onto the same line as the item number. For most lines that returns the results "Total Value of Item ..." in the item row. However lines 66, 142, 167, etc. have others values sine the "Total" row for those items is not in the same relative position.

What I am trying to accomplish:
I would like the "Total Value" data on the same row as the "Item:" number. I will then use "Text to Column" and put the data into a usuable form so I can do a VLookup against another Excel database.

I hope this explains enough what I am trying to accomplish.