# Compare Two Lists to Highlight Matched Records

Share
Embed
• Published on Apr 22, 2013
• www.computergaga.com/blog/compare-two-lists-to-highlight-matched-items/ Compare two lists in Excel to identify the matched records. Highlight the duplicate, or the unique records, by comparing two lists using the Match function and Conditional Formatting.
• Howto & Style

• Breathing Heart 7 days ago

i have a doubt about , in duplicates we can highlight same value presant in two columns,but those are in zig zog position.i requred that same values in side by side ,not in zig zog position.

• Liberty Garden 2 months ago

Just saved me a couple of hours of work. Thanks.

• FINANCE QUERY SOLUTION 2 months ago

my quation is , I have write 30 list where I enter 10 name in each list and total name which I have written in 30 list is 22 name . so how to find dublicate list in 30 list if one name is differ from list then that list unique list give the Solution .My Mail id - khajansingh40@gmail.com

• MR ABALONE 3 months ago

Hi sir, really appreciate your sharing.
May i know, can the excel match the record and arrange them side by side?
thank you!

• Computergaga  3 months ago +1

Thank you. Sure. Instead of using MATCH alone. Combine it with INDEX, or use VLOOKUP instead. Use this formula in the column adjacent to the one you're searching from so that you get them side by side.
VLOOKUP can find the record and return information related to it. I have a video on VLOOKUP here - thexvid.com/video/EB-rhTyS3Fo/video.html

• Mohammad Ali Al-tamimi 4 months ago

Thank you mate.

• Verky 4 months ago +1

Does the formula change if I want to get the information on another tab? Do I add something saying to look at the data from the other tab?

• Computergaga  4 months ago

Sure. You would add the sheet name. Using the formula from the video and imagining a sheet name of members the formula would be =ISNA(MATCH(\$A2,members!\$A\$2:\$A\$19,0))
If the sheet name had a space, so for example members 2019. Then the single quotes are needed like this =ISNA(MATCH(\$A2,'members 2019'!\$A\$2:\$A\$19,0))

• Danyos777 4 months ago

Wonderful strategy! Thank you @Computergaga

• VFX Desi 4 months ago +1

Thank God for your *existence* !

• Kenneth Vela 4 months ago

It doesn't work in my sheet

• Salim Saba 5 months ago

When I watch your videos, I can keep each single step in mind easily. You make things easy to comprehend and your examples are stunning and useful. Thanks indeed Alan.

• Farid Albaloshi 5 months ago

Thank you Soo Much You MAke my Day ... and i was searching for this from one week ,,,

• gcNewd 6 months ago +1

Love this! thank you.
Would you know how to do this with currency, one sheet having negative amounts, one sheet same amount without the negative (the 2nd sheet is from a scan and export into excel and does not show negative)

• Computergaga  6 months ago

Thank you. What do you want to do? Check values that are not the same? The AB function will remove the sign from a number so that they can be compared equally.

• Deepak Bhardwaj 6 months ago +1

Ty for this vedio.... keep posting

• sathish kumar 7 months ago +1

it was very useful

• Solomon Kinyanjui 7 months ago +1

Hi Allan? I like this video, but how do I automate the copying and pasting of the unique records (in clour format) to a new worksheet

• Solomon Kinyanjui 7 months ago

The situation I'm referring to is for example where an employee data is in more than 50 columns and the total number of employees is almost five thousand. Now new employees are added in the list every month and old employees leave due to varied reasons. Now considering this is a huge data I would like the new employees extracted by copying and pasting in a new worksheet. The same should happen to those who have left but they must be in an independent worksheet.

• Solomon Kinyanjui 7 months ago

@Computergaga Thanks. I'm OK with VBA, well I thought there is a way in worksheet environment. Well for the case of macro recording what will happen in situations where you have different huge data with new conditions?

• Computergaga  7 months ago

Hi Solomon, if you are new to VBA I would record a macro of you filtering the green records and manually copy and pasting to another sheet. The generated code can then be improved to your liking depending on the exact scenario you desire. That will be a nice introduction to get an idea though.

• Waheed Khan 7 months ago

Sir it good but how we can remove the color from sheet

• Waheed Khan 7 months ago

Thank you very much

• Computergaga  7 months ago

Select the range, Conditional Formatting and then Clear Rules > Clear Rules from selected cells

• swizlestick 7 months ago +1

If this isnt working for you, make sure that you dont have any blank spaces in your cells.

• Francis Santora 8 months ago +1

Worked perfect, you're a genius! Saved me hours of drudgery.

• Jeremy Gaiser 8 months ago

Does it matter if the data is in a formatted table? im doing this exactly like you are with employees that have unique ID's and nothing happens.

