Thank you SO MUCH!! I've looked through so many tutorials on RUclips trying to figure this out and no one could explain it as simply as you did. Thank you!!
I've watched multiple videos and read articles on this problem and yours is the easiest to follow! Thank you so much!! Wondering how to organize all the highlighted cells in one column... Again thank you!
Bro i appreciate it!!! For people who dont know if you have values in your sheet you have to first format it into numbers. Thats why i kinda lost to much time on my work LOL
If you want to use "match" for conditional formatting based on a column in another Google sheet - this video's approach will not work (why Google Sheets, why have you not fixed this!) I have found this workaround works really well as the conditional formatting formula: =match(K:K,indirect("AnotherSheet!A:A"),0) Hope the "indirect" function (within "match") helps you despite this Google Sheets limitation.
I think I found a typo in your description formula. In the video you write "=MATCH(D2,B$2:B$8,0)" but in the description, it's listed as "=MATCH(D2,B$2:A$8,0)"
Hi! Thank you. I have another question. I am trying to find how to select the hilighted information and create a list based off of that. Do you have a video that explains this?
I know this is not the same, but do you know if there is a way to compare 2 lists that have equal values in them and return numbers that indicate how much they changed? Example: there's a track list of x songs. Column A has the 'old order', column B has the 'new order' after a new round of voting. The columns are sorted with the highest voted song at the top and lowest at the bottom. How can I make Column C tell me which tracks remained in the same place (value 0), which ones rose 1, 2, y places (+1, +2, +y) or fell 1, 2, z places (-1, -2, -z). I can't really figure it out.
Hi I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car Topic: I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance) And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance) I cannot obtain an account statement because we are considered a branch of the main account so I used the tables My question: I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table Until I know who the rest is of value and who has not paid Thank you
Hi. My goal is to find a way to copy a cell and text color to copy the origination of a cell's and text's colors. For example, cell a2 has the word "completed" and the text is bold red with the cell's color purple. When I type "completed" in cell c35, it automatically makes that cell's color purple, and the word becomes bold and red. How can I do that without a script?
Try adding the worksheet name to the cell reference. See here for more info on how to construct cell references to other worksheets ruclips.net/video/vBEZiSA49MM/видео.html
Facing issue with this ISNA function, even though value is same while comparing two columns but its highlighting it as unique. Can you clarify. I can email you screenshot
I'm trying to create a sheet to dump in words for online Boggle so that the words in each column would highlight if they match with words in any other column. It sounds like this is what I'm looking for but instead of two columns I'm comparing 5 columns all against each other. I can't figure out how to make that happen. Any suggestions?
Can you also add a feature where you hover over a cell and see its specific value in another cell. Instead of doing a "control F" you simply hover over the cell and see where it's highlighted in the other column?
I want to do almost the same, but my list of words is in just 1 column. So in the case you are working on, how do you find the duplicates in the D column (and get the spreadsheet to highlight the duplicate words)?? Thanks,
Thank you for your video! Is there any way to compare more than 2 columns? I have 4 columns in which numbers should match. If all four numbers are equal, it's true otherwise (if at least 1 out of 4 do not match) is false. Ideally to make it with conditional formatting to color in green if true and in red if false.
Hey, is there a possibility to to compare list A to list B and mark in list A the once which arent in list B and to get a new list with the once highlighted only?
Is this formula I would use to identify and remove duplicate records and emails? I have a very very large list with company name, first, last name, address phone email etc.. and some I'm sure are even there up to 10 to 15 times, but they are scattered AND not all all of those 10 to 15 records have EXACT data, meaning...one may not have the zip code, another may have the zip, but not the last name. They all DO have the emails though. Once identified, how can I do mass remova/delete? Any kind of guidance is very much appreciated. Thank you sir.
I'm trying to reference another sheet called Sheet2 - I'm trying to input this =MATCH(B2,Sheet2!C$2:C$393,0) but I'm getting an error, any idea why this wouldn't work?
Hi. I have enjoyed and learn a lot from your videos. But I have searched through but couldn't find a video matching what I am looking for and currently am struggling to find the correct query for what I need. I understand how the MATCH, FILTER is. However, if I have 2 lists, Column A with Monday, Tuesday, Wesdnesday, Column B with Tuesday, Wednesday. My results should be of those not in Column B, which is Monday.
doesnt work anymore, or at least not for me (copied the code form your desc.) :( sad! Id like to compare 2 cells f the number is equal, greater or smaller than..
Hi. we play with the workmates who tip the most rows on the Europatipset (Sweden; series of 13 matches). And now I have created a spreadsheet about who has how many right. Everything works until we have some with the same number right. I have a formula that finds the most number right and writes in the cell next to the winner's name. But if they are two or more? how to write all the names that are right?
Just feeling a little crazy here...just output a list of the winners with the SORTN function with the option to show ties. BAM, done! sheetshelp.com/filter/sortn
thanks it's very useful, but if like me it still doesn't work in your spreadsheet, JUST REPLACE THE COMMAS BY SEMI COLONS ! so in the sample =match(D2;B$2:B$8;0) it also work if you replace =match by =equiv(...
This formula is good for accurate matches, but what if i need to find cells having just part of word. From your example, i will typin only 'yell' or 'ang' in B column, and it have to light up 'yellow' and 'orange' in D column. Is there the way to do this and how?
No, but I am guessing that would get complicated pretty quickly. How much of a match? Would typing "y" match to yellow? You may be able to string together a formula with different versions of part of the word, but I don't know a good way to get this done.
Same here... Thought I was crazy. EDIT: Just discovered the solution to my issue. I was trying to use conditional formatting to highlight cells in one column, based on matching in multiple columns. I instead created a conditional formatting rule for EACH column I was testing a match for. In other word, each column to be tested got their own rule. This worked. If your issue is the same as mine, follow the exact steps in the video. Just repeat those steps and for any additional column you're testing.
![Diggy Graves - Red Vineyard [Official Lyric Video]](http://i.ytimg.com/vi/8Ro085T0mnQ/mqdefault.jpg)
Thank you SO MUCH!! I've looked through so many tutorials on RUclips trying to figure this out and no one could explain it as simply as you did. Thank you!!
I glad it helped out.
This just saved me like 4 hours of comparing thank you so much!
Awesome, glad it helped.
Thank you for such a quick, clear instruction. The time invested in your video and building the formula has saved me hours already.
Looks like every 2 months or so i have to go back to this video, its so good. Save me hours of work in less than 3 minutes
Well, we're always happy to have you back.
LIFESAVER! I could almost cry. Every other solution was so complicated compared to yours. Thank you!
You're welcome!
I've watched multiple videos and read articles on this problem and yours is the easiest to follow! Thank you so much!! Wondering how to organize all the highlighted cells in one column... Again thank you!
Thank you for the short and sweet tutorial. Exactly the function I was looking for.
Great to hear!
This was so amazing - trying to do attendance for an entire school for our new remote learning due to school closures. What a lifesaver - stay well.
Cool, glad it helped! Us accountants get to stay inside so all is good here.
That was THE MOST HELPFUL video I have seen on using custom formula to identify duplicates! Thanks!
Thanks, glad you liked it!
Bro i appreciate it!!! For people who dont know if you have values in your sheet you have to first format it into numbers. Thats why i kinda lost to much time on my work LOL
Amazing tutorial. I could listen to your voice all day, your mic + voice + your explaining style is one of the best tutorial voices I've heard. :)
Glad you liked it!
I watched about 347 tutorials trying to find how to do this. Brilliant thanks
You're welcome. Glad it wasn't 348. That would have been _really_ bad.
Thank you so much!
You're welcome!
Thanks for this- clear, concise, and it works! Much appreciated.
Glad it helped!
If you want to use "match" for conditional formatting based on a column in another Google sheet - this video's approach will not work (why Google Sheets, why have you not fixed this!)
I have found this workaround works really well as the conditional formatting formula:
=match(K:K,indirect("AnotherSheet!A:A"),0)
Hope the "indirect" function (within "match") helps you despite this Google Sheets limitation.
Thank you SO much! This was EXACTLY what I was looking for! Seriously appreciate it!
I’m so glad it helped!
Just saved hours of manual work. Thank you very much!!!!
Great to hear!
This is amazing!! Thanks so much, saved my life!
Yep, exactly what I was looking for and thanks for using simple data for the demostration
Glad it was helpful!
Thank you ! Exactly what i was searching for :)
THANK YOUUU, can finally save time when checking the student's attendanceee
Glad it's helpful to you!
How could you select the matching values and add just those values to a new column?
This was so helpful! Thank you for the tutorial.
I think I found a typo in your description formula. In the video you write "=MATCH(D2,B$2:B$8,0)" but in the description, it's listed as "=MATCH(D2,B$2:A$8,0)"
Wow! 63,000 views and you are the first to point that out. I think you're right so I updated it. Thanks again.
You rock! I didn't think this would be possible. Thanks so much for the help!
Happy spreadsheeting to you Maria;)
Awesome, clear, visual. Thank you very much Professor Oak!
You're quite welcome my pupil.
This is super helpful!! THANK YOU SO MUCH!
You're so welcome!
This is very helpful dude, thanks a ton
Thanks, i try find vídeos in spanish but thé only vidéo that know do this action its yours,, you are great
You are welcome!
Just what I was looking for, thanks !
this was so cool. thank you!
Glad you liked it!
works! you're a genius thank you so much
Glad it helped
Very useful! thank you for sharing!
That's EXACTLY what I needed! Thanks a lot!
This was super helpful and concise. Thank you!
You're welcome!
This was very very helpful. Best explanation I came across!
That's a great compliment! Thank you for taking the time to let me know.
Hi! Thank you. I have another question. I am trying to find how to select the hilighted information and create a list based off of that. Do you have a video that explains this?
Thanks so much, finally found a match to my requirements. Tried earlier with IFS, OR but MATCH functions seems so much better. Thank you!
Great to hear!
a thousand times thank you!!!!
Thousand times welcome
Thanks for information and working method
Welcome
So helpful thank you!
You're so welcome!
GREAT tutorial and ALMOST exactly what I needed. Can I do this same thing on two different pages in the same sheet?
Yes you can, but to do conditional formatting between sheets, you have to use INDIRECT there is a tutorial on how to handle that on YT.
Super helpful. Thank you!
Hi Thanks SO MUCH ! Just one question : How can I copy only the email in color ?
The color doesn't exist in the cell so it can't be copied.
Thanks for such informative video
Thank you, that helped me a lot.
SAVED MY LIFE
Great explanation, thank you
Thanks! No more manual comparing.
legend! works perfectly
=MATCH(A2,B$2:B$258,0)
I know this is not the same, but do you know if there is a way to compare 2 lists that have equal values in them and return numbers that indicate how much they changed?
Example: there's a track list of x songs. Column A has the 'old order', column B has the 'new order' after a new round of voting. The columns are sorted with the highest voted song at the top and lowest at the bottom. How can I make Column C tell me which tracks remained in the same place (value 0), which ones rose 1, 2, y places (+1, +2, +y) or fell 1, 2, z places (-1, -2, -z).
I can't really figure it out.
Hey! Please tell us how to import those matching keywords into new excel
Could you show an example of doing this exact same thing but the data sets are in different tabs within the same google sheet?
Take a look here for that type of cell reference: sheetshelp.com/cell-references/#References_To_Another_Sheet
i love you
Right back at ya
Hi
I work in a small company, we bid cars to customers after paying a guarantee from an auction site in America and after winning the car the customer pays the required value and then we ship the car
Topic:
I have a table with customer payments and data (date / name / car / car code / receipt / exchange / balance)
And the second table in the capital movement of the car that was paid to the site and data (date / name / car / car code / receipt / exchange / balance)
I cannot obtain an account statement because we are considered a branch of the main account so I used the tables
My question:
I want a function or method to read a column in which (the car code) is in the capital movement of the car that was purchased with the column in which (the car code) is in the customer payments column and output the difference between the first and the second table
Until I know who the rest is of value and who has not paid
Thank you
Hi, how to modify formula to match 2 arrays which are on different tabs? When I write =match(G2,Relevant!G$2:G,0) - error appears. is it possible?
tnx it helped.
You rock! worked great!
Excellent! Party on.
I want to highlighted two columns then what will be the formula
Hi. My goal is to find a way to copy a cell and text color to copy the origination of a cell's and text's colors. For example, cell a2 has the word "completed" and the text is bold red with the cell's color purple. When I type "completed" in cell c35, it automatically makes that cell's color purple, and the word becomes bold and red.
How can I do that without a script?
It would be helpful if you wrote the formulas in the video description.
Good idea. I just added them. Thanks.
Hey! Is there a way to extract those matching words to another column or sheet? If there were thousands of words to match
I'm putting this question in the que to answer tomorrow during the live stream at 11 am. Hope to see you there. Look at my channel page for the link.
@@ProlificOaktree hey! Have i missed the live stream? Could you paste here the link?
@@romaknafel4116 ruclips.net/video/A2HoEYK6rw4/видео.html
strange. I have an error. I even tried to do the same sheet, as you do on the video and copypaste the formula.
Can I use the MATCH function across sheets? So check a range in sheet 1 against a range in sheet 2?
Yeah, see here for how to reference across sheets. ruclips.net/video/vBEZiSA49MM/видео.html
Very helpful video. Is there any chance you can highlight a duplicate cell between multiple sheets? Thanks
Try adding the worksheet name to the cell reference. See here for more info on how to construct cell references to other worksheets ruclips.net/video/vBEZiSA49MM/видео.html
Thanks for your video, but formula is not visible clearly, please highlight full formula in subtitle
Please use the link in the description of the video.
When I transfer data from one file in Google Sheet to another file with (= QUERY (IMPORTRANGE), the row links do not move to the new rows?
Thanks, EXCELLENT VIDEO!
Thanks, I'm glad you liked it!
I have used this formula =COUNTIF(B:B,B2)>1
But same 2value not showing duplicate??
Please suggest here
I love you more than I can ever, ever, ever say.
Love you too.
Who I add conditional formatting that will change cell colors if there are not empty based on a certain cell?
Thanks!
Oh yeah, this makes my day! Thank you.
No one understands what goes into these videos except fellow creators. Happy to support great videos and creators behind them.
Facing issue with this ISNA function, even though value is same while comparing two columns but its highlighting it as unique. Can you clarify. I can email you screenshot
Thanks boi
I'm trying to create a sheet to dump in words for online Boggle so that the words in each column would highlight if they match with words in any other column. It sounds like this is what I'm looking for but instead of two columns I'm comparing 5 columns all against each other. I can't figure out how to make that happen. Any suggestions?
Just try this with the range being all of the columns ruclips.net/video/CvySUgYSrbA/видео.html
Can you also add a feature where you hover over a cell and see its specific value in another cell. Instead of doing a "control F" you simply hover over the cell and see where it's highlighted in the other column?
I want to do almost the same, but my list of words is in just 1 column. So in the case you are working on, how do you find the duplicates in the D column (and get the spreadsheet to highlight the duplicate words)??
Thanks,
I have a video on doing this in one column right here: ruclips.net/video/CrwFQqxzQn8/видео.html
Thank you for your video! Is there any way to compare more than 2 columns? I have 4 columns in which numbers should match. If all four numbers are equal, it's true otherwise (if at least 1 out of 4 do not match) is false. Ideally to make it with conditional formatting to color in green if true and in red if false.
Hey, is there a possibility to to compare list A to list B and mark in list A the once which arent in list B and to get a new list with the once highlighted only?
That sounds like a custom action that you'd have to write in Apps script.
I want to highlight the matches in both columns how would I go about that
Thank you! It works with semicolons for me. Russia
Is this formula I would use to identify and remove duplicate records and emails? I have a very very large list with company name, first, last name, address phone email etc.. and some I'm sure are even there up to 10 to 15 times, but they are scattered AND not all all of those 10 to 15 records have EXACT data, meaning...one may not have the zip code, another may have the zip, but not the last name. They all DO have the emails though. Once identified, how can I do mass remova/delete? Any kind of guidance is very much appreciated. Thank you sir.
Maybe use the UNIQUE function no the emails and then pull the other columns back in with VLOOKUP.
I was too looking for the answer to this question. Here I found the answer: productforums.google.com/forum/#!msg/docs/HCOCOS7A-w8/Jb41fsjVBgAJ
I'm trying to reference another sheet called Sheet2 - I'm trying to input this =MATCH(B2,Sheet2!C$2:C$393,0) but I'm getting an error, any idea why this wouldn't work?
No. My best advice is to try the formula in a regular cell outside of conditional formatting, get it to work there, and then paste it back in.
Hi, thanks a lot for the video. Is there a chance, when you can higlight not only one cell, but full row?
Yes, take a look at this video ruclips.net/video/W7wjXGzB4hs/видео.html
Hi. I have enjoyed and learn a lot from your videos. But I have searched through but couldn't find a video matching what I am looking for and currently am struggling to find the correct query for what I need. I understand how the MATCH, FILTER is. However, if I have 2 lists, Column A with Monday, Tuesday, Wesdnesday, Column B with Tuesday, Wednesday. My results should be of those not in Column B, which is Monday.
You could use VLOOKUP and whenever it doesn't get a match, you've got what you want.
I did the same thing and it tells me the formula is invalid ....
Hi Thanks! Can it compare between two different sheets?
Yeah change the reference as shown here: ruclips.net/video/vBEZiSA49MM/видео.html
Thanks for this
You're welcome
doesnt work anymore, or at least not for me (copied the code form your desc.) :( sad!
Id like to compare 2 cells f the number is equal, greater or smaller than..
I like it, just trying to match it to a second sheet... Using =match(a2,2ndsheet!g$2:2ndshhet!g$30,0) is rejected ... why? and thx
How do I in Google Spreadsheets - which maintain hyperlink formatting on import importRange + QUERY?
if i want to compare a list of 100 addresses to a master list of 1000, can I highlight the columns to do this?
Yes!
Hi. we play with the workmates who tip the most rows on the Europatipset (Sweden; series of 13 matches).
And now I have created a spreadsheet about who has how many right. Everything works until we have some with the same number right.
I have a formula that finds the most number right and writes in the cell next to the winner's name. But if they are two or more? how to write all the names that are right?
Just feeling a little crazy here...just output a list of the winners with the SORTN function with the option to show ties. BAM, done! sheetshelp.com/filter/sortn
thanks it's very useful, but if like me it still doesn't work in your spreadsheet, JUST REPLACE THE COMMAS BY SEMI COLONS ! so in the sample =match(D2;B$2:B$8;0) it also work if you replace =match by =equiv(...
Thanks for the input. This is the case for anyone that lives in a country where the comma is a decimal point.
no matter what i do, i always get a red line around the formula box and sheets is telling me that it is invalid :(
Start by typing your formula into a cell and see if it works there. Then copy and paste it.
Thanks
Welcome
How about highlighting all duplicates in both columns?
Sorry, I can’t think of anything showing that.
This formula is good for accurate matches, but what if i need to find cells having just part of word. From your example, i will typin only 'yell' or 'ang' in B column, and it have to light up 'yellow' and 'orange' in D column. Is there the way to do this and how?
No, but I am guessing that would get complicated pretty quickly. How much of a match? Would typing "y" match to yellow? You may be able to string together a formula with different versions of part of the word, but I don't know a good way to get this done.
So this worked for me a few weeks ago...but it doesn't work now. Did Google Sheets make some updates?
No, they haven't made any update that would impact these formulas.
me too
Same here... Thought I was crazy.
EDIT: Just discovered the solution to my issue. I was trying to use conditional formatting to highlight cells in one column, based on matching in multiple columns. I instead created a conditional formatting rule for EACH column I was testing a match for. In other word, each column to be tested got their own rule. This worked. If your issue is the same as mine, follow the exact steps in the video. Just repeat those steps and for any additional column you're testing.
@@chrisr9441 I am doing the same thing but I am having an issue where it's not comparing straight across and instead comparing to the whole column.
THANKS FOR VIDEO
SIR HOW TO HIGHLIGHT MATCHING WORDS (30% OR 50% )JUST IN ONE COLUMN LIKE ( GARDEN,GARDON,NEWYORK,NEOYORK ,AMAZON,AMAJONE)