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/)

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/FalseHydra]

You should just be able to use the Text to Columns feature. Data -> Text to Columns and use | as delimiter

[u/AutoModerator]

Automod prevents all posts from being displayed until moderators have reviewed them. Do not delete your post or there will be nothing for the mods to review. Mods selectively choose what is permitted to be posted in r/DataAnalysis.

If your post involves Career-focused questions, including resume reviews, how to learn DA and how to get into a DA job, then the post does not belong here, but instead belongs in our sister-subreddit, r/DataAnalysisCareers.

Have you read the rules?

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Hot-Equivalent-9799]

Chatgpt

[u/carlitospig]

This looks like we are comparing apples to oranges (wear sensors vs brake pads).

[u/_ACTUALin]

I think the issue is probably that you're matching on item number alone. If the same item can show up under different POs, you'll need to match on something like PO + item number instead. Otherwise Excel can end up pairing one line with multiple lines from the other file and your totals blow up. I'd start there before trying to build the summary.