How to do 2 loops in Excel Macro

[accessfever]

I have a huge text file which I opened it in a spreadsheet. The data is not in database format. I want to manupulate the data and put them in right columns.

Currently, all data is in column A 1 thru A70000. I want to get the part numbers from column A in column D and the part numbers are not in each line. If the line doesn't have a part number, then I still want the line filled with the previous part number until a new part number.

C5 is a LEFT forumla (=Left(A5,4) to pick up the first 4 letters from A5.
D5 is to get the part number from A5 by mid function (Mid(A5,6,len(A5)-21) if column C5 is "Item".

The tricky thing is if A6 <> "Item" then D6 is to get the same part number from C5 until A? = "Item" then next row gets a new part number.

I tried to do two loops but failed.

Any help will be appreciated!

 

[Kiwiman]

Howzit
In D5 fry this formula

 =if(c5="item",mid(a5,6,len(a5)-21),d4)

This will use the part number in the previous row until a new item is found

 

[accessfever]

Excellent! This is exactly what I tried to accomplish.