• swizlestick 7 months ago

@Jeremy Gaiser There were blank spaces in some of the cells. Removed those and it worked.

• Jeremy Gaiser 8 months ago

and when i say exact. I am using a-d and F-I for my tables. Only difference is the range.

• Cynthia Szczepaniak 8 months ago +1

Thank you! Makes my job much easier. Will this formula work if the arrays being compared are on separate sheets or separate workbooks?

• Computergaga  8 months ago

• Cynthia Szczepaniak 8 months ago +1

I can live with separate sheets. You have shaved off about 3 hours of work today for me.

• Computergaga  8 months ago

You're welcome. Separate worksheets = yes. Separate workbooks = I don't think so, not with this method.

• Ubaid Ubaid 8 months ago +2

I appreciate it, really nice bro, i liked , i subscribe and i will watch all your videos thanks alot.

• Computergaga  8 months ago

You're very welcome Ubaid, thank you.

• Wanayn 9 months ago

Hi! would this work for several columns/lists? What I'm trying to do is to highlight the same strings in 10 different columns/lists with 100 rows each.

• Brian x 9 months ago

thanks for the video. my 2016 excel wont work. I put the exact the same formula and then, nothing happens. Everytime i go back to edit, i found it automatically add =" ......." at the beginning and end of the formula. Do you know why? thanks

• dimpy panwar 9 months ago

This was a lifesaver , I am not able to do the same for comparing 10 lists with each other. Can you please cover that too in one of your videos?

• Dolce Amore 9 months ago +1

what a life saver!!! thnx bruh

• Pune Pet Park 9 months ago

What would be the formula I would have to use to Highlight A2:A21 for names that are F2:F21=G2 (G2 in this case would be 'Devonshire")

• Computergaga  9 months ago

Select range A2:A21 the Conditional Formatting > New Rule > Use a formula to determine the cells to format. Enter = F2:F21=\$G\$2

• Liyana R. 10 months ago +1

I'm not an excel experts.. this really helps me a lots. thanks mate

• SALEM Center 10 months ago

Hi my friend,
It says there's an error in the formula.
I'm using office 2013.

• SALEM Center 10 months ago

@Computergaga Yeah, you're right. Maybe because I'm using it on nouns instead of numbers.

• Computergaga  10 months ago

There could be multiple reasons for this. Check what you have against the formula in the video. It should work fine on 2013.

• jeny xOx 11 months ago

Thank you for this!!

• MOHD HAFIZ BIN MURNEH - 11 months ago +1

THANKS BRO

• Rohit Singh 11 months ago +2

Thanks Mr. GaGa

• Clarence Alger 11 months ago +1

I know this is old, but still let me say: thank you! Works perfectly as I wanted!

• Zola Clark Year ago +1

This is a great tutorial, thank you!! I was running into trouble using conditional formatting and one of the predetermined features. While I was trying to find matches between two columns I realized that the one column had multiples of the same number and was counting that as a match even though it wasnâ€™t in the other column. Your method solves that problem, correct?

• Computergaga  Year ago

Correct. Using the formula instead of the in-built duplicate of Conditional Formatting it looks for a match in the other table and not the same column.

• Sajeev Kumar Year ago

HI i need to delete two same names, i use duplicate , but it remove only one name , i need to remove both names . Example ( boby, steve ,jeff, steve , evan ,eric ) need to delete both steve names . any formula please.

• Shane Year ago +1

Thanks buddy!

• Scott B Year ago +1

Sweet Jesus, I finally got this figured out thanks to you, bud. thanks!

• Mahesh Govind Year ago +1

Hi there, thanks for the video . Just want ask you how to do same thing if you have you data in 2 different sheet or workbook pls? Pls let me know .thanks.

• Computergaga  Year ago

Thank you.
If the tables were on different worksheets you could use a formula like this, where Sheet2!\$A\$2:\$A\$42 is the data on the other sheet.
=ISNA(MATCH(\$A2,Sheet2!\$A\$2:\$A\$42,0))
I believe it works across workbooks also by adding the workbooks name like you would in a formula. However I would get it into one workbook as it is more reliable. Power Query is a good one to bring data from different workbooks into one in a quick and updatable way. CF can then be run from this.

• David Givens Year ago +1

2018 and this is still helpful. Thanks!

• VARUN DUGGAL Year ago +1

Thank you sir

• Thanks for the video computer gaga. I have a request i have to find common names entries in two excel sheets 1 and sheet 2. I have done vlookup to get the common names, now i am facing the challenge that is sheet 1 has sheet one has name and sheet 2 has names with few more details. Now as i have got the common names by doing vlookup, now i need to add additional details along with the names from sheet 2. could you please help ?

• Computergaga  Year ago

You can then use VLOOKUP to bring across these details. Look for the name from Sheet1 on Sheet2 and return information from the necessary column. Say for example, you have an ID for each name down column A of sheets 1 and 2. And you want detail that is in column C on sheet2. You could write;
=VLOOKUP(A2,Sheet2!\$A:\$F,3,False)

• Sreenivas M Year ago

How to add multiple columns in excel 2007

• Computergaga  Year ago

You can add multiple columns in one go, as Excel will insert the same number of columns you select. For example, select columns C, D and E. Right click and insert Columns and you get 3 more columns.

• Ahsanul kabir Year ago +1

best method

• The Last Sifu Year ago +1

This helped me shave 15 minutes off of a daily task. Thank you!

• Manny R Year ago +1

Dude....... I blow everyone's mind that has a list that needs to be compared. I've gotten pretty creative with it as well. Saves me so much time!!! THANK YOU

• albatool social Year ago +1

This is what I am looking for, Thanks for tutorial video :)

