Excel experts required please....

[chubster]

Hi

I have two files....one from my database and one from my supplier. One thing they both have in common is the sku.

Is there an easy way (using some sort of excel formula) I can compare the two and get excel to show sku's from my file that do not appear in my suppliers ?

Thanks in advance

Chub

[Red Devil]

You could try downloading Exam Diff Pro (free for 30 days) which will compare 2 files and highlight differences.

Make sure both files are sorted on SKU first (in SKU order).

Website

http://www.prestosoft.com/edp_examdiffpro.asp

You can do this sort of thing in Excel but that's beyond me without Googling.

[chubster]

Cheers Red....I will give it a go. Was just looking at a VBA Macro but suddenly got hot flushes and blurred vision :-)

[Red Devil]

There's something called vlookup in Excel (I think) that matches 2 files but dont know how to use it.

[cjtait]

I think the match() function is what you need.

Copy the values from one file into a new sheet in the other. Assuming the SKUs are in column A in both sheets in cell B1 on Sheet1 put

=MATCH(A1,sheet2!a1:a1000)

1000 is an example - use the number of SKUS you have pasted into that sheet. This will put in B1 the row in Sheet2 that the matching value appears. If there's no match you get #N/A error.

You might be able to put a reference to cells in a second workbook, but I have never done that so don't knw the syntax.


Hope that helps

[chubster]

You could try downloading Exam Diff Pro (free for 30 days) which will compare 2 files and highlight differences.

Make sure both files are sorted on SKU first (in SKU order).

Website

http://www.prestosoft.com/edp_examdiffpro.asp

You can do this sort of thing in Excel but that's beyond me without Googling.

Hmm....Dont think that will work as it is highlighting ALL the differences, (thousands).....I just want to find skus on my file that dont show on suppliers

[Red Devil]

Hmm....Dont think that will work as it is highlighting ALL the differences, (thousands).....I just want to find skus on my file that dont show on suppliers

Can you cut the 2 files down to just contain the SKU's?

Think cjtait has an Excel solution.

[Red Devil]

In Exam Diff Pro, in View Options / Compare, there is an "Ignore parts of each line" and "Column Ranges" so you could just compare the bits where the SKU is.

[chubster]

Can you cut the 2 files down to just contain the SKU's?

Think cjtait has an Excel solution.

Just trying that now

[chubster]

I think the match() function is what you need.

Copy the values from one file into a new sheet in the other. Assuming the SKUs are in column A in both sheets in cell B1 on Sheet1 put

=MATCH(A1,sheet2!a1:a1000)

1000 is an example - use the number of SKUS you have pasted into that sheet. This will put in B1 the row in Sheet2 that the matching value appears. If there's no match you get #N/A error.

You might be able to put a reference to cells in a second workbook, but I have never done that so don't knw the syntax.


Hope that helps

Cheers Cj.....Not sure if I am doing this right though.....Bear with me :o

I copied sku's from supplier into Sheet 1 Column A (19931 lines in total) then in Sheet 2 pasted my sku's (3202 in total) into Column A. Typed =MATCH(A1,sheet2!a1:a19931)

This returned one result in cell B1 (3202) on Sheet 1 . I assume this is saying that all 3202 of my skus appear in my suppliers, is that correct ???

[chubster]

Ok, must be doing something wrong as I have just done a quick manual check and have already found one sku in my file that doesnt appear in my suppliers ?

[Red Devil]

I put the formula in Colum B on Sheet 1.

But when I copy the formula down, the start cell increments by 1. So it searches from/after that point.



=MATCH(A1,Sheet2!A1:A10,0)
=MATCH(A2,Sheet2!A2:A11,0)
=MATCH(A3,Sheet2!A3:A12,0)


So once it finds a mismatch, it stops.

I had 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 on Sheet 1 Colum A and 1, 2, 3, 5, 6 on Sheet 2 Column A.

It returns "1" for 1, 2, and 3 then "#N/A" for every number thereafter.

Even tried it with "1" as the match type (last parameter). Same result.

Found this if it helps?

http://www.techonthenet.com/excel/formulas/match.php

[Red Devil]

Spent ages trying to get this to work.

Tried VLOOKUP.



=VLOOKUP(A1,Sheet2!A1:A10,1,FALSE)


Didn't work for "hours" then I cleared the spreadsheet and started again and now it does work!

Reference

http://office.microsoft.com/en-ca/excel-help/vlookup-HP005209335.aspx?CTT=3

[chubster]

Wow.....wasnt expecting anyone to go to that much trouble. :eek:

Much appreciated Red I will give it a go

[chubster]

Vlookup doesnt work im afraid....Arghh, Im really bad at explaining this as I really dont understand it. (excel)

I thinks its because sheet 1 (supplier) has 19000 odd entries and sheet 2 (mine) has only 3200 odd and the numbers in the cells are different. If I enter that formula and then copy it down it just returns N/A in every column.

Attached a screen shot to help explain

23

Obviously because the numbers in Col B (my numbers from sheet 2 pasted just for demo purposes) dont match with Col A then i get the N/A result :confused:

Sorry I know I am not explaining this very well....

[chubster]

Got it working......:D:D

Copied supplier sku's into Col A, mine into Col B and then pasted this formula into Col C

=IF(ISERROR(VLOOKUP(B1,A:A,1,0)),"No","Yes")

Works a treat.

Thanks to the pair of you for the help, much appreciated

[cjtait]

VLOOKUP/HLOOKUP and MATCH are all notoriously difficult to understand, but are very useful in this type of situation. I also made a mistake on my earlier description which wouldn't have helped!

Again, I assume you have your supplier SKUS on Sheet1 and your SKUs on Sheet2 both in column A.

In cell B1 on Sheet 2 put =MATCH(B1, Sheet1!$A$1:$A$19931, 0). You need the 0 (I thought this was the default argument earlier and so could be omitted). Select Cells B1 and Copy. Select cell B2 to B3202. Paste. This should fill that column with MATCH functions. Those that match will have a row number. Those without a match will have N/A.

Putting the $ signs in the cell reference makes it absolute i.e it won't be changed when you copy the function into all those cells which is what you want in this case. The B1 reference will update to point to column 1 in each row. Just click on any cell in column 2 to see the updated formula.

Hope that helps.

[powertoolworld]

Hi

I have two files....one from my database and one from my supplier. One thing they both have in common is the sku.

Is there an easy way (using some sort of excel formula) I can compare the two and get excel to show sku's from my file that do not appear in my suppliers ?

Thanks in advance

Chub

Let Bluepark take the strain!

If you have 2 files then they are 2 sepatate sets of data you need for your site for the same page ie. images in one and data in the other. They both have the SKU as the field that is similar!

Upload both separately and Bluepark will put them where they belong.