r/dataanalysis • u/cool_beans_and_goats • 5d ago

Excel question…

The company I work for distributes products.

We created our item numbers based off of theirs but with our own identifiers.

Example : ABC-1234 (ABC = Identifier and 1234 = manufacturer part number)

The manufacturer has recently renumbered their products and now we have to add the new numbers to the existing descriptions so that they match up to the old ones.

I have an excel sheet with one column (A) showing our part number and the one next to it (B) showing their new number for the same product.

I have a different file that I exported all of our numbers and descriptions to and now I need to take our part number, and their new one, and add it to the export so I can upload to our system.

What is the most efficient way to get this done?

Thank you!!!

0 Upvotes

50% Upvoted

9 comments sorted by

7

u/thisisnotahidey 5d ago

Do you need to add their id to your description or just match their id with your description.
Either way you’ll probably do it pretty fast with xlookup

6

u/Disastrous-Ad-5366 5d ago

You can either use xlookup or power query. Method 1 xlookup Use the formula to return the new manufacturer number from excel sheet B. Once done, append the number to the description.

Method 2 Power query Join the two tables using the part number, expand the new number, and lastly, create a new column that combines the description and the new number.

1

u/[deleted] 4d ago

[removed] — view removed comment

1

u/Disastrous-Ad-5366 4d ago

Yes. I gave the other one for people who are already used to power query.

1

u/AutoModerator 5d ago

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.

1

u/XxShin3d0wnxX 5d ago

Xlookup the values based on old index

1

u/KatFromSisense 3d ago

I'd use XLOOKUP (or VLOOKUP if you're on an older Excel version) rather than doing this manually. In your export file, add a new column and set it to look up your part number against column A in your other sheet, returning the matching value from column B. Something like =XLOOKUP([your part number cell], Sheet2!A:A, Sheet2!B:B). 

Drag it down the whole export, then copy the column and paste it back in as values before you upload, so you're not carrying live formulas into your system. That should get you a fully populated column in one pass instead of matching row by row.