• Joel Walker Year ago +1

Saved me a lot of time by not having to organize manually. Here is an example if anyone is trying to compare 2 columns with 2 other columns.
=ISNA(MATCH(\$A2&\$B2,\$AE\$2:\$AE\$40&\$AF\$2:\$AF\$40,0))

• Zakeer Hussain Year ago +1

This is exactly what I wanted.

• macmillan hanry Year ago

Dear Sir,
I am kindly requesting you to help me particularly in this section...... I have 2 Sheets in Book1.. Sheet1 has 6 Columns with data and 39 Rows With data and sheet2 has 6 Columns with data and 41 Rows with data. I want to find out differences one (Not Common) between these two Sheets Sheet1 and Sheet2.
Could you kindly Explain Formula In detailed way.. as early as possible
Sincerely Thanking you,
Basvaraj,

• macmillan hanry Year ago

Dear Sir,
Have you got Mail Sir?
Basavaraj

• macmillan hanry Year ago

Dear Sir,
Just Now I have sent a Excel File to Your Mail-ID.
Basavaraj

• Computergaga  Year ago

Sure mail it to me

• macmillan hanry Year ago

Dear Sir,
can I attach that Excel file Now to Your Mail ID? Easily... Fraction of second You can solve it.
can I Proceed.....? I am feeling that.... I am unable.
Willingly Thankful for all your Immediate Reply And It is my Gratitude to you.
Really It is Immense Pleasure for me and having wonderful conversation on this topic.
Thanking you Sir,
Basavaraj,

• Computergaga  Year ago

On your VLOOKUP. The A:F should be a single cell reference. So if the first record is in row 2 and the item you are looking for is in column A it should be A2. Copy that formula to the bottom and it will say #N/A if the item is on sheet 2 but not on sheet 1.

• Brigitte Meifeng Year ago

Thank you so much for this video. I have another question. Let say I have a record with 2 columns. ID and Title. The other record is ID and Url. How do I create an output with records that have matching ID, title and Url? Thanks.

• Computergaga  Year ago

Excellent work Brigitte.

• Brigitte Meifeng Year ago +1

Computergaga Got it to work. That is so awesome. Thanks!

• Brigitte Meifeng Year ago

Computergaga I will try that out. Many thanks!

• Computergaga  Year ago

You want to extract the uniques. So say you have a unique list of the ID and Title columns. Then use a VLOOKUP to fetch the URL for them.

• Brigitte Meifeng Year ago

Thank you but I have a question. Is there a way to extract the non duplicates? If I have thousands of rows, I don't want to scroll down to find the highlighted items and copy them one by one to separate them into another sheet? Thanks.

• Computergaga  Year ago

awesome isn't it

• Brigitte Meifeng Year ago +1

Computergaga -I didn't know that you can filter the column by color. Thanks.

• Computergaga  Year ago

once highlighted you can filter by colour. once filtered you can then copy these. That will save scrolling :)

• Carlos Alfonso Year ago +1

Thank you for this important lesson!

• B Prabhakaran Year ago +1

Very useful, Great Thanks!

• Saurabh S Year ago +1

thank you so much, it was really helpful.

• Jitendra Shejwadkar Year ago +1

Thank you.

• urroyalhynus Year ago +1

Thank you!!!!!

• Fernand Carignan Year ago +1

Just what I was looking for! Thank you very much, it can't be clearer!!

• Marisol Jones Year ago +1

THANK YOU!!!!!!

• crtlyksds Year ago +1

very informative

• John Anderson Year ago +1

Thanks!

• mendebil 2 years ago