Extracting information from an Excel converted file to put into an CSV Excel file

[wicked_bounce]

Hi I have a little project that Iv been working on for the past 2 weeks where I am trying to simplify a task and try to have less manual work involved as possible.

The task im having problems with is extracting information from 1 excel file (which has been converted to excel from its original format (which could be word, pdf etc)) and puting this into a 2nd excel file that needs to be uploaded into our inhouse system. The information needed is pretty straight forward i.e: Equipment description, item amount, serial numbers, invoice date, invoice number and supplier, all other information isnt required. But the issue is, is that all invoices are different and the biggest issue is the equipment description, I may get the following: 5x Strawberry lollies with 20 serial numbers, which translates to 20 lines in the 2nd excel spreadsheet with a serial number each.

I have included examples of what the invoice would look like when I receive it (Attachment Spreadsheet 1) and an example of what the 2nd spreadsheet would look like once all information has been transferred to it (Attachment Equipment List).

Does anyone know a more simple and less painful way of converting this information from the original invoice to the final spreadsheet that our inhouse system requires?, i do understand that SOME manual work will be required so im happy for that, atleast this way will cut down a 40 minute job to atleast 10 minutes, iv also tried Macros and that helps but only if all invoices are the same, which in this case it isnt, so may need more than 1 Macros formula.

All help,feedback, input, referrals would be much appreciated.

Cheers
Excel Noob