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 • 259

  • MR ABALONE
    MR ABALONE 18 days 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  17 days 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

    Thank you mate.

  • Verky
    Verky Month 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  Month 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 Month ago

    Wonderful strategy! Thank you @Computergaga

  • VFX Desi
    VFX Desi Month ago +1

    Thank God for your *existence* !

  • Kenneth Vela
    Kenneth Vela Month ago

    It doesn't work in my sheet

  • Salim Saba
    Salim Saba 2 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 2 months ago

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

  • gcNewd
    gcNewd 3 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  2 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 3 months ago +1

    Ty for this vedio.... keep posting

  • sathish kumar
    sathish kumar 4 months ago +1

    it was very useful

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

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

    • Waheed Khan
      Waheed Khan 4 months ago

      Thank you very much

    • Computergaga
      Computergaga  4 months ago

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

  • swizlestick
    swizlestick 4 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 5 months ago +1

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

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

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

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

      Great to hear. Thank you for your comments Cynthia.

    • Cynthia Szczepaniak
      Cynthia Szczepaniak 5 months ago +1

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

    • Computergaga
      Computergaga  5 months ago

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

  • Ubaid Ubaid
    Ubaid Ubaid 5 months ago +2

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

    • Computergaga
      Computergaga  5 months ago

      You're very welcome Ubaid, thank you.

  • Wanayn
    Wanayn 6 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 6 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 6 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 6 months ago +1

    what a life saver!!! thnx bruh

  • Pune Pet Park
    Pune Pet Park 6 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  6 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

  • missXstreet
    missXstreet 7 months ago +1

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

  • SALEM Center
    SALEM Center 7 months ago

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

    • SALEM Center
      SALEM Center 7 months ago

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

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

    Thank you for this!!

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

    THANKS BRO

  • Rohit Singh
    Rohit Singh 8 months ago +2

    Thanks Mr. GaGa

  • Clarence Alger
    Clarence Alger 8 months ago +1

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

  • Zola Clark
    Zola Clark 9 months 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  9 months 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 9 months 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 9 months ago +1

    Thanks buddy!

  • Scott Bell
    Scott Bell 9 months ago +1

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

  • Mahesh Govind
    Mahesh Govind 10 months 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  10 months 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 10 months ago +1

    2018 and this is still helpful. Thanks!

  • VARUN DUGGAL,BAWU
    VARUN DUGGAL,BAWU 10 months ago +1

    Thank you sir

  • Sidharrth Pillaii
    Sidharrth Pillaii 10 months ago

    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  10 months 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)

  • Sreenivasa Rao Medida
    Sreenivasa Rao Medida 10 months ago

    How to add multiple columns in excel 2007

    • Computergaga
      Computergaga  10 months 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 10 months ago +1

    best method

  • The Last Sifu
    The Last Sifu 11 months 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!

  • Isaiah Kronstad
    Isaiah Kronstad Year ago +1

    pERFECT!

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

    skip to 1:25. you are welcome

  • Ravi Shingala
    Ravi Shingala Year ago +1

    Loved this video even more because you taught it in two different ways. And of course, love the accent :)

  • Welleh Kie
    Welleh Kie Year ago +1

    OH YOU WON'T BELIEVE HOW YOU SAVE ME!

    • Computergaga
      Computergaga  Year ago

      Thanks for the comment Welleh, always good to hear nice feedback.

  • Rajesh FJHS
    Rajesh FJHS Year ago +1

    Thank You Bro

  • Zeeshan Ali
    Zeeshan Ali Year ago

    if we don;t have primary key but combination of first name. last name and city, how do we go about it

    • Computergaga
      Computergaga  Year ago

      Not a problem, Zeeshan.

    • Zeeshan Ali
      Zeeshan Ali Year ago +1

      Aah, I should have thought of that, thanks for your response!

    • Computergaga
      Computergaga  Year ago

      You could create a new column containing the firstname, lastname and city. Then concatenate them in your lookup like in this video - thexvid.com/video/ZqWTsmk2Jk8/video.html

  • Omoloye Awojobi
    Omoloye Awojobi Year ago +1

    Thanks for your video - saved me hours of manually trawling through my spreadsheets

  • Ly Spooner
    Ly Spooner Year ago

    This is really great. What I would also love to know is how to highlight the individual cells that might be different from the ones that match the row on the id, and also to add new records with a blank id, in order to maintain school/course membership lists.

  • Kelsey Hamman
    Kelsey Hamman 2 years ago +1

    As everyone else stated, THANK YOU!! Huge help!

    • Computergaga
      Computergaga  2 years ago

      Your welcome Kelsey. Thanks. Have an awesome day.

  • Khan Khan
    Khan Khan 2 years ago

    how to give numbers to highlighted cell in some other column ? ?

    • Khan Khan
      Khan Khan 2 years ago

      Thanks. But i figure it out. using sorting and then giving numbers.

    • Computergaga
      Computergaga  2 years ago

      Not sure what you mean Khan. If a cell is highlighted then assign a value to it in a different cell? Sounds like you need a formula in that other cell. Maybe a IF function.

  • Benia Munoz
    Benia Munoz 2 years ago

    What if in column A you had duplicate records. I tried the =match formula but it highlights all duplicate values in column A even though they aren't in column F OR it doesn't find any duplicates.

  • Flavius Totorean
    Flavius Totorean 2 years ago

    hello, i followed the instructions step by step, but for some reason it doesnt work. i'm using MS Office Profesional Plus 2010. Is there any condition regarding a previous formating of columns "a" and "f", as seen in your video?? Maybe they should be formated as number, or text, or ... I was trying the MATCH function, and it said that the formula contained an error. Any idea? Thanks

    • Computergaga
      Computergaga  2 years ago

      Argh! Frustrated I didn't think of that. Some European countries use the semi colon as opposed to the comma in as an argument separator.
      Good work Flavius.

    • Flavius Totorean
      Flavius Totorean 2 years ago +1

      finally! it works :) the problem was the separator inside the formula (its " ; " instead of " , " ). Thanks a lot! Now i can go home :)

    • Computergaga
      Computergaga  2 years ago

      Hard to say without seeing it. You are quite right though, it is a good idea to check that the formats of the value you are looking for, and the list of values you are looking in are the same.

  • Jen and Phils Wedding
    Jen and Phils Wedding 2 years ago +7

    you are a lifesaver!!!!!!!!!!

  • Shivam Anshiv Goyal
    Shivam Anshiv Goyal 2 years ago

    Hello Sir/Madam
    Myself Shivam Goyal from Faridabad Haryana India, I have an problem in comparing two columns in a excel worksheet .I am working in this Worksheet for 6 Months approx , i have used or tried all the possible methods for comparing these columns but no desired results found. I have already applied or applied these methods (Vlookup , Conditional Formatting, Countif etc) but unable to find results. Please help
    Shivam Goyal
    Mob: 8447270294
    Email: shivamgoyalvip1@gmail.com

    • Tinisha B
      Tinisha B Year ago

      What this ever resolved? I"m having the same issues.

    • Computergaga
      Computergaga  2 years ago

      You can message me on Facebook - facebook.com/Computergaga or via admin@computergaga.com

    • Shivam Anshiv Goyal
      Shivam Anshiv Goyal 2 years ago

      Sir
      Please provide me your email id so that i can send you the attachment , to prove that excel comparitive formulas fails on that sheet.
      what i want to is, i have two lists and i want to find out which of the data in list B is also Present in A and Vice -Versa. Thats all..
      thanks for your attention.
      Shivam

    • Computergaga
      Computergaga  2 years ago

      How do you want to compare them Shivam. Are you looking for duplicates or uniques?
      Send me an email and I'll try and help.

  • Fredrik Jeanson
    Fredrik Jeanson 2 years ago

    Hi Computergaga! I found this really helpful, I understand a lot more now, but I get an error after I click ok, where Excel thinks I'm not trying to write a function. I'm using Excel 2016, don't know if that changes anything... But has this happened to you before and if so how did you fix it?

    • Computergaga
      Computergaga  2 years ago

      Legendary work Fredrik

    • Fredrik Jeanson
      Fredrik Jeanson 2 years ago +1

      edit: problem solved!

    • Fredrik Jeanson
      Fredrik Jeanson 2 years ago

      Thank you! My error disappeared, but I didn't get the exact result I wanted even though it was somewhat what I wanted, so I'll keep experimenting! But thank you for the help

    • Computergaga
      Computergaga  2 years ago +1

      Excel 2016 should not cause any problems. You may need a semi colon ; in between the function arguments instead of a comma , if not that then I would check the brackets.

    • Fredrik Jeanson
      Fredrik Jeanson 2 years ago

      I should mention I did exactly as you did in the video,
      the error/warning says:
      There's a problem with this formula
      Not trying to type a formula? Excel recognises things that start with (=) and (-) as formulas

  • Ryan Klemm
    Ryan Klemm 2 years ago +1

    Very helpful. Thanks

  • Rich Giles
    Rich Giles 2 years ago

    Excellent, thank-you!

  • Suryakant Tikare
    Suryakant Tikare 2 years ago +1

    This video is of great help for me.

  • Megan Mooi
    Megan Mooi 2 years ago

    Hi... Great video! Thanks.
    Quick question: What if I didn't want to find cells with the exact match but rather cells that "contained" or "included" the text?

    • Computergaga
      Computergaga  2 years ago

      Hi Megan, thank you so much.
      Assuming the text that you want to ensure was included is in cell A2 and your looking in range B2:B44 you could use the below. The * is a wildcard that replaces any text.
      =MATCH("*"&A2&"*",$B$2:$B$44,0)
      I hope this helps
      Alan

  • Cosmetic Dental Patients
    Cosmetic Dental Patients 2 years ago +1

    Computergaga - Good job!!!

  • mezoani
    mezoani 2 years ago +1

    Thank you so much for the vid, it was a big help!

  • Victoria Lee
    Victoria Lee 2 years ago +1

    Hi! Thank you so much for your response. But if the sheet has hundreds of entries is will be very time consuming to delete each manually. Request: can you teach us how to create such macro? Your teaching is very clear & concise & excellent. THANK YOU!

    • Computergaga
      Computergaga  2 years ago

      On my list to do. Will produce a video tutorial soon.

      With the technique in this video you can sort the list by colour prior to deleting to group them together.

  • Victoria Lee
    Victoria Lee 2 years ago +1

    This is an excellent tutorial! Thank you. Are you able to deleted automatically those enteries that are non-duplicate? If so, can you teach us how?

    • Computergaga
      Computergaga  2 years ago

      To do it automatically you would need a macro. Otherwise when the rows are highlighted in this tutorial, you can sort by colour and then delete the duplicates. That would take seconds, but it is not automatic.

  • Odam TJ Ly
    Odam TJ Ly 2 years ago

    that was awesome
    How can I count the total of match and no match?

    • Computergaga
      Computergaga  2 years ago

      If the cells of the unmatched are empty you can use the COUNTBLANK function.

    • Odam TJ Ly
      Odam TJ Ly 2 years ago

      sorry to bother but just one more question
      how can you count if it not match? to count how many items does not match

    • Computergaga
      Computergaga  2 years ago

      Your welcome

    • Odam TJ Ly
      Odam TJ Ly 2 years ago +1

      Thx alot Computergaga you are the best for a starter like me

    • Computergaga
      Computergaga  2 years ago

      Thanks Odam,
      We would use a formula instead of Conditional Formatting. There are a few ways but this video uses COUNTIF to compare lists - thexvid.com/video/HUWrnLOOGsQ/video.html

      You could then count the 1's to identify the number of matches.

  • Reza Talebi
    Reza Talebi 2 years ago

    Thank you!

  • L K
    L K 2 years ago +1

    Awesome! You the man!

  • Riaz mahmood
    Riaz mahmood 2 years ago

    Hello,Is there a way to include in a new column and state that it is a "Match" or a "No Match"?

    • Computergaga
      Computergaga  2 years ago +1

      Hi Riaz, yes using the example from the video, in a column I could use the formula below

      =IF(ISNA(MATCH(A1,$F$1:$F$19,0)),"No Match","Match")

  • Muhammad Yasir
    Muhammad Yasir 2 years ago

    There are 3 ways to Compare Excel Sheets or Workbooks.
    1) 1st & the Best : Vlookp(The Most Powerfull Excel Tools).
    2) Conditional Formatting,Make New Rules for Formating by using this(=A1Sheet2!A1)&Setting Format(In my Case i have sheet1 & sheet 2 for Comparison).
    3)Match Function at Conditional formating...

  • Star Lily
    Star Lily 2 years ago

    hi, i just tired it. But the whole culumn been highlighted, even if the number doesn't match, May I know the reason? Thank you.

    • Computergaga
      Computergaga  2 years ago

      Sure, if you send me what you have I may be able to help

    • Star Lily
      Star Lily 2 years ago

      Computergaga is any way I can show you in image?

    • Computergaga
      Computergaga  2 years ago

      I need to see what is happening to know. There will be a reason.
      Click Conditional Formatting and Manage Rules. There may be a conflicting rule.

    • Star Lily
      Star Lily 2 years ago

      Yes, exactly the same procedure and formula, but it still appear that highlighted all cells instead of the Match ONE.

    • Computergaga
      Computergaga  2 years ago

      Formula should read =MATCH($A2,$F$2:$F$42,0)

      I would double check the formula and the table layout. Is it the same as mine? The cell references may need changing.

  • mirin jawbro
    mirin jawbro 2 years ago

    I need to match a table with criteria of plus or minus 10 percent (this is for an OTIF report) any ideas?

  • Rune Tonseth
    Rune Tonseth 2 years ago

    HI Nice Video. But it is f....frustrating. Im trying to compare and match two culoms to save time. But everytime I do this I get a error message. The Forumula automaticly get a + sign: =match($B2+,$D$2:$D$9,0) I remove that and I get an ERROR message. " We Found a problem with this formula. Try Clicking Insert function on the forumla tab to fix it.... bla bla bla bla
    What am I doing wrong I follow all the steps..... I havent saved time I have to do it manually...:) But I wont give up

    • Computergaga
      Computergaga  2 years ago

      Ha Ha, Job done though Rune

    • Rune Tonseth
      Rune Tonseth 2 years ago

      Hi Do you have an email adress? I can send you a printscreen. Not from the document but Im trying to learn this thing so im testing it on just a test sheet

    • Computergaga
      Computergaga  2 years ago

      Strange. You sound well equipped to be able to solve it. If you use VLOOKUP, this formula does not stand a chance.

      Drop me a message if you need extra help.

    • Rune Tonseth
      Rune Tonseth 2 years ago

      Im familiar with Excel I use it everyday. Im used to create vlookup etc but have never used the match function.

    • Rune Tonseth
      Rune Tonseth 2 years ago

      HI I delete the + sign and hit enter. But get the alert commented. I might restart the machine.

  • dollno5
    dollno5 2 years ago

    Ok, this is the third time I've checked out this video. I've solved my excel problem, but do you know that you sound just like Jason Statham? Nice.

    • Computergaga
      Computergaga  2 years ago

      Awesome, Jason Statham wishes he knew this Excel stuff :)

  • Asifgraphy com
    Asifgraphy com 2 years ago +2

    Boss Thank you so much for uploading such a great Video.

  • Niloy Sadat
    Niloy Sadat 2 years ago

    really helpful thanks man, tomorrow i will check with my datas

  • Ashwat Nagarajan
    Ashwat Nagarajan 2 years ago

    i really liked this video, thank you :)

  • Ran Shir
    Ran Shir 2 years ago

    You the man, thanks!

  • Robert jirsaraie
    Robert jirsaraie 3 years ago

    Really Helpful. Thank you

  • Jagu Kulkarani
    Jagu Kulkarani 3 years ago

    SIR PLEASE HELP ME I WILL UPLOAD ONE EXCEL FILE AS U SAID I DID EXACTLY BUT IT IS NOT WORKING PLZ GIVE EMAIL ID

  • Caz M
    Caz M 3 years ago

    thank you! i had to bump up two data sets of students looking for duplicate records today so i could adjust funding for students on both lists. by using the match function i was able to locate the dupes in a spreadsheet that had about 3000 line items very quickly!

  • Justin Howell
    Justin Howell 3 years ago

    This really helped me! Thank you so much for posting this video!

  • Mike Rice
    Mike Rice 3 years ago

    Great tutorial. How can i get this function to recognize text selected from a drop down list on a separate sheet?

  • Tanner Tucker
    Tanner Tucker 3 years ago

    I guess one can use two conditional formatting formulas: one to find the match, and a second to find the unmatched rows?

    • Tanner Tucker
      Tanner Tucker 3 years ago

      +Computergaga Thank you. I though it would be "nice" to shade matches with one color and non-matches with a different color.

    • Computergaga
      Computergaga  3 years ago

      +Tanner Tucker Absolutely! You can have unlimited Conditional Formatting rules per cell. The NOT function can be used to reverse the criteria.

  • Tanner Tucker
    Tanner Tucker 3 years ago +1

    Thank you, this is excellent!!

  • jazzista1967
    jazzista1967 3 years ago

    greattt!!! that's what I needed..