Excel question!

[SpeckledJim]

I'm sure this is straight-forward but I don't have a clue how to do it.

2 files. The first contains lots of columns of product data, including one column labelled SKU

The second file includes just 2 columns - SKU and Item number.

The first file contains all of the same items as the second file, and a lot more that are not contained in the second file.

My task (your task!) is to add the item number (from file 2) to a new column matching the corresponding SKU in the first file.

Thousands of lines, so I can't do it manually.

Any help gratefully received!

TLDR - SKU is the common key, I need to move a correlated piece of data (item number) from one file to the other.

[Fartgalen]

Sort both tables on the code, then cut/paste the data from to the other.

[SpeckledJim]

Sadly 50,000 lines in this sheet, and another 250,000 to go after I've cracked how to do this, so I can't do the C&P route.

There are as many hits as misses and they are randomly scattered through the data, so the sort will not line-up.

Thanks though.

[chilluk7]

I'd import them both into Access, do a query, and export the results of that!

If you must use Excel think you could do a VLookup from one sheet to another.

[Scott W]

This sounds like a very simple VLOOKUP command where you are matching on the SKU values between your files and you bring in the Item number for the matches you find from file 2 in to file 1.

[SpeckledJim]

Thanks, sounds like VLOOKUP is my friend. I'll go for a play and see if I can do it.

Ta!

[a7girl]

Yes VLOOKUP should sort it, you'll need your SKU as column a on both tables. If you use the function button you should get the help function (and in case you are a beginner the formula will need to go in the new column you create) but the formula should look something like

=VLOOKUP(A2, '2 COLUMN TABLE LINK', 2, FALSE)

Essentially you need to tell it what data it's cross referencing (your SKU thingy from table 1), where it needs to go and look for that cross reference (table 2, whose name won't be like I've typed it above!), which column it then needs to look in to get the info you need (column 2 as you only have 2 columns) and whether you need an closest or exact match (exact so false would apply in this case)

[BenWRXSEi]

If you don't want to fiddle around with VLOOKUP, try this:

• Sheet 1 - sort SKU by A-Z. Add filters so it's effectively a table.

• Sheet 2 - sort SKU by A-Z. Copy the SKU cells (cells only, not the whole column) and paste them underneath the bottom of the SKU column in Sheet 1 (leaving a couple of blank cells in between)

• Sheet 1 – Highlight SKU column: Conditional Formatting – highlight cells rules – highlight duplicates.

• Sort SKU by colour (should be a nice pink-ey colour for each SKU that matches up, meaning it features in both lists). Add in a column for Item Number.

• Paste Item Number into new column, and all should match up with the relevant SKU.

If it’s non-sensitive data feel free to PM me and I’ll happily do this for you. It’s a 30-second job

[a7girl]

BenWRXSEi said:

If you don't want to fiddle around with VLOOKUP, try this:

• Sheet 1 - sort SKU by A-Z. Add filters so it's effectively a table.

• Sheet 2 - sort SKU by A-Z. Copy the SKU cells (cells only, not the whole column) and paste them underneath the bottom of the SKU column in Sheet 1 (leaving a couple of blank cells in between)

• Sheet 1 – Highlight SKU column: Conditional Formatting – highlight cells rules – highlight duplicates.

• Sort SKU by colour (should be a nice pink-ey colour for each SKU that matches up, meaning it features in both lists). Add in a column for Item Number.

• Paste Item Number into new column, and all should match up with the relevant SKU.

If it’s non-sensitive data feel free to PM me and I’ll happily do this for you. It’s a 30-second job

So is VLOOKUP when you get used your it, plus it's more versatile 😉

[SpeckledJim]

BACK OF THE NET!!

Thanks a lot everyone. My afternoon has just switched from frustration and angst to boredom and ennui.

And that's a big result.

[BenWRXSEi]

a7girl said:

So is VLOOKUP when you get used your it, plus it's more versatile

True - in all honesty I've never taken the time to get my head around it

[a7girl]

BenWRXSEi said:

True - in all honesty I've never taken the time to get my head around it

I had to in my old job and needed to try similar things to OP. Getting used to naming tables etc made the logic of the formulas a lot easier to follow.

[Murph7355]

You have hopefully finished this by now, but when copying the vlookup make sure the table range remains correct.... Use a named range for the data or use '$' signs to prevent Excel incrementing rows/columns.

Vlookup is a powerful tool once you get your head around it....

[SpeckledJim]

Thanks. Once the data was across in the master file I copy and pasted values only to 'lock' it in.

I knew what I wanted to do but couldn't find a concise enough way to get google to help me.

PH delivers - thanks all.