#1
|
|||
|
|||
Finding Duplicates & Unique Values in More Than One Sheet
Using Excel 365
Please see attached. I would like to know how to set up a formula/function in Sheet2, cell B2 that will search column A in Sheet1. If a company in Sheet1 matches a company in Sheet2, it will return TRUE or FALSE. For Example-Sheet2: Cell B2: FALSE Cell B3: TRUE Cell B4: FALSE Your help is greatly appreciated. Karen |
#2
|
||||
|
||||
I am using my cellphone soI can't work on your attached sample. Try this in B2
=IF(COUNTIF(Sheet1!A$2:$A$14,A2)>0,"TRUE","FALSE") Copy down. |
#3
|
|||
|
|||
IF() is not needed.
Code:
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0 |
#4
|
||||
|
||||
Right Arvi. Thank you.
|
#5
|
|||
|
|||
Thank you both for your help!
Karen |
#6
|
|||
|
|||
I was wondering if this would be possible.
I modified the file and re-uploaded a new file. I added a "Yes / No" column in column F. If "Yes" is typed into cell F2, cell range A2:F2 gets highlighted with (let's say) a yellow fill. I'm very curious to see if this is possible. Your help is greatly appreciated. Karen |
#7
|
||||
|
||||
Yes Karen. A2:F2 could be highlighted with yellow through conditional formatting. Select A2:F2, in the ribbon of the Home tab, open Conditonal Formatting>Formula>type this formula in the box:
=($F2,"Yes") Click Format>Fill>select yellow color>ok Again, this is a cellphone so I am not sure if the formula works okay. Edit: The formula should be: =($F2="Yes") Last edited by Marcia; 03-07-2020 at 07:00 AM. |
#8
|
|||
|
|||
Thank you so much Marcia! This worked great!
So if I want to apply this conditional formatting to each subsequent row in column F if a user types in "Yes" to F2:F15, how would I apply it? Thank you again for your help. Karen |
#9
|
||||
|
||||
Click conditional formatting>manage rules>in the apply to, change F2 to F15. Or, select A2:F15, then repeat the conditional formatting process.
|
#10
|
|||
|
|||
Thank you so much Marcia!
|
#11
|
|||
|
|||
How exactly does this formula work?
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0 |
#12
|
||||
|
||||
Karen, google COUNTIF and you'll find excellent websites providing detailed explanations. I go direct to Exceljet.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Loop through cells and return only unique values | trevorc | Excel Programming | 4 | 10-11-2018 03:49 PM |
Sorting cells with duplicates values | allex011 | Excel | 4 | 12-27-2017 12:31 PM |
Finding Duplicates with Multiple Criteria | balajigade | Excel | 2 | 12-06-2015 10:16 PM |
Combining Records of two sheets with Loop till Unique ID | abhilashv | Mail Merge | 1 | 01-22-2014 04:02 AM |
If two geographical data match in two sheets, copy unique id/code found in one sheet | alliage | Excel | 1 | 09-01-2011 05:23 AM |