by 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.
Compare two columns or sheets
One of the tasks you may have is to scan two columns or sheets for matches or differences and identify them somewhere outside the tables.
Compare two columns in Google Sheets for matches and differences
I'll start by comparing two cells in Google Sheets. This way lets you scan entire columns row by row.
Example 1. Google Sheets – compare two cells
For this first example, you will need a helper column in order to enter the formula into the first row of the data to compare:
=A2=C2
If cells match, you'll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:
Tip. To compare columns from different files, you need to use the IMPORTRANGE function:
=A2=IMPORTRANGE("spreadsheet_url","Sheet1!A2")
Example 2. Google Sheets – compare two lists for matches and differences
- A neater solution would be to use the IF function. You'll be able to set the exact status for identical and different cells:
=IF(A2=C2,"Match","Differ")
Tip. If your data is written in different cases and you'd like to consider such words as different, here's the formula for you:
=IF(EXACT(A2,C2),"Match","Differ")Where EXACT considers the case and looks for the complete identicals.
- To identify only rows with duplicate cells, use this formula:
=IF(A2=C2,"Match","") - To mark only rows with unique records between cells in two columns, take this one:
=IF(A2=C2,"","Differ")
Example 3. Compare two columns in Google Sheets
-
There's a way to avoid copying the formula over each row. You can forge an array IF formula in the first cell of your helper column:
=ArrayFormula(IF(A2:A=C2:C,"","Differ"))This IF pairs each cell of column A with the same row in column C. If records are different, the row will be identified accordingly. What is nice about this array formula is that it automatically marks each and every row at once:
-
In case you'd rather name the rows with identical cells, fill the second argument of the formula instead of the third one:
=ArrayFormula(IF(A2:A=C2:C,"Match",""))
Example 4. Compare two Google Sheets for differences
Oftentimes you need to compare two columns in Google Sheets that belong inside a huge table. Or they can be entirely different sheets like reports, price lists, working shifts per month, etc. Then, I believe, you can't afford to create a helper column or it can be quite difficult to manage.
If this sounds familiar, don't worry, you can still mark the differences on another sheet.
Here are two tables with products and their prices. I want to locate all cells with different contents between these tables:
Start with creating a new sheet and enter the next formula into A1:
=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")
Note. You must copy the formula over the range equal to the size of the biggest table.
As a result, you will see only those cells that differ in contents. The formula will also pull records from both tables and separate them with a character you enter into the formula:
Tip. If the sheets to compare are in different files, again, just incorporate the IMPORTRANGE function:
=IF(Sheet1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),"")
Tools for Google Sheets to compare two columns and sheets
Of course, each of the above examples can be used to compare two columns from one or two tables or even match sheets. However, there are a few tools we created for this task that will benefit you a lot.
Compare sheets add-on
This first one will compare two (& more!) Google sheets and columns for duplicates or uniques in 5 steps. Make it mark the found records with a status column (that can be filtered, by the way) or color, copy or move them to another location, or even clear cells and delete entire rows with dupes whatsoever.
I used the add-on to find the rows from Sheet1 that are absent from Sheet2 (and vice versa) based on Fruit and MSRP columns:
Then I saved my settings into one scenario. Now I can quickly run them without going through all steps again whenever records in my tables change. I just need to start that scenario from the Google Sheets menu:
If you're feeling excited about this tool, go ahead and click that image below to install it from the Google Workspace Marketplace. You'll notice how much time it saves you :)
This help page will gently guide you in case you're stuck on any step.
Compare sheets cell by cell
This on is also part of the Compare Sheets collection. It will compare your Google Sheets for differences. Whether you have two or more tables, it will check them all cell by cell and create one thorough report with differences from all sheets grouped accordingly.
Here's an example of the same two tables. The add-on creates one report with not only different cells (marked with yellow) but also unique rows (marked with red and blue):
Video: How to work with the comparison report
To look at the report and all its parts closely, feel free to read this tutorial or watch this demo video:
Try both add-ons for yourself and notice how much time they save you. :)
Compare data in two Google Sheets and fetch missing records
Comparing two Google Sheets for differences and repeats is half the work, but what about missing data? There are special functions for this as well, for example, VLOOKUP. Let's see what you can do.
Find missing data
Example 1
Imagine you have two lists of products (columns A and C in my case, but they can simply be on different sheets). You need to find those presented in the first list but not in the second one. This formula will do the trick:
=ISERROR(VLOOKUP(A2,$C:$C,1,0))
How does the formula work:
- VLOOKUP searches for the product from A2 in the second list. If it's there, the function returns the product name. Or else you will get an #N/A error meaning the value wasn't found in column C.
- ISERROR checks what VLOOKUP returns and shows you TRUE if it's the value and FALSE if it's the error.
Thus, cells with FALSE are what you're looking for. Copy the formula to other cells to check each product from the first list:
Note. If your columns are in different sheets, your formula will reference one of them:
=ISERROR(VLOOKUP(A2,Sheet2!$C:$C,1,0))
Tip. To get by with a one-cell formula, it should be an array one. Such formula will automatically fill all cells with results:
=ArrayFormula(ISERROR(VLOOKUP(A2:A10,$C:$C,1,0)))
Example 2
Another smart way would be to count all appearances of the product from A2 in column C:
=IF(COUNTIF($C:$C, $A2)=0, "Not found", "")
If there's absolutely nothing to count, the IF function will mark cells with Not found. Other cells will remain empty:
Example 3
Where there's VLOOKUP, there's MATCH. You know that, right? ;) Here's the formula to match products rather than count:
=IF(ISERROR(MATCH($A2,$C:$C,0)),"Not found","")
Tip. Feel free to specify the exact range of the second column if it remains the same:
=IF(ISERROR(MATCH($A2,$C2:$C28,0)),"Not found","")
Pull matching data
Example 1
Your task may be a bit fancier: you may need to pull all missing information for the records common for both tables, for example, update prices. If so, you'll need to wrap MATCH in INDEX:
=INDEX($E:$E,MATCH($A2,$D:$D,0))
The formula compares fruits in column A with fruits in column D. For everything found, it pulls the prices from column E to column B.
Example 2
As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.
Yet, there are a few more instruments for the job. We described them all in our blog as well:
Merge sheets using the add-on
If you're tired of formulas, you can use our Merge Sheets add-on to quickly match and merge two Google sheets.
Alongside its basic purpose to pull the missing data, it can also update existing values and even add non-matching rows. You can see all changes in colour or in a status column that can be filtered.
The 2.0 version of Merge Sheets will merge not just 2 tables (one main with one lookup) but multiple sheets in a row (one main with several lookups). The data from the lookup sheets will be added to your main one by one: as you added them in the add-on. Lots of additional options will make your merge as comprehensive as you need.
Video: How to use Merge Sheets add-on for Google Sheets
Check out this video about the Merge Sheets add-on. Though it features just 2 sheets, it paints a clear picture of the add-on possibilities:
Conditional formatting to compare data in two Google Sheets
There's one more standard way Google offers to compare your data – by colouring matches and/or differences via conditional formatting. This method makes all records you're looking for stand out instantly. Your job here is to create a rule with a formula and apply it to the correct data range.
Highlight duplicates in two sheets or columns
Let's compare two columns in Google Sheets for matches and colour only those cells in column A that tally with cells in the same row in column C:
- Select the range with records to color (A2:A10 for me).
- Go to Format > Conditional formatting in the spreadsheet menu.
- Enter a simple formula to the rule:
=A2=C2 - Pick the color to highlight cells.
Tip. If your columns change in size constantly and you want the rule to consider all new entries, apply it to the entire column (A2:A, assuming the data to compare starts from A2) and modify the formula like this:
=AND(A2=C2,ISBLANK(A2)=FALSE)
This will process entire columns and ignore empty cells.
Note. To compare data from two different sheets, you'll have to make other adjustments to the formula. You see, conditional formatting in Google Sheets doesn't support cross-sheet references. However, you can access other sheets indirectly:
=A2=INDIRECT("Sheet2!C2:C")
In this case, please specify the range to apply the rule to – A2:A10.
Compare two Google sheets and columns for differences
To highlight records that don't match cells on the same row in another column, the drill is the same as above. You select the range and create a conditional formatting rule. However, the formula here differs:
=A2<>C2
Again, modify the formula to make the rule dynamic (have it consider all newly added values in these columns):
=AND(A2=C2,ISBLANK(A2)=FALSE)
And use the indirect reference to another sheet if the column to compare with is there:
=A2<>INDIRECT("Sheet1!C2:C")
Note. Don't forget to specify the range to apply the rule to – A2:A10.
Compare two lists and highlight records in both of them
Of course, it's more likely the same records in your columns will be scattered. The value in A2 in one column will not necessarily be on the second row of another column. In fact, it may appear much later. Clearly, this requires another method of searching for the items.
Example 1. Compare two columns in Google Sheets and highlight differences (uniques)
To highlight unique values in each list, you must create two conditional formatting rules for each column.
Color column A: =COUNTIF($C$2:$C$9,$A2)=0
Color column C: =COUNTIF($A$2:$A$10,$C2)=0
Here are the uniques I've got:
Example 2. Find and highlight duplicates in two columns in Google Sheets
You can colour common values after slight modifications in both formulas from the previous example. Just make the formula count everything greater than zero.
Color dupes between columns in A only: =COUNTIF($C$2:$C$9,$A2)>0
Color dupes between columns in C only: =COUNTIF($A$2:$A$10,$C2)>0
Tip. Find many more formula examples to highlight duplicates in Google Sheets in this tutorial.
3 quickest ways to match columns and highlight records
Conditional formatting can be tricky sometimes: you may accidentally create a few rules over the same range or apply colors manually over cells with rules. Also, you have to keep an eye on all ranges: the ones you highlight via rules and those you use in the rules themselves. All of these may confuse you a lot if you're not prepared and not sure where to look for the problem.
Luckily, our Compare Sheets collection for Google Sheets has 3 user-friendly solutions for you.
Video: What is Compare Sheets collection
Add-on to compare & highlight duplicates or uniques
Compare sheets for duplicates is intuitive enough to help you match different tables within one file or two separate files, and highlight those uniques or dupes that may sneak into your data.
Here's how I highlighted duplicates between just two tables based on Fruit and MSRP columns using the tool:
I can also save these settings into a reusable scenario. If the records update, I will call for this scenario in just a click and the add-on will immediately start processing all the data. Thus, I avoid tweaking all those settings over the add-on steps repeatedly. You will see how scenarios work in the example above and in this tutorial.
Add-on to compare Google sheets and highlight differences
Compare sheets cell by cell doesn't fall behind. It sees all differences between two columns or sheets. In fact, it compares as many sheets as you need, even from different files. Usually, one of these tables acts as your main one, and you compare it with others. The add-on highlights differences on those other sheets so you could spot them instantly:
Video: How to use Compare Sheets Cell by Cell add-on
This help page and the demo video below will give you a better idea of how it compares multiple Google sheets for differences:
Compare two columns and color dupes/uniques
This last tool comes in especially helpful for a simpler task: comparing just two columns within one Google tab. Why especially helpful? Because since both columns are on one sheet, going over 5 steps is too much. Hence, there's only one step with all the necessary settings:
This tutorial discusses every option in detail if you'd like to take a look.
And guess what? This tool is also part of the Compare Sheets collection from the Google Workspace Marketplace.
That's right: you get all 3 tools with just one add-on. Give it a go and you won't regret it! (And if you do, let me know why in the comments section!)
Anyways, all these methods are now at your disposal – experiment with them, modify and apply them to your data. If none of the suggestions help your particular task, feel free to discuss your case in the comments down below.
215 comments
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"
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.
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!!
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.
Hi. I want to compare columns and find duplicate values between two sheets, can you please help me with it?
Hi Anusha,
I described ways you can use at this part of the blog post:
Compare two columns or sheets
If you want to mark duplicates with a color, look for a solution here:
Highlight duplicates in two sheets or columns
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).
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.
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'
Hello Eric,
It looks like you need to use the IF function for your task. We described the function and its usage in this article, please take a look.
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 :-)
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.
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?
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"),))
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
Hello Santo,
Here's what you need to know:
So here's the formula I would try:
=ISNA(MATCH(H4,INDIRECT("'Jangan Di Sentuh'!N2:N31"),0))=TRUE
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
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
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.
oh... but many thanks for your help.... you help me a lot. wish you have a great day
You're most welcome, Susanto :) Have a great day, too!
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.
Thank you for your feedback.
Please check out this blog post: Conditional formatting based on another cell. You will see custom formulas used in cases like yours there: where numbers from different columns are compared and colored based on the result.
-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!
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%,"")
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!
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!
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.
Hi again. Shared the sheet with editor access for the email provided. =)
Tab: Test sheet for Natalia
Thanks!
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.
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.
Hi Natalia, saw the new columns with the formulas. Will look at them and test some scenarios. Thank you so much for the assist!
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!
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.
Hi again Natalia, thank you for replying and the effort. I sent my data to that email. Thanks again!
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.
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!!
Hello Jorge,
Please see 'Example 2. Find and highlight duplicates in two columns in Google Sheets'. It goes right after the one you mentioned.
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
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)
Hi @Natalia Sharashova (Ablebits.com Team),
Any inputs from your end?
Thanks,
Sonal
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.
Thanks Natalia!!
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?
Hi Kim,
I believe the way with conditional formatting described in this part of the article will help you.
If you don't want to do that manually, feel free to try our add-on shown at the end of the article.
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!
Hello Rey,
Since cells should be complete matches to be treated as duplicates, you will need to remove these characters and remaining extra spaces:
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.
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.
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!
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.
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.
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.
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.
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!
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)))
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!!
Hi Nini,
You need to use the VLOOKUP function for this task. Feel free to look for the instructions and examples of the formula here: VLOOKUP in Google Sheets.
=A2=INDIRECT("Sheet2!C2:C")
uneven match issue
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.