Microsoft Office Forums Finding Duplicates & Unique Values in More Than One Sheet
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-05-2020, 08:28 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69
Finding Duplicates & Unique Values in More Than One Sheet

Using Excel 365

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

Karen
Attached Files
 Excel Sample.xlsx (10.4 KB, 4 views)
#2
03-05-2020, 09:50 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 274

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
03-06-2020, 12:13 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 564

Quote:
 Originally Posted by Marcia =IF(COUNTIF(Sheet1!A\$2:\$A\$14,A2)>0,"TRUE","FALSE")
IF() is not needed.
Code:
`=COUNTIF(Sheet1!A\$2:\$A\$14,\$A2)>0`
will do fine.
#4
03-06-2020, 12:22 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 274

Right Arvi. Thank you.
#5
03-06-2020, 09:53 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69

Thank you both for your help!

Karen
#6
03-06-2020, 10:28 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69

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.

Karen
#7
03-06-2020, 11:10 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 274

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
03-07-2020, 10:57 AM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69

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
03-07-2020, 11:13 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 274

Click conditional formatting>manage rules>in the apply to, change F2 to F15. Or, select A2:F15, then repeat the conditional formatting process.
#10
03-07-2020, 05:47 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69

Thank you so much Marcia!
#11
03-08-2020, 08:15 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 69

How exactly does this formula work?
=COUNTIF(Sheet1!A\$2:\$A\$14,\$A2)>0
#12
03-08-2020, 09:57 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 274

Quote:
 Originally Posted by Karen615 How exactly does this formula work? =COUNTIF(Sheet1!A\$2:\$A\$14,\$A2)>0
Karen, google COUNTIF and you'll find excellent websites providing detailed explanations. I go direct to Exceljet.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post trevorc Excel Programming 4 10-11-2018 03:49 PM allex011 Excel 4 12-27-2017 12:31 PM balajigade Excel 2 12-06-2015 10:16 PM abhilashv Mail Merge 1 01-22-2014 04:02 AM alliage Excel 1 09-01-2011 05:23 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:52 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top