Help Organizing Converted PDF pages

[maskseed]

Hi there! I am trying to structure data from PDF files that were converted to excel. The issue is that I need the store number next to each line of data to identify which store it is from and then the area and quantity need to be stacked. The only information I need is the Store number, Area, and Qty. I uploaded a sample of the data and the end result that I am trying to achieve. Does anyone have any ideas?

Thanks! 

[BrandonB]

This should do the trick!

 

[danilang · Accepted solution]

Hi @maskseed 

@BrandonB's solution comes close, but it doesn't account for the fact that your columns shift.  In your first store group F2 is Qty and F3 is area, but in the next one(line 61), F2 is blank,F3 is Area and F4 is now QTY.

This workflow analyses each group within the report and extracts and matches the area column with the corresponding Qty

Dan

[BrandonB]

Ah good catch @danilang! I was working too fast and missed that.  

[maskseed]

This was very helpful. After receiving @BrandonB's solution I decided to work on the PDF to excel extraction to align all the columns and I was able to figure it out, but this workflow accounts for the shifting columns even even if they don't shift. I ran two different input files (one with shifting columns and one without) through the workflow and ended up with the same result. Well done @Danilang