Comments on: How to compare data in two Google sheets or columns

Whether there's summer knocking on our doors or winter invading Westeros, we still work in Google Sheets and have to compare different pieces of tables with one another. In this article, I'm sharing ways of matching your data and giving away tips on doing that swiftly. Continue reading →

Comments page 2. Total comments: 217

  1. Hi!
    I am looking for a formula to find the sum or difference on separate google sheets (each sheet is a monthly report). Each sheet has names and a number value. Trying to identify if the number values increased or decreased from month 1 to month 2 for each name. The names are not always the same and don't always match up by cell each month, complicating the issue for me.

    A2:A192 are the names
    C2:C192 are the number values

    1. Hi Kel,

      For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task.

      1. Thank you!

        I've shared a "Sample Sheet" with the above contact, it consists of the 2 google sheets and the expected result sheet. I also have 2 sheets with pivot tables based on the 2 sample sheets, as not sure it it would be easier to solve from the pivot or source data sheets.

        Hope this is all the information you need, if not, please let me know, thanks so much!

        1. I've got the file, Kel, thank you.

          Based on your result example, I've created a couple of formulas that do the same. You will find them in the Expected Result sheet, cells J4 & K4. I used SUMIFS and UNIQUE functions. Hope this is what you need!

          1. Thank you Natalia, this looks like it should work great!

              1. hello Natalia, i had share you the sheet too, cn i've ur kind assistance too? i also have the same issue. just shared you the google sheet!

                Trying to identify if the number values increased or decreased from month 1 to month 2 for each name. The names are not always the same and don't always match up by cell each month, complicating the issue for me.

              2. Hello Zhi,

                Thank you for sharing the spreadsheet right away. I've looked into it and created the formulas to solve your task on the last sheet - Copy of Sheet1 - Ablebits. You will see the formulas in cells J2 & K2. I used UNIQUE, ARRAYFORMULA & VLOOKUP functions to solve the task.

                Before building the formulas, I also used our Combine Duplicate Rows to total numbers for any duplicates your monthly tables may contain.

  2. Hi Natalia!

    Thanks so much for these, please can you help me?

    I have two Google sheets of names. I need to look one list of names up against the other to see if any have left our business. I need to highlight the names in sheet one, that aren't in sheet two, so I can call them.

    Which formula would be best for me please? Thanks so much for your help in advance!

    Sarah

  3. Hi,

    I'm hoping you can help me, I'm not sure what function to use (or if it's even possible). I have a long list of ingredients condensed in one cell separated by commas and I need to verify it against a list of other ingredients (individually listed in a column). How can I go about this ?

    1. Hi Ariane,

      Try this formula:
      =ArrayFormula(IF(ISERROR(VLOOKUP(TRANSPOSE(SPLIT(A1,",")),$B$1:$B$20,1,0))=FALSE,"Duplicate",""))

  4. Hi,

    I need to change the colour of the name in a list on sheet one to orange if it appears in list 2 (Sheet 2) and Blue if it appears on list 3 (also on sheet 2). What is the best way to do this?

    I also have multiple lists on sheet 1 that will need to do the same, still based on the content of sheet 2.

    Thanks

    Tom

  5. Thank you so much!

  6. Hi Mam,
    I have a sheet of my school lab testing reports. There are many samples in that sheet with their properties like Density mass and volume. These samples have their standard properties .

    for eg.

    Name Density Volume Mass
    Sample A 5 24 9
    Sample B 6 35 10

    and the standard range of these samples are Sample A Density- 4 Volume- 25 Mass- 9
    Sample B Density- 6 Volume- 37 Mass- 9
    So i want to add a formatting based upon the product if Sample b has greater values above its standard it will be automatically highlighted .
    so after that formatting according to me in sample A 5 will be highlighted (because its above standard)
    and in sample B Volume 36 and mass 9 will be highlighted. I think you understood.

    1. Hi Deepak,

      If I understand you correctly, you need to create a conditional formatting rule that will check if values in your sample sheet are greater than particular records on a sheet with the standard numbers. A similar case with the examples of the rules is covered in this blog post.

      1. sorry to say but my case is different......
        I have 4 columns like Name Density Volume Mass
        all sample have their different results and but I want to format if the the sample properties values highlights if the properties are more with standard.

        Now tell me how to add formatting....means first i want to add standard value for the sample names and highlights if they are above limit with respect to standard

        1. For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets:

          1. a copy of your source data
          2. the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description)

          I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

          Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it and try to help.

          1. shared just now please check

              1. Thank you for helping.
                =VLOOKUP($B2,B2:E10,2,0)>VLOOKUP($B2,INDIRECT("Standard!A1:D7"),2,0)

                will you please tell me what is 2,0 here?
                Also please tell me the meaning of each function here.

              3. Dear Mam,

                I am thankful for the response but its working only for the Mass row...and also whenever the standard changing its not getting changed automatically

              4. I'm sorry, I forgot to mention that I applied the rule to the 'Mass' column only. You just needed to create the same rule for 'Density' and 'Volume' columns. I've just copied the formula for you into those columns as well, please take a look.

              5. Dear Mam,

                Its not working, whenever i change the value in standard increase or decrease there is no automatically change...also whenever i do the same in the results of product tab there also it does not work

  7. What an amazing article! But unfortunately I just can't get your instructions to Highlight duplicates in two sheets to work. I have two sheets, one called USD and the second one CNA. On column A of each of them I have a set of dates. I'm trying to have the dates that are duplicates when comparing both sheets to be highlighted on the USD sheet. For that I did the following:

    -Selected column A on the sheet called USD
    -Conditioning format ranged from A2:A999
    - Custom formula =A2=INDIRECT("CNA!A2:A")

    Unfortunately it does not work, non of duplicated dates across sheet USD and CNA is highlighted. What am I missing here?

    Thank you for you time!

    1. I think I got it, conditioning format on sheet USD and the formula bellow did the trick. Thank you again for the article.
      =COUNTIF(INDIRECT("CNA!A1:A");$A1)=1

      1. Thank you for your feedback, Luiz! The way with the INDIRECT that didn't work looks for duplicate records on the same row. If your task is to check for duplicates regardless of their position in a column, you did the right choice with the COUNTIF :)

        1. How would you get that formula to ignore empty cells. I am using it for conditional formatting, it works, it highlights what I want but also the empty cells.

          =COUNTIF(INDIRECT("CNA!A1:A");$A1)=0

          1. Hello Sam,

            For me to be able to help you better, please consider sharing an editable copy of your spreadsheet and a rule with us: support@apps4gs.com

            Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

            I'll do my best to help.

  8. Just wanted to say thank you so much for this article! It really helped me with a problem at work ?

  9. Actually I need your help. I am trying to fetch data from one sheet to another by comparing name on both the Sheets with the name.
    1. I want to get the row where the name "Syed Faizan Ali" and also the name is case insensitive . there is no matter the name is exist in wherever column.
    2. Then after fetching the row where "syed faizan ali" case insensitive exist. I want to compare the details from my another sheet with this sheet. And get the matched data and also want to get unmatched data.

    please help. I tried many formulas. But I failed.

    1. Hello Amin,

      If I understand your task correctly, for its first part you can use one of the methods described here: Pull matching data. However, since you have lots of small tables on each sheet and the name appears in a different column in each table, you will have to create separate formulas for each table because you need to tell the function where to look for that name.

      The same goes for comparing. You can use the ways described here: Compare two columns or sheets. But again, you will have to compare each pair of tables separately since all tables have different structures.

    2. Hello Amin,

      I've got your spreadsheets and will look into them as soon as possible. Thank you for understanding.

  10. How to Highlight Duplicates from Two or Three Sheets?

  11. Hi,

    I'd like a formula to intersect 2 columns.
    For instance, if I have A B C D E in column A and K E T A M R G in Column B, I'd like A E in column C

    Thanks for your answer

    1. Hi Christophe,

      I'm sorry but your task is not clear. For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  12. Thank you for this!! I needed to use conditional formatting to match two columns, could not figure it out for the life of me, and your explanation has made it so simple :)

  13. hi there
    i want to know the exact formula to find similar data in "one" column
    mean find similar cells in just one column not 2 or more
    just one
    we have any formula for this?

    1. Hi Atousa,

      You can either identify duplicates without the 1st occurrences using a formula like this:
      =IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "")

      or count duplicates by the example of this article for Excel.

  14. Hello there! This is a great reference, but I'm having issues comparing lists of differing lengths. I have one list that is 400 records, the other list is 2600 records. For whatever reason, I get false positives (or negatives depending on how I run the formula). Is there a way to construct the formula to account for such vastly different list lengths that won't return false pos/negs?

    1. Hello Isaac,

      Well, you could incorporate IF and compare only non-empty cells. But that actually depends on the way you chose to compare your lists. Can you please give examples of the contents of your lists and specify the formula you're using for comparison?

  15. Hi Natalia,

    How about if you compare values on different columns?

  16. How can I check duplicate across all sheets? Let say I got 5 different sheets. Thank you

    1. Hello Maricel,

      I'm afraid you will have to create formulas to compare each sheet against all other sheets. You'll be able to do that much faster with our add-on.

      You can also use Scripts instead. But since we don't cover the programming area, you may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links.

      Or you can combine all tables into one first and then look for dupes/uniques within a sheet.

  17. I want to compare 2 google sheets i.e.one is old sheet and the other is new sheet and having huge amount of data . I want to highlight those values/rows that are modified and deleted in the new sheet by comparing with old sheet.
    What formula can I use in conditional formatting to highlight it in the new sheet itself?

    1. Hello Manisha,

      If I understand you correctly, it looks like you need to highlight unique values remaining in the old sheet (that are no longer in the new sheet). Here's a formula for you to try on the old sheet:
      =COUNTIF(INDIRECT("New sheet!$A$2:$A$10"),$A2)=0

  18. Thanks so much for the quick answers.

    I've put the following code in column "c"

    =INDEX(E2:E17400 , MATCH(M2&"*", M2:M4901 , 0))

    I get this error

    Parser error in formula.

    The ranges are correct though..

  19. The Excel logic in the link also doesn't solve the issue.
    Because it is all based on the fact that the matching queries are on the same row.
    I'm searching for matching queries in all the rows of a column.

    I'm stuck on how to fix this problem...

    1. Hi Quinten,

      Thank you for the confirmation. If you're going to use formulas, you can't build them in column A while processing column A. You'll have to use a helper column and put formulas there.

      1. First, pull data from column A based on partial matches in column B using INDEX/MATCH:
        =INDEX(A2:A7,MATCH(B2&"*", B2:B7, 0))
      2. Now the new column features only those names from column A that also appear in column B. But these names may duplicate themselves (if INDEX/MATCH found matches a few times). To remove duplicates, first convert formulas to values. Then go to Data > Remove Duplicates to remove duplicated names.

      Hope this helps!

  20. Hi,

    I have thousands of data in 2 columns

    First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the latest activity)

    I stuck in finding a way to get all the email addresses from the latest activity and check where they are in the export list.
    ***And important sidenote: the latest activity list is incomplete at the end of each query. As the example below.***

    name1@mail.country name3@mail.c
    name2@mail.country name4@mail.coun
    name3@mail.country name6
    name4@mail.country name1@ma
    name6@mail.country name5@mail.country
    name5@mail.country name2@ma

    I need to **find** and **keep** all the duplicates and **remove** all the others

    Is this something I can do in Excel automated instead of manually finding each one by `cmd+f`.

    I have about 5000 active users I need to filter out.
    *fingerscrossed*

      1. Sorry for the confusion! I work in Sheets.

        I automatically typed Excel out of common habit.

  21. If I have have a list of 8 digit numbers in lets say column A1:A50 and i have a similar (but slightly different) list in column B1:B50, how can i check if the list of numbers in A1:A50 match/occur with B1:B50?

    Als i am using google drive Excel for this in Dutch

    1. Hello Ash,

      The ways described in this blog post work with complete matches only. As for your records, could you please give me an example of numbers from column A and their similar values from column B?

      1. Thank you for your reply, here is an example:
        A B
        12178715 12178841
        12178784 12179559
        12182831 12181891
        12182835 12182921
        12182843 12183293
        12182908 12183535
        12183449 12183859
        12183486 12183899
        12183508 12184062
        12183871 12184377
        12184246 12184884
        12184345 12184908
        12184681 12184955
        12185347 12184959
        12185363 12185770
        12185454 12186686
        12185471 12186958
        12185627 12188055
        12185671 12188133
        12185719 12188145
        12185760 12188219

        1. Thank you, Ash.
          And sorry for the late reply.
          I can see that only 4-5 first characters are the same between these columns. So how would you like them to match? For example, which one from column B should match the first number in column A?

  22. Hello!
    I wanted to match 1 col with 1 item and place value from other Col into specified col.
    So I was able to do that with =SUMIF(A1:A100,C1,B1:B100)

    But now when I change col A to Dropdown list items and using this formula its giving completely wrong values.
    Anyone know any specific formula for doing this with dropdown values?

    1. Hello Shujaat,

      A drop-down is a single cell with only one record available at a time. The formula can only look at whatever record is currently selected from the drop-down. Thus, if you want to test all 100 cells in column A against the SUMIF criteria, I'm afraid the drop-down is not an option.

  23. Hi Nat

    can you please help me with below scenario

    I have to check for 3 column ( like ID, date, Amount) from file 1 and if these 3 column matches with file 2 then return Transaction_no to 4th Colum from file 2 to file 1 else no match return "not found/ pending"

    1. Hi Ashwini,

      Since you need to match two tables by multiple columns and pull the related data, I'd advise you to use INDEX MATCH for the task. However, to reference another file, you will have to embed the IMPORTRANGE function as well.

      If using formulas is too complicated, we have the add-on that is perfect for the task: Merge Sheets.

  24. I have 4 different people using 1 google sheet each. In column B of each sheet is the unique id (expressed as a number if this matters) of each client. I want to be able to see where there are duplicates of any client id in any of the 4 sheets.
    By this I mean, if sheet 2 has client Z and sheet 3 has client Z, I would like this highlighted. I know how to use conditional formatting to do this on one google sheet.
    However, to do this across the 4 sheets, I thought that I would create a 5th google sheet and import all the data in column B onto sheet 5 then have the same conditional formatting.
    My issue now...
    I am using this formula
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1x8xd2nWW-YaS0SlmvKO-gF8EDzxQfFcKxA2Iag4L4gE/edit#gid=0", "B2:B700")
    and this works for the data on sheet 1
    When I try to do the same with sheet 2, I am having issues. I have tried the below formula but it is having issues. Error codes.
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1x8xd2nWW-YaS0SlmvKO-gF8EDzxQfFcKxA2Iag4L4gE/edit#gid=0", "sheet2!B2:B5")
    The above is even an error code when I try to take the url for the second sheet and plug that in.

    Any ideas? Much appreciated!!

    1. Hello Robert,

      Please look through this explanation on how to use the IMPORTRANGE correctly.

      If you're still getting errors even after following those steps, please specify what errors you're getting exactly. Also, if possible, create a shortened version of your spreadsheet and share it with us (support@apps4gs.com) for checking. NOTE. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  25. Hi. I want to compare columns and find duplicate values between two sheets, can you please help me with it?

  26. I have two lists of names. On one sheet the name is in one column (cell) and in the other sheet it is split into 2 or 3 columns. I need to highlight the names from the single column (Doe, John A) to the sheet that has them as (Doe) (John) (A).

    1. Hello John,

      If you want to skip the steps where you merge name parts in a helper column, you will have to do that directly in your conditional formatting rule formula. It may look like this:
      =MATCH(CONCAT(JOIN(", ", $C2:$D2),IF(COUNTA($E2)>0," "&JOIN(" ",$E2),"")),INDIRECT("Sheet4!$A$2:$A$30"))>0

      Where C2:D2 and E2 are name parts (the one that is split) and INDIRECT references a single column with names in another sheet.

  27. Some please help with the formula/approach for this : How to find Cells containing at least one of the same words in another cell.
    Example CELL A contains Smith Dean K and CELL B contains Smith D Kane. In this case the result should be TRUE because both CELL A and B contains 'Smith'

  28. Hey,
    You guys are great helping everyone with these formulas... hope you could perhaps help me as well.

    I have a google sheets file with data entered in two columns. The data in both columns is separated by commas as such: abc,def,ghi

    So, it's possible to have "abc" for example both in column A and column B.

    I would love to be able to use a formula in column C to tell me if that specific row contains duplicates between column A and B.

    The data is scrambled so can't use any of the formulas I've been reading here.

    Any idea ? thank you in advance for the assistance :-)

    1. Hello!
      If I understand you correctly, you cannot change anything on your sheet. In this case, I cannot help you. Try making a copy of the data to a new sheet and working with it.

  29. I'm trying to compare two lists of account names to find duplicates. Problem is, the two lists have some accounts that have slightly different names (for example, "Company" and "Company LLC" do not show as duplicates). I've used the highlighting option from the last Example 2 on this page, but it only highlights duplicates that are EXACT matches.

    Is there any way to highlight/identify duplicates with similar names or partial matches?

    1. Hi Chris,

      You're right, these formulas are for exact matches. For partial matches, please try the following one assuming you compare columns A and B:
      =ArrayFormula(IF(LEN(A2:A10),IF(COUNTIF(B2:B10,"*"&A2:A10&"*")>=1,"Yes","No"),))

  30. hello i have a trouble about google sheet

    i am trying to compare 2 data from 2 different sheet (same workbook)

    long story short, i want to compare SKU data from sheet "4" column H4:H and compare with sheet "Jangan Di Sentuh" colomn "N2:AH347"

    if the data from H4:H can not be found in data N2:AH347" then highlight with red color

    i have been using this formula but always "invalid formula" in conditional formatting feature

    =ISNA(MATCH(H4,'Jangan Di Sentuh'!N$2:AH$347,0))

    =ISNA(MATCH(H4;'Jangan Di Sentuh'!N$2:AH$347;0))

    any help please

    1. Hello Santo,

      Here's what you need to know:

      1. MATCH can process only one-dimensional arrays: 1 column or 1 row at a time, so you need to shorten N$2:AH$347 to N$2:N$347.
      2. Direct cell references to other sheets don't work in conditional formatting. You need to use indirect references instead. We described them here.

      So here's the formula I would try:
      =ISNA(MATCH(H4,INDIRECT("'Jangan Di Sentuh'!N2:N31"),0))=TRUE

      1. What if instead different sheet, i make different workbook? so everytime there is new SKU, i didnt have to change every workbook SKU List? (i have 8 workbook from 8 different marketer)

        can i combine it with importrange like this?

        =ISNA(MATCH(H4,INDIRECT(=importrange("https://xxx","'Jangan Di Sentuh'!E2:E1427")),0))=TRUE

        1. Susanto,

          I'm afraid IMPORTRANGE doesn't work in conditional formatting. Instead, you can use this function to pull all 8 sheets into one file, and then use INDIRECT in conditional formatting to highlight data.

          1. oh... but many thanks for your help.... you help me a lot. wish you have a great day

      2. MANY THANKSSSSSSSSSSSSSSSSSSSSSSS YOU SAVE A LOT OF MY TIME !!!!!!!!!!!
        IT WORKS LIKE CHARMMMMMMMM

        Every end of the months i need to check 1 by 1 each SKU, every month i have to check minimum 5000++ transactions just to make sure the SKU is correct. You save my lifeeeeee

  31. Wow, some of the comments you answered are amazing. It's crazy how difficult some are.
    I am hoping you can solve mine.
    Conditional formatting in Google Sheets, many columns. If the cell in the right column is greater than the cell in the left column, highlight it. Continuing that across about 50 columns each individual cell compared to the previous and highlighted only if greater than the left cell.

  32. -Names- -Emails- -Rate- -Output-
    Alan a@mail.com $60
    Belle a@mail.com $65
    Cera a@mail.com $70
    Dan b@mail.com $65
    Eleanor b@mail.com $65
    Frank b@mail.com $70
    Gideon b@mail.com $65

    HI!
    I am currently looking for help on this issue im having. Is there a way to:
    Step 1) Find all the emails in column 2 that are exact matches (all are sorted in asc orders using query function).
    Step 2) Compare the rates of those exact matches
    Step 3) Give discount to the lower rates and output the calculated amount in Column 4

    eg.
    There happened to be 3 accounts using a@mail.com. The rates are $60, $65 and $70 accordingly for those 3 accounts. 5% would be given to the $60 and $65 (as there are the lowest compared to the account with the rate $70.). Then repeat the steps for the next few aacounts under b@mail.com. Etc etc.

    Thank you!

    1. Hi Draco,

      If I've got your task right, here's a formula that works for me in the Output column:
      =IF(MAXIFS($C$2:$C$8,$B$2:$B$8,"="&$B2)>C2,5%,"")

      1. Hi Natalia, thank you for the reply! The formula works to some extent. But I realised there is a condition I forgot to include in >.< I'm so sorry.

        Condition:
        'x' account with same email
        - all are same rates, give the discount to x-1 of the accounts.

        eg. 2 accounts with same email
        - if both rates are the same, give 5% disc to only 1 of them (maybe to the first account)

        eg. 3 account with same email
        - if all 3 rates are the same, give 5% disc to only 2 of them. (maybe to the first 2 accounts.)

        Will it be easier if I share the link of the Google sheet to you? =)

        Thanks!

        1. 2nd condition
          eg. 3 account with same email
          - if 2 accounts are the same and they are higher than the 3rd account, give 5% to the lowest and to one of the highest.
          ($60, $65, $65 => $60 account and one of the $65 account will have the discount.)

          Thanks again!

          1. Thank you for the clarifications, Draco,

            yes, if it's possible, please do share an editable copy of your file with us (support@apps4gs.com). Please also include the tab with the expected result there.

            Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.

            1. Hi again. Shared the sheet with editor access for the email provided. =)
              Tab: Test sheet for Natalia

              Thanks!

              1. Hi Natalia, saw the new columns with the formulas. Will look at them and test some scenarios. Thank you so much for the assist!

              2. Hi Draco,

                Please look into the shared file, the solution we've found is already there. It requires 2 extra helper columns (D and E, colored in gray) to avoid a huge formula in one cell. The results are in column F.

              3. Hi Draco,

                Thank you, we've got the file. I've asked our tech specialist to look into it. As soon as there's a reply, I'll let you know right away.

  33. Hi Natalia! Very nice of you helping people in need with your great knowledge, good work!
    I have troubles comparing multiple columns, which should be arranged by their content (witch is composed of numbers, letters and mixed), in the way that columns with similar content should be grouped together. Thanks in advance!

    1. Hi Divna,

      Thank you for your lovely feedback! :)

      For me to be able to help you with your task, please consider sharing an editable copy of a sample spreadsheet with us (support@apps4gs.com). Please make sure it contains 2 sheets: a sample of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. Thank you.

      1. Hi again Natalia, thank you for replying and the effort. I sent my data to that email. Thanks again!

        1. Hi Divna,

          Thank you, I've got the file. I can see several columns as your original data, but still, I don't really understand what the task is. Please include the second sheet illustrating how the result of processing all these columns should look like.

  34. You are awesome. I love this one: "Compare two columns in Google Sheets and highlight differences (unique)". But, is there a way to do the same when the values are no unique ?

    What can I do if Column A has a value twice while Column B just once?

    Thanks!!

    1. Hello Jorge,

      Please see 'Example 2. Find and highlight duplicates in two columns in Google Sheets'. It goes right after the one you mentioned.

  35. Scenario 1 - Output sheet contains new records based on Employee ID (Which are uncommon in WHMOutputStatus & Component 1&2)

    Scenario 2 - Output sheet contains records (compare records in Component 1&2 and check whether record having “In Progress” Status. If records contains “In Progress” then dont pick that record from WHMOutputStatus. If record having Actioned status and not having In Progress - pick that record.

    for e.g WHMOutputStatus
    Emp ID Name Status
    1 ABC Actioned
    2 CDE Actioned
    3 EFG Actioned
    4 HIJ Actioned
    5 JKL In Progress
    6 LMN In Progress
    1 ABC Actioned
    2 CDE Actioned
    3 EFG In Progress
    4 HIJ In Progress

    Component 1&2
    Emp ID Name
    1 ABC
    2 CDE
    3 EFG
    4 HIJ
    5 JKL
    10 OOO
    8 YYY
    7 XXX
    Output Sheet must contains below records
    Emp ID Name Status
    1 ABC
    2 CDE
    10 OOO
    8 YYY
    7 XXX

    it would be difficult to manage both scenarios in one sheet. So i break it down into two sheet. I've achieved Scenario 1 using formula - FILTER('Component 1&2'!A:I,IF('Component 1&2'!A:A="",FALSE,(ISNUMBER(MATCH('Component 1&2'!A:A,WHMOutputStatus!A:A,0)))))

    I am looking for scenario 2 solution

    1. hope this summary helps!!

      DT1 (Master DB) DT2 (RAW Data from System) Output Sheet
      Actioned - Not In progress record record available (employee ID exists) include this record from dt2
      In progress record available (employee ID exists) dont include from dt2
      Actioned - Not In progress record No record exists for same employee don’t include (not exists in DT2)
      In progress No record exists for same employee don’t include (not exists in DT2)

      1. Hi @Natalia Sharashova (Ablebits.com Team),

        Any inputs from your end?

        Thanks,
        Sonal

        1. Hi Sonal,

          Our tech specialist took a look at your task. Here's a formula he came up with to compare your two tables and pull those records from 'Component 1&2' that appear in 'WHMOutputStatus' with the status 'Actioned':
          =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Component 1&2'!$B$2:$B$9,SMALL(IF(COUNTIFS(WHMOutputStatus!$B$2:$B$11,'Component 1&2'!$B$2:$B$9,WHMOutputStatus!$C$2:$C$11,"Actioned",WHMOutputStatus!$A$2:$A$11,'Component 1&2'!$A$2:$A$9),ROW('Component 1&2'!$B$2:$B$9)),ROW(1:1))-1),"")), 1, 1)

          Hope this helps.

          1. Thanks Natalia!!

  36. Hi Natalia,
    I have a list of people (first and last names) in one Google Sheet, and a different list in another Google Sheet. Some people will appear in both sheets, but in a different order. How can I check for people with the same first and last names who appear in both Google Sheets and highlight them in both Sheets?

  37. I'm working on some tracking of lists of quotes from famous people. I've already used the countifs and detected the duplicates BUT the issue is that I encountered two quotes that are the same but different usage of punctuations (comma and semicolon) and of course, it will not be detected as "duplicate" because of 1 character. How can I filter this kind of scenario and mark them as duplicate? Thank you in advance!

    1. Hello Rey,

      Since cells should be complete matches to be treated as duplicates, you will need to remove these characters and remaining extra spaces:

  38. I am trying to get a range on Sheet 1 to format based on a range on Sheet 2. Both ranges are full of checkboxes (so not empty). The data validation is identical. I know that I need to use INDIRECT to direct it towards Sheet 2, every time I get a formula that should work, it doesn't. I've gone through the entire checklist of troubleshooting and still cannot figure out what I am doing wrong. I've tried multiple comparative formulae that I've found through searching for help with this. If you can give me the correct formula to conditionally format based on another sheet, I would greatly appreciate it. Thank you in advance.

    1. Hello Gwen,

      I'm sorry it's hard to suggest anything without seeing even the smallest example of your data. Please consider sharing an editable copy of a small sample spreadsheet with us (support@apps4gs.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. We'll look into and try to come up with a solution.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  39. Hello,
    I am comparing 2 large lists in separate sheets, one a master copy of all names and the other a list of names I've phoned. I'm trying to highlight the ones that are missing from list 2.
    The issue I have is that column A is last name and column B is first name on each sheet, so I'm needing to compare 2 columns to 2 columns to find discrepancies.
    How do I go about comparing, highlighting and finding who is missing from list 2?
    Thanks a million!

    1. Hello Cyndi,

      One of these points from the article above will help you compare columns:
      Find missing data
      Compare two Google columns or sheets for differences
      Quick way to match columns and highlight records - Compare sheets add-on

      Or feel free to share an editable copy of your example data with us (support@apps4gs.com). Please include the result you expect to get as well and shorten the tables to 10-20 rows.
      Note. That account is for file-sharing only, please do not email there. Once you share the file, just confirm by replying here.

      I'll look into your task and do my best to help.

      1. Fantastic. That advice worked well.
        Is there a way to sort the now "highlighted" rows so that I can create a list showing just the highlighted items?
        Thanks again.

        1. Hello Cyndi,

          I'm happy to let you know that we have implemented our own Sort by Colors to Power Tools. It works in a different from the standard Google Sheets feature way. Please visit this help page for the details.

        2. Cyndi,

          Glad I could help!
          Google Sheets has just recently introduced the feature to filter by color. Just apply the filter to a column of interest and pick Filter by color > Fill Color.

  40. Hi! Thank you for this tutorial! It was very helpful.

    I was wondering if it was possible to write a formula that would compare two cells for matching words regardless of order. For example, trying to compare names in two separate cells, B2 contains the text "Text Name" and cell C2 contains the text "Name Text". I would like to consider this a match and have cell D2 reflect True.

    Thank you in advance for any and all help!

    1. Hi Tommy,

      Thank you for your lovely feedback! :)

      As for your task, please try this formula:
      =JOIN(" ",TRANSPOSE(SORT(TRANSPOSE(SPLIT($B2," ")),1,TRUE,2,TRUE)))=JOIN(" ",TRANSPOSE(SORT(TRANSPOSE(SPLIT($C2," ")),1,TRUE,2,TRUE)))

  41. Hi! Appreciate your help in advance! I need to compare names in Column B in Tab 2, to Column D in Tab 1 and if the name matches, pull cell data (a date) from Column E in Tab 1. Thank you!!

  42. =A2=INDIRECT("Sheet2!C2:C")

    uneven match issue

    1. Hello Akan,

      I've just double-checked and it works on my end. Please make sure you set the exact range to apply the rule to.

  43. Hi
    I have a google sheet of students reading levels that are letters. I would like to find the difference between for example e3-d3 to calculate growth. How do I do this? Thanks!

    1. Hi Andrea,

      For me to be able to suggest you anything, please share a small sample spreadsheet with us (support@4-bits.com) with 2 sheets: your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that email for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying here.

  44. Hi,
    I have a team of 14 people. I have to manually enter their name and email addresses in col3 and col4. Is there a way I can enter their name and their email address comes up automatically in the next col?
    Eg: Name Email address
    ABC Abc@gmail.com
    DEF Def@gmail.com
    ABC Abc@gmail.com

    1. Hi Midnite,

      Suppose you enter names to column A on Sheet1, and you need to have emails in column B, same Sheet1.

      To do this, first, list each and every name and its corresponding email on a separate sheet, for example, Sheet2, in columns A and B respectively.
      Then, build a VLOOKUP formula like this in column B, Sheet1:
      =VLOOKUP($A2,Sheet2!$A$2:$B$15,2)

      Once you enter the name from the list to A2, B2 will be automatically filled with the needed email.

  45. Hello
    I need to identify the different attendees year on year. Column A is all the attendees in 2017, column B is all the attendees in 2018. I want a list of persons who came in 2017 and not in 2018 and also who came in 2018 but not in 2017.
    Doing this manually is taking a great deal of time and is not reliably accurate.
    Thanks for your help
    Tony

  46. Hi, is it possible to compare 2 pivot table and calculate the sum of both pivot table?
    Pivot table 1 is tab IN, pivot table 2 is tab OUT. I need to know the sum of person A IN minus the sum of person A in tab OUT. So that I can know the actual sum of person A in certain date.

  47. Hey, thanks for posting. Rly need help here.
    I need a function for:

    If the content of Column B in sheet 1 matches the content of Column B in sheet 2, then answer YES in column E in the same row that column b sheet 1 is in.

    Help!!!

  48. Hi i wanted to know which formula shall I use when the case is: is to find if the Email id in Column B in sheet A is Equal to Email id In Column D Sheet B then Copy the Data of Column C i.e Mobile Number in Sheet A to Column C in Sheet B.

  49. Hi, our company needs to update the prices of its stock monthly as we get price updates from them every month. Is there a way to compare an old list of prices with the updated list and then highlight which ones have changed? At the moment we are having to do it by eye. Thank you.

  50. Hello,

    I have a Google Form people filled out, in total I need about 220 responses.
    People have filled out their e-mail adresses and I want to check if everyone I sent it too actually filled it in.

    If not, I can remind them to fill it out.

    Is there a way I can crossreference the filled out email adresses with my imported list of e-mail adresses I sent the form too?
    For example, I sent the email too hello1@gmail.com and hello2@gmail.com
    I want to filter out which one hasnt filled out the form yet, so I can remind them.

    Not sure if you will read it, but I'll give it a shot.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)