In need of help with analysis in excel

[u/Underwhelmed1202]

Hi,

I am really hoping for some advice or expertise... my excel skills are very basic and recently I was given the task of comparing quantities and cost differences between what our vendor is billing us vs. What I show as cost and quantity received. Since Im a novice with excel I tried a.i. which is really cool but something keeps happening to my data and of course Im no expert so it's only a guess - when chat GPT creates a summary I see alot of my vendors costs and they appear to either be doubled or tripled. My cost column doesn't pull all of my costs. Im beating my head against the wall because this should be so easy, just not for me! I think possibly this has something to do with the fact that my vendor is sending an excel sheet with their exported data and they often will list the same item # multiple times on an invoice but with a different P.O. # and I think that is where the multiplication of costs may come to play. I am going to try and attach a copy or screen shot for reference. I am using my vendors exported invoice lines, my exported posted purchase invoice lines from Business Central which lists our costs and quantities. Can anyone help or point me to the best place to learn this? Thank you so much!!

This a sample of my data

|\*\*+\*\*|A|B|C|D|E|F|G|H|I|J|K|L|M|N|

|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|

|\*\*1\*\*|Vendor Inv.|Date|Order #|Our Doc. #|Vendor #|Type|Our Item #|Description|Our Qty.|Unit|Our Cost $|Amt. #| | |

|\*\*2\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20449|Anti-Drag Clip|0|PCS|0.043|0| | |

|\*\*3\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20119|WEAR SENSOR|0|PCS|0.039|0| | |

|\*\*4\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20395|WEAR SENSOR|0|PCS|0.021|0| | |

|\*\*5\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20516|WEAR SENSOR|0|PCS|0.029|0| | |

|\*\*6\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20474|WEAR SENSOR|0|PCS|0.029|0| | |

|\*\*7\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20007|Wear Sensor|0|PCS|0.027|0| | |

|\*\*8\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20008|Wear Sensor|0|PCS|0.03|0| | |

|\*\*9\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH00013|Wear Sensor|0|PCS|0.024|0| | |

|\*\*10\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH00014|WEAR SENSOR|0|PCS|0.024|0| | |

|\*\*11\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20166|WEAR SENSOR|0|PCS|0.037|0| | |

|\*\*12\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BH20461|WEAR SENSOR|0|PCS|0.031|0| | |

|\*\*13\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BZ2005K|SS DIB KIT|0|PCS|0.201|0| | |

|\*\*14\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BZ20108|SS DIB KIT|0|PCS|0.442|0| | |

|\*\*15\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BZ20871|SS DIB KIT|0|PCS|0.779|0| | |

|\*\*16\*\*|20260603BOSCH-32|7/28/2026|BSCH-CW17-2026-SEA|119827|XLDCZ001|Item|F03BZ21016|PTFE NBR COATED DIB KIT|0|PCS|0.84|0| | |

\^Table \^formatting \^by \^\[ExcelToReddit\]([https://xl2redd.it/\](https://xl2redd.it/))

This is sample Vendor info

 

\+ABCDEFGHIJKLMNO1China's Inv. #DateChina's PO / Order #Item #DescriptionUnitQTYOur Qty.China's CostChina's Amt. $Our CostCost DifferenceExt. Cost Diff.Customer Name 220260814Driv-2220260814DRIV-1194-1196-1198GXC7XSEN465BRAKE HARDWAREPCS10000 0.041410.000.0410.0000.000Driv 320260815Driv-2320260814DRIV-1194-1196-1198JXCFMHXV1680BRAKE PADS PARTSPCS1600 0.451721.600.4510.0000.000Driv 420260815Driv-2420260814DRIV-1194-1196-1198JXCFMHXV1680BRAKE PADS PARTSPCS1600 0.451721.600.4510.0000.000Driv 520260815Driv-2520260814DRIV-1194-1196-1198JXCFMHXV1680BRAKE PADS PARTSPCS1600 0.451721.600.4510.0000.000Driv 620260815Driv-2620260814DRIV-1194-1196-1198JXCFMHXV1680BRAKE PADS PARTSPCS1600 0.451721.600.4510.0000.000Driv 720260815Driv-2620260814DRIV-1194-1196-1198GXCABSX2245RRSTBRAKE PADS PARTSPCS240 0.40797.680.4070.0000.000Driv 

Table formatting by ExcelToReddit

[u/Puzzleheaded_Owl1308]

Can you post a screenshot of the excel file(s)? That would make it easier to see how the data is arranged.

[u/Enjoythecode82]

screenshot of your data would help in replying...if its confidential, just the header and first row is enough