Need help cleaning up pasted text and comparing two lists.

[fredriko83]

Hi!

I wonder if someone would be willing to point me in the right direction on a couple of things.

I'm trying to make a excel sheet that compares the the shipping note from our supplier with gods that are received and entered into our Business management system. Today this is done "by hand" which leaves a lot of room for errors.
One of the problems is that shipping note is sent to us as a PDF and i would really like if it was automatically cleaned and only the necessary information was put in the next sheet (i have supplied the shipping note (just a template i found online since i don't have access to work files at home) and a excel spreadsheet that shows my desired results and some more notes about the functions i need). I also wonder about what functions to use for the comparison the shipping note and received goods?

Thanks for any help, it was along time since i used excel now and it all feels pretty overwhelming

/Fredrik Olsson

[sourabhg98]

Hi
Its quite difficult to clean the data like this....but we can certainly try if we have some more example of the raw data from the pdf file....also do you keep on pasting all the the data from pdf one below the other in "Pasting packing note" Sheet?

[fredriko83]

Hi
Its quite difficult to clean the data like this....but we can certainly try if we have some more example of the raw data from the pdf file....also do you keep on pasting all the the data from pdf one below the other in "Pasting packing note" Sheet?

The pdf I supplied it's just a template i found online i don't have access to a real packing note now and I'm probably not allowed to post a real packing note online or even take it home from work. But i have extended it with a couple of pages so that looks a lot like the shipping notes at work.

No we just check one delivery note at a time so it's just pasting one pdf at a time and when thats done i just reload the empty document and do the next.

[sourabhg98]

Hello
Check the after pasting sheet....this is the format I am getting after pasting from PDF ..would this format be the same for all of them?

[fredriko83]

The pasting you have doesn't look like mine, I just installed adobe acrobat reader switched to enable browsing under view-> side viewing (I can't find a way to mark everything in the pdf when i have One page at a time view, my os is in swedish so that is probably not the right translation) and then marked everything and copied it and now my pasting looks like yours (before i used windows 8 own pdf-reader) If you look in the Paste packing note sheet there the the whole packing note i did with three pages in it. But there could be more or less than four products per page.

By the way i really appreciate your help

[sourabhg98]

Would it be comfortable for you if you paste one page and then press a button to clean and then delete the packing note and paste the second page...press the button and so on?

[fredriko83]

Absolutely

[sourabhg98]

I will be back with the answer by tomorrow!!

[fredriko83]

Thank you very much!

[sourabhg98]

Hello
I am almost done with your requirements
It will give accurate results if these conditions are satisfied-
1. Before all the quantities the word "Quantity" must be there in column A
2. You always have the word "Packed By:" after the end of quantities.
3. All the data must be in column A
4. The last quantity must be the total quantity
5. Before all the items and their names the word "Description" must be there in column A
6. First there is an item number and then its name just below the item number
7. You have data between rows 1 to 51
8. You always have the text "Special Notes Total Quantity of Goods/Boxes" after description of items is over

If all these conditions are satisfied in other packing slips like in the packing slips you gave me...then I can assure you correct results...
Till now I have made the formulas the ..difference sheet is still pending but thats easy ....please check the results in the cleaned packing note sheet....try pasting some packing notes in the orange area...and then clarify the results...
Once you approve these formulas then we can move forward towards designing the macros..

Also please tell that for pasting the next page of your packing note...do you need the cleaned results for the second page below the cleaned data for first page and so on....??

Hope this works...

[fredriko83]

hehe i didn't mean for you to do all the work, it's really nice of you but I need to understand whats going on myself so that i can further develop the document. Rather than doing all the work I would prefer that you gave me some tips and pointers so that i can learn.

Hope you don't think I'm ungrateful, you are just a bit to nice to me

[sourabhg98]

Hi again
Its good if you understand it yourself and apply accordingly...
So let me try to explain what I did in the sheet I sent you..
If you see the yellow table in the sheet starting from the column "O" and see cell O2...it looks for the word "quantity" in column A and returns the row number of the cell where it is found.. then cell O3 searches for the word "Packed by:" in column A and returns the row number of the cell where it found.
As we know that all the figures of quantity are between "Quantity" and "packed by:" so we get the row numbers of the cells between which it is sandwiched.
And now O4 is an if formula....it sees if the row number of the cell is between the two numbers we got from cells O2 and O3 and if yes then it gives the number in from the column A.
Similarly it is done for item and its name
Hope it helps

Do ask if you don't understand...you cal also use "evaluate formula" from the "formulas" Tab to get a better understanding of the formulas...

[fredriko83]

Hi

I think i had figured out the most if it now will send you the file when it finished so that you can a look at our work when the cleaning of the pdf is done do i need a macro to compare the two lists? Do you know if there is any way to delete all the rows in a range specified in two cells? For example b1 contains only 12 and b2 contains only 25 can I then automatically remove the rows a12:a25 (using "A"&b1 with indirect maybe?)? I don't necessarily need to delete the rows it's fine if I just can move all the other rows to another sheet.

[sourabhg98]

Yes Indirect function would work
if we have lets say 12 in B1 and 12 in B2 ...then in B13 put the formula
=B1&":"&B2
and use this macro

Please Login or Register  to view this content.

[fredriko83]

It works really well! Thank you so much for your help!!