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 & StyleHowto & Style

Comments • 261

  • Breathing Heart
    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
    Liberty Garden 2 months ago

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

  • FINANCE QUERY SOLUTION
    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
    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
      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
    Mohammad Ali Al-tamimi 4 months ago

    Thank you mate.

  • Verky
    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
      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
    Danyos777 4 months ago

    Wonderful strategy! Thank you @Computergaga

  • VFX Desi
    VFX Desi 4 months ago +1

    Thank God for your *existence* !

  • Kenneth Vela
    Kenneth Vela 4 months ago

    It doesn't work in my sheet

  • Salim Saba
    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
    Farid Albaloshi 5 months ago

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

  • gcNewd
    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
      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
    Deepak Bhardwaj 6 months ago +1

    Ty for this vedio.... keep posting

  • sathish kumar
    sathish kumar 7 months ago +1

    it was very useful

  • Solomon Kinyanjui
    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
      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
      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
      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
    Waheed Khan 7 months ago

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

    • Waheed Khan
      Waheed Khan 7 months ago

      Thank you very much

    • Computergaga
      Computergaga  7 months ago

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

  • swizlestick
    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
    Francis Santora 8 months ago +1

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

  • Jeremy Gaiser
    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
      swizlestick 7 months ago

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

    • Jeremy Gaiser
      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
    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
      Computergaga  8 months ago

      Great to hear. Thank you for your comments Cynthia.

    • Cynthia Szczepaniak
      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
      Computergaga  8 months ago

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

  • Ubaid Ubaid
    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
      Computergaga  8 months ago

      You're very welcome Ubaid, thank you.

  • Wanayn
    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
    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
    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
    Dolce Amore 9 months ago +1

    what a life saver!!! thnx bruh

  • Pune Pet Park
    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
      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.
    Liyana R. 10 months ago +1

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

  • SALEM Center
    SALEM Center 10 months ago

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

    • SALEM Center
      SALEM Center 10 months ago

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

    • Computergaga
      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
    jeny xOx 11 months ago

    Thank you for this!!

  • MOHD HAFIZ BIN MURNEH -
    MOHD HAFIZ BIN MURNEH - 11 months ago +1

    THANKS BRO

  • Rohit Singh
    Rohit Singh 11 months ago +2

    Thanks Mr. GaGa

  • Clarence Alger
    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
    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
      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
    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
    Shane Year ago +1

    Thanks buddy!

  • Scott B
    Scott B Year ago +1

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

  • Mahesh Govind
    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
      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
    David Givens Year ago +1

    2018 and this is still helpful. Thanks!

  • VARUN DUGGAL
    VARUN DUGGAL Year ago +1

    Thank you sir

  • Sidharrth Pillaii

    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
      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
    Sreenivas M Year ago

    How to add multiple columns in excel 2007

    • Computergaga
      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
    Ahsanul kabir Year ago +1

    best method

  • The Last Sifu
    The Last Sifu Year ago +1

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

  • Manny R
    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
    albatool social Year ago +1

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

  • Joel Walker
    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
    Zakeer Hussain Year ago +1

    This is exactly what I wanted.

  • macmillan hanry
    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
      macmillan hanry Year ago

      Dear Sir,
      Have you got Mail Sir?
      Basavaraj

    • macmillan hanry
      macmillan hanry Year ago

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

    • Computergaga
      Computergaga  Year ago

      Sure mail it to me

    • macmillan hanry
      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
      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
    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
      Computergaga  Year ago

      Excellent work Brigitte.

    • Brigitte Meifeng
      Brigitte Meifeng Year ago +1

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

    • Brigitte Meifeng
      Brigitte Meifeng Year ago

      Computergaga I will try that out. Many thanks!

    • Computergaga
      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
    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
      Computergaga  Year ago

      awesome isn't it

    • Brigitte Meifeng
      Brigitte Meifeng Year ago +1

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

    • Computergaga
      Computergaga  Year ago

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

  • Carlos Alfonso
    Carlos Alfonso Year ago +1

    Thank you for this important lesson!

  • B Prabhakaran
    B Prabhakaran Year ago +1

    Very useful, Great Thanks!

  • Saurabh S
    Saurabh S Year ago +1

    thank you so much, it was really helpful.

  • Jitendra Shejwadkar
    Jitendra Shejwadkar Year ago +1

    Thank you.

  • urroyalhynus
    urroyalhynus Year ago +1

    Thank you!!!!!

  • Fernand Carignan
    Fernand Carignan Year ago +1

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

  • Marisol Jones
    Marisol Jones Year ago +1

    THANK YOU!!!!!!

  • crtlyksds
    crtlyksds Year ago +1

    very informative

  • John Anderson
    John Anderson Year ago +1

    Thanks!

  • mendebil
    mendebil 2 years ago

    skip to 1:25. you are welcome