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 4. Total comments: 217

  1. 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!!

  2. =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.

  3. 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.

  4. 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.

  5. 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

  6. 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.

  7. 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!!!

  8. 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.

  9. 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.

  10. 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.

  11. I am scraping data from the web in two columns and want to find the common values between the two columns (ie duplicates) and put them on the thrid column.
    As I am scraping the data, I do not know how many cells I will have at any given time.
    Any help appreciated!

    1. Hello Nickolas,

      If you want to use formulas, you need to know the exact number of duplicates because formulas won't supply you with additional rows.

      So I'd advise you to try our Compare two sheets or columns tool. It will copy/move duplicate values to any place you select. If the sheet is short on rows, the tool will insert them along with the dupes.

  12. Hey, you seem super knowledgable and this is about as close as I've come to a solution for my problem.

    I'm putting together a data set, which is basically a list of countries in row A, and I want to add data in form of entire columns - typically two columns where one is the list of countries and the other one is the data input. The issue is that some lists don't include all countries, for which I'd like to leave an empty row in that specific column of data.

    Here's how I want it to look:
    https://cdn3.imggmi.com/uploads/2019/10/22/3e9f91f0e882d2296a8f2b3e0cf40c59-full.png

    On the right how it ends up, compared to my full list of countries:
    https://cdn3.imggmi.com/uploads/2019/10/22/37545ca2a98a32471307627200bdeb09-full.png

    I'm very greatful for any help of redirection towards a solution!

    1. Hello Viking,

      If I get it correctly, it looks like our Merge Sheets can help you. It pulls the related data from one table to another and adds missing rows with the rest of the countries.
      In the end, you can simply sort the table by countries A>Z.

      You can test the tool for 30 days for free to see if it suits your needs.

      1. You're absolutely correct, Merge Sheets helped immensely and I appreciate you taking the time to help me out!

  13. Please, i need a formula to remove duplicates from a large data of name and phone number from two different columns. Thanks

  14. Hi! This is very helpful!
    I've used =ISERROR(VLOOKUP(C3,$G:$G,1,0)), and it gives me a FALSE if the C and G columns have the same name, and TRUE if it only exists in C.
    Is there a way to list the True values in a single, tight column, excluding the False?
    My project is a list of clients that need a specific task. If it has been accomplished, then the name is in G and C. G is all clients, C is a subset of those clients with the task completed. So, I'm looking for a list of clients who need the task. Thanks in advance,

    Alex

    1. Hi Alexander,

      I'm sorry but your task is not entirely clear. Please consider sharing a small sample with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Since we keep that email for file-sharing only, once you share the file, please confirm by replying here. Thank you.

      In the meantime, you can check our blog post devoted to Google Sheets VLOOKUP function. Perhaps, it'll help.

  15. I am trying to test for conditions in two different columns and count the number of times the condition is true in both columns.
    For instance if the Value is "Closed" in column F and the Value is "M" in column K then count that row.

  16. Hi Natalia, I am hoping you will help me with a way to identify the common row fields from the two columns. They may not be in the same row and I want to identify the common in both columns appearing in anywhere in the two columns. All the fields in the individual columns are unique. Thanks for the opportunity to ask you this question.

  17. I am trying to compare/list duplicates taken from a calendar.
    Columns A and B are where people list their names and holiday dates. These columns got pretty bug quite quickly. To simplify looking for any available dates that you may want off I have created a drop down in J14 where you can select the month and which pulls the calendar for that month into cells J16:P21.
    I have taken dates from the list in column B and highlight the corresponding dates in the calendar in J16:P21.
    But, I want to list the people and the dates which they have booked off underneath the calendar.

    To recap.... compare dates from column B2:B to the calendar J16:P21 and list duplicates and the corresponding name from column A2:A

    1. Hello David,

      For us to be able to help you better, please share a small sample spreadsheet with us (support@4-bits.com) with your source data and the result you expect to get. I kindly ask you to shorten the table to 10-20 rows.
      Since we keep that email for file-sharing only, once you share the file, please confirm by replying here. Thank you.

  18. Hi,

    I have two separate Google Sheets, I need to take information from one sheet to the other. Using INDEX, MATCH how would I pull information over from one sheet to the other? MATCH("X",Sheet #2 URL, 0) doesn't seem to work

    Thanks

  19. Hey Guys!
    I'm having trouble doing this for two lists of names from a party. I have tried so many equations but still having trouble.
    For example, in one column I have the full list of invited guests and then in column B I have all the people who have paid. I am trying to find who hasn't paid for their ticket to the party. Can someone help me out?
    Cheers!

    1. I have two sheets of color names. in sheet one I have all the colors organized under their primary colors. I want to look for colors in column A of Sheet2 inside the table in Sheet1 and return in column B of Sheet2 the title of the column it was found in Sheet1. if there are multiple found then all in one cell separated with comma and no space. thanks

      1. Hello Maae,

        For us to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 3 sheets: where 1st and 2nd are your sheets with colors and 3d is the result you need 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.

        We'll look into your task and see if our software can help.

    2. Hey Xavier,

      It looks like you need to find those names in column A that are not in column B. This point of the article offers a short formula to solve this:
      https://www.ablebits.com/office-addins-blog/google-sheets-compare-two-sheets-columns/#find-missing-data

      If you'd rather use colours, please read through this paragraph:
      https://www.ablebits.com/office-addins-blog/google-sheets-compare-two-sheets-columns/#highlight-both-lists

      Make sure your names are written identically and there are no extra spaces or other chars in them.

  20. Hi
    I you have a user database with all names in the SHEET1 A:A but in SHEET2!A:A you only have some of the names, and same with SHEET3A:A and SHEET4!A:A

    What if In SHEET1 you want the B:B collum to refference to what other sheet has the same name.

    So if the First name in The Sheet 1 list is «John B» And «John B» is also the 13th name in the SHEET3. How do I get SHEET!B1 to say SHEET3 when SHEET!A1 says «John B» ?

    If that makes any sence? What would the formula be in B1 (and dragged down for the rest of the sheet)

    1. Hi Christian,

      If I get your task correctly, this formula will help:
      =IF(COUNTIF(Sheet3!$A:$A,A1)>0,"Sheet3","")

      If you'd like to check in all 3 sheets at the same time and return the corresponding sheet name, try this one instead:
      =IF(COUNTIF(Sheet2!$A:$A,A1)>0,"Sheet2",IF(COUNTIF(Sheet3!$A:$A,A1)>0,"Sheet3",IF(COUNTIF(Sheet4!$A:$A,A1)>0,"Sheet4","")))

      Keep in mind that once the formula finds the value in Sheet2, it won't look in other sheets, it will return "Sheet2" right away. If the searched entry is absent from all three sheets, cells with formula will remain blank.

  21. Hi everyone,
    Can somone please help me with which formula to use:
    If Value in Column B is Column B minus Column A equal or greater than 2, then highlight Column B. So for example:

    Column A Column B
    100 103 = 103 is highlighted
    100 101 = 101 is not highlighted

    Thanks to whoever can answer this.

  22. Hi,
    I have to separate Google Spreadsheets. I need to make sure that they are identical. How can I compare the data between two separate spreadsheets?

    Thank you,
    Violet

    1. Hi Violet,

      To reference another spreadsheet, you need to use the IMPORTRANGE function.

      Thus, your formula to compare two Google Sheets for differences may look like this:
      =IF(Sheet1!A1<>IMPORTRANGE("link_to_your_2nd_spreadsheet","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("link_to_your_2nd_spreadsheet","Sheet1!A1"),"")

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 :)