Parsing an inventory pdf to usable data

Slizer6893

Active Member
Joined
Oct 25, 2013
Messages
271
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

Code:
[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

Code:
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!
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Watch MrExcel Video

Forum statistics

Threads
1,109,400
Messages
5,528,518
Members
409,821
Latest member
decibelpilot

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top