CODEWITHSUNDEEP
mysqlexcelvba
MotoDave452
MotoDave452

Reputation: 406

Compare one column in excel to another column in excel and find duplicate values

I was just wondering if there was an easy way in Excel to complete the following task:

In my Excel spreadsheet I have two tabs. One tab represents one database and the other tab represents a different database. I have a column of data that is 1500+ rows long from my first database(tab) that looks like this:

enter image description here

In the other tab, I have another column that is similar to the above column. My goal is to be able to compare the above column to the other column in the separate tab, and search for duplicate values.

So for example the first code in the above column is 00064. It would search the specified column in the separate tab for the code 00064. If it finds it I would need to be alerted or have it logged somehow. Then it would move on to the next code, 00368 and repeat the process for every cell inside that column. I hope this makes sense. Please feel free to ask any questions you may have for further clarification.

My desired outcome is to ween out the duplicate values but I am not sure how to go about this because due to the length of my columns, manual comparisons would take quite some time. If there is a macro that could accomplish this it would make my life so much easier.

I greatly appreciate any help that can be offered.

Thank you!

-Dave

Upvotes: 1

Views: 877

Answers (2)

JNevill
JNevill

Reputation: 50034

No need for VBA here. In the first cell of your second column (B1) of your first tab use a countif formula:

=countif(tab2!A:A, A1)

Copy this formula down so it is next to every populated cell in Column A. This will return the number of records in Tab2 that are equal to A1 in your first tab. Look for anything > 0 to find things that have a match, or things > 2 for duplicates.

You can extend this with an if statement like

=if(countif(tab2!A:A, A1)>2, "Duplicate", countif(tab2!A:A, A1)=1, "Only one Match", "No Match"))

Upvotes: 2

BradyK
BradyK

Reputation: 387

This does not require the use of a macro. I would use a VLOOKUP formula. If an exact match is found between the columns it will return the columns value. If no match is found the cell will read #N/A.

An example of the formula that compares the cell A1 on Sheet1 to the column A on Sheet2 looks like this:

=VLOOKUP(A1,Sheet2!A:A,1,)

Here is documentation on how to use this formula.

Upvotes: 2

Related Questions