comparing 2 excell list's - how to?

[gitarrero]

hi folks,I'm not an expert in excell, so I need your help.here's my issue:* I have two excell lists with entries of companies, and I want to compare them automatically and highlight / or mark in a separat collumn any double entries (ie if the same company name appears more than once).I could have all entries in one list, that won't be a problem.it would be most usable if any double entry is marked in a separate collumn by a word like "doublette". then I can look at it more closely and decide wether I want to delete it or not.I think there must be an easy solution for this - but I don't know how to do it in excell; so any help is welcome!thanks in advance,martin

[mer]

Well one idea is to put them all in one list (do this in a separate worksheet!), and then alphabetize them, by sorting in ascending or descending order based on the column that has the name. Then at least the names that are the same will be next to each other! There is probably another way to do it, but this is what I thought of first... I thought there is also a "compare" function, but that may be in Word instead of in Excel, and I don't think it would work because it would expect everything to be in the same order in both lists and if it is not, then it would point out ALL the differences...Hope this is helpful!--Mer

[gitarrero]

hey mer,thanks for your input. yeah that would be an idea, but the problem is that the list is quite long - plus I don't want alphabetize the entries.a function that markes the double entries would be best I think.any other ideas?thanks,martin

[aubreyz]

Which version of excel are you on? I know this works in excel 2007, but can't remember if it does in previous versions.Under data, there is a remove duplicates option and you can specify which column to check. If you combine the two sheets into one that should work.Aub

[bigbluebarry]

hey Martin, if you keep the lists in 2 different tabs in the same workbook then here's how you could distinguish the duplicates from each other. You're going to want to use a combination of 2 functions, the IF() and VLOOKUP() functions.Let's say your excel file has 2 tabs in it, called Sheet1 and Sheet2. Make a choice as to which sheet is the "Master List" and which one is going to contain the "duplicates." Let's say for this example, Sheet1 is your Master List and Sheet2 contains the potential duplicates. Let's pretend that Sheet1 contains the following data with a Company Name in Column A and a Phone # in Column B: AB1Big Bass555-BASS2Guitar Heaven555-GTRS3Drums-R-Us555-DRUMAnd now let's pretend that Sheet2 contains the following data: AB1Drums-R-Us555-DRUM2Guitar Center555-ROCKI'm going to give you a formula with data in it and then explain what you need to change. In Sheet2, go to your first row of data and then find an empty column, preferrably to the right of all of your data. Then copy the following formula into that cell. Using the example data above, you would be pasting the following formula into cell C1:=IF(VLOOKUP(A1,Sheet1!$A$1:$B$3,1)=A1,"duplicate","")I color-coded it for you so you can see which sections to change a bit easier.Ok, the first red A1 in that statement is the value you're looking for to see if it's being duplicated. In this case, it's the Excel cell number for the first company in the sheet. By using the cell number of the value instead of a 'hard coded' value, you're able to copy and paste this formula easily.The yellow Sheet1!$A$1:$B$3 represents the range of cells you're wanting to look in. The 'Sheet1!' makes sure it's looking up the values in the "Master List". The '$' sign before the A, 1, B and 3 makes sure that when you copy and paste this formula, the range of cells you're looking in doesn't get changed. The '$A$1' represents the upper-leftmost cell and the '$B$3' represents the lower-rightmost cell in the range of values you're wanting to look for. The green 1 represents the column that contains the cells that your'e wanting to check for duplicates in. In this case, it's the company name so we have a value of 1 since the Company Name column is the first column of data.The second red A1 is used to say if the VLOOKUP function finds the value specified by the first red A1, and that value is equal to this one, then we have a match. If you were only looking for one value then you could hard code that value here, but since you're looking for a different value in each row, we'll use the Excel cell number instead so that we can copy/paste.And finally, the blue "duplicate" represents what text will be displayed if this row does contain a duplicate. And the purple "" is what will be displayed if the the row does not contain a duplicate. So if there is a duplicate you will see the text "duplicate" in the cell, and if a duplicate did not exist the cell would remain empty.So once you've got your formula set for the first row, just copy and paste it for the rest of the rows in Sheet2 and you should be able to easily identify the duplicates.Hope that helps!- Big Blue

[gitarrero]

hey big blue,thanks a lot for this in-depth answer!but if I copy/paste your formula in a cell left to the data I get the message "formula contains an error".I assume that I just need to paste the formula into an empthy cell left to the data in sheet2, correct?thanks a lot again,martin

[bigbluebarry]

My first hunch tells me that your tabs might be named differently. If your "master list" tab is not named "Sheet1" then you'll need to update the formula to use the same name as your "master list" tab. If your tab name contains spaces in it, then you'll need to enclose it within single quotation marks. So for example, if your "master list" tab was called "My Master List" then the formula would look like this:=IF(VLOOKUP(A1,'My Master List'!$A$1:$B$3,1)=A1,"duplicate","")If that doesn't solve the problem, let me know and I'll send you my email address via PM and you can email me the file and I'll take care of that for you.- Big Blue

[gitarrero]

@big blue - you've got mail;)

[devin]

Not only did BigBlue beat me to providing the solution...he also stole my idea of just getting the files sent to him LOL!"Hey Blue...I'll give you $5 to send me a copy of Martin's list...I'm sure it would come in handy!!"(Just kidding Martin...hope it all works out for you!) P.S. I don't have these problems...with one forward, my list only has one name on it, and it's still "waiting for contact"