Cleaning up messy data from PDF report, need to match up purchase order numbers with supplier names on 2 separate rows

[Tosborn]

The messy data looks like the list in columns A & B (see image), the finished list needs to be like columns D&E. I'm cleaning up some very messy data with formulas on the spreadsheet up to this point but the solution could be with VBA also, I'm open to either.

So basically the purchase order number relates to the supplier below and the reverse is true also, the supplier name relates to the purchase order above.

The kicker is the supplier names repeated multiple times along the spreadsheet all the way down but always relate to the purchase order immediately in a non blank row above. That is, a supplier relates to multiple purchase orders but each purchase order only relates to one supplier.

Purchase order numbers are not repeated.

There is no mathematical consistency to the blank rows between PO & supplier.

 

[Toadstool]

Hi Tosborn,

To simplify the formulae somewhat I've added a PO Row column.

Tosborn.xlsx
	A	B	C	D	E	F	G	H
1	Messy data:						Desired finished list:
2	Purchase Order	Supplier				PO Row	Purchase Order	Supplier
3	18760-00					1	18760-00	Supplier 9
4						6	18869-00	Supplier 6
5						10	18877-00	Supplier 7
6		Supplier 9
7
8	18869-00
9		Supplier 6
10
11		Supplier 6
12	18877-00
13
14
15		Supplier 7
Sheet1

Cell Formulas
Range	Formula
F3:F12	F3	=IFERROR(AGGREGATE(15,6,ROW($A$3:$A$999)-ROW($A$2)/($A$3:$A$999<>""),ROW()-ROW($F$2)),"")
G3:G12	G3	=IF(F3="","",INDEX($A$3:$A$999,F3))
H3:H12	H3	=IFERROR(INDEX($B$3:$B$999,AGGREGATE(15,6,ROW($B$3:$B$999)-ROW($B$2)/(($B$3:$B$999<>"")*(ROW($B$3:$B$999)>F3-ROW($B$2))),ROW()-ROW($F$2))),"")