Formatting Inconsistent Source Data

[SkiingCPA]

I'm trying to solve a problem to automate the formatting of some inconsistent source data we download daily for a partner.


The source data is formatted like below. For the all lines we need to pull data into a simplified table. Ex. first line we need to pull the invoice # (11753195), Vendor # (100379), Invoice amount ($2,836.71) and description (invoice Closed) into a simple table to help us upload into our ERP. This seems to be difficult due to the inconsistent output format, does anyone have suggestions on how to get me started here?
[TABLE="width: 1024"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XXXX SHOP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11753195 (100379)[/TD]
[TD][/TD]
[TD="align: right"]2,836.71[/TD]
[TD]Invoice Closed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11753195 (100379)[/TD]
[TD][/TD]
[TD][/TD]
[TD]597-Short Pay (Inv/Ck Difference)[/TD]
[TD][/TD]
[TD="align: right"]0.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11756759 (100379)[/TD]
[TD][/TD]
[TD="align: right"]262.71[/TD]
[TD]Invoice Closed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL:[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3,099.42[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.03[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAYMENT #: 9708[/TD]
[TD][/TD]
[TD]AMOUNT: 3,099.39[/TD]
[TD][/TD]
[TD][/TD]
[TD]DEPOSIT DATE: 10/19/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YYYY SHOP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11756130 (C000308)[/TD]
[TD][/TD]
[TD="align: right"]717.43[/TD]
[TD]Invoice Closed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOTAL:[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]717.43[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

try Power Query aka Get&Transform