Parsing an inventory pdf to usable data

[Slizer6893]

Hello All,


I am working on reducing monthly reconciliation timing and I have a few ideas on how to do it but my VBA skills are not what they used to be or this is just over my head completely. Basically, I will receive in a stock sheet in a PDF which I will take and parse the text into excel and then manually clean it up to auto fill my inventory reconciliations and pull through...this can sometimes take 20-30 minutes and obviously can still have some errors.

Below is an example of the raw text with some data redacted for anonymity

[TABLE="width: 571"]
<tbody>[TR]
[TD]ITEM ID DESCRIPTION UOM VENDOR ITEM ID[/TD]
[/TR]
[TR]
[TD]TRANS# LOT ID BIN MFG'D EXPIRES CONTNRS UNITS WEIGHT HOLD[/TD]
[/TR]
[TR]
[TD]-------------------------------------------------------------------------------------------[/TD]
[/TR]
[TR]
[TD]*** Category: NONE ***[/TD]
[/TR]
[TR]
[TD]11154 Juice # DRUM 11154[/TD]
[/TR]
[TR]
[TD]310715 0916 DF5203 1 3 1,818[/TD]
[/TR]
[TR]
[TD]310716 0916 DF5202 1 4 2,424[/TD]
[/TR]
[TR]
[TD]354682 2646 CF5608 1 4 2,424[/TD]
[/TR]
[TR]
[TD]354683 2646 CF6402 1 4 2,424[/TD]
[/TR]
[TR]
[TD]354684 2646 CF8107 1 4 2,424[/TD]
[/TR]
[TR]
[TD]354685 2646 CF6702 1 3 1,818[/TD]
[/TR]
[TR]
[TD]---------- ---------- -----------[/TD]
[/TR]
[TR]
[TD]** Item Totals 6 22 13,332[/TD]
[/TR]
[TR]
[TD]11874MANC15 6.8% MANC[/TD]
[/TR]
[TR]
[TD]335212 24DE/2015/0089 ACCORNER 1 18 1,584[/TD]
[/TR]
[TR]
[TD]335208 24DE/2015/0089 CC3106 1 3 264[/TD]
[/TR]
[TR]
[TD]---------- ---------- -----------[/TD]
[/TR]
[TR]
[TD]** Item Totals 2 21 1,848[/TD]
[/TR]
[TR]
[TD]11970 Orange Peels[/TD]
[/TR]
[TR]
[TD]343536 22161 AC1706 1 24 1,200[/TD]
[/TR]
[TR]
[TD]343537 22161 BC3606 1 24 1,200[/TD]
[/TR]
[TR]
[TD]343538 22161 BC3506 1 24 1,200[/TD]
[/TR]
[TR]
[TD]343539 22161 CC1001 1 28 1,400[/TD]
[/TR]
[TR]
[TD]---------- ---------- -----------[/TD]
[/TR]
[TR]
[TD]** Item Totals 4 100 5,000[/TD]
[/TR]
[TR]
[TD]12813 PEARS[/TD]
[/TR]
[TR]
[TD]348581 1000212893 CF6208 1 38 1,710[/TD]
[/TR]
[TR]
[TD]348582 1000212893 BF5502 1 25 1,125[/TD]
[/TR]
[TR]
[TD]---------- ---------- -----------
[TABLE="width: 571"]
<tbody>[TR]
[TD="class: xl65, width: 571"]** Item Totals 2 63 2,835[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

So the data is rough to use at the moment but all I want out of it is the item number which is 90% of the time just a number below the two asterisks. Then I take the item totals from below the dashed lines. I have made a few formulas to just auto pull based on common numbers but with constantly changing item numbers and the file changing its gotten to be quite the task. However the data isn't always even that clean but it will always follow that strange format of item...lot info..etc all in blocks.

My goal is to clean this up into a summary but formulas alone just can't cut it I need a way to parse data, even if I fed in the item numbers for example if it new to look for 11154 and pull in the item totals nearest to that I could work through that.

So my logic was this and maybe its not easy to translate into VBA but I am hoping so.

• Find Item number from a list I can create easily (Start at first item, if it's not anywhere in column a move to next number)
• Using that item numbers cell location continue down to find either the ------- or Item Totals?
• Now pull the value from the cell location of the Item Totals but to the right 6 columns (This doesn't change that I have seen yet)
• Copy the item number and description from item list and the new total into a new sheet in a tabular format

Long story short here is the above data in a summarized format

11154 Juice  13,332
11874MANC15 6.8%  1,848
11970 Orange Peels 5000
12813 PEARS  2,835

Hopefully, my explanation has been clear, I am working on some VBA now to find the item number cell location and pull that into the code and so on but my VBA skills are usually focused more on simplistic loops.

Thanks for any help or suggestions!