I need to look for a match within a second Excel file that has the same information but for lots of PO’s, i need to indicate/append the second file to show there was a match ensuring the PO, qty and £ is the same and also qty and price(each).
I’ve got as far creating variables for each of the items in the first excel file to start checking against the second one, but now I’m rethinking the whole thing to see if there is a better option re matching?
Then my secondary issue is if there is potentially 1 line on the first file that adds up to 2 lines on the second file (or the other way around), but i’ll save thinking about that for another day. Maybe a change in process is in order to avoid the possibility of multi line matching against single line or multi line against multi line.
It depends how far you want to go with the matching. If you want to only calculate on the total of the PO and not get into individual lines:
Start by looping through your ‘purchase’ file, store the quantity, and amounts required. Then, loop through your ‘Purchase orders’ file. Look for a match to the PO number, then store the quantity and amounts available. The bot then just needs to calculate if theres a quantity or amount issue from the information gathered. It would be a good idea to output all the gathered information and whether it was a match / the discrepancy amount so that you can see if the comparison is working the way you intend.
if you want the bot to actually check things on a PER LINE basis then you will have to add another layer of loops to do the above but for each PO and line instead of for each PO. you could work concatenate the PO number and line number and do your comparisons that way.
Edit: actually it wouldn’t be another layer of loops. You’d just be looping through each PONUMBER and LINE combination instead of each PO number. If your ‘purchase’ data doesn’t necessarily state the correct po line number you might have issues matching the lines to your ‘available purchase orders’ data
Read Excel 1 and convert to Datatable Dt1
Read excel 2 and convert to Datatable Dt2
iterate Dt1 using For each row then Use Filter Datatable or Select Query to get the match item then you can add it too need sheet or file.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu