Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-05-2020, 08:28 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Excel Sample.xlsx (10.4 KB, 8 views)
Reply With Quote
  #2  
Old 03-05-2020, 09:50 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 03-06-2020, 12:13 AM
ArviLaanemets ArviLaanemets is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 8 Finding Duplicates & Unique Values in More Than One Sheet Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Marcia View Post
=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.
Reply With Quote
  #4  
Old 03-06-2020, 12:22 AM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Right Arvi. Thank you.
Reply With Quote
  #5  
Old 03-06-2020, 09:53 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

Thank you both for your help!


Karen
Reply With Quote
  #6  
Old 03-06-2020, 10:28 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 03-06-2020, 11:10 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #8  
Old 03-07-2020, 10:57 AM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 03-07-2020, 11:13 AM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Click conditional formatting>manage rules>in the apply to, change F2 to F15. Or, select A2:F15, then repeat the conditional formatting process.
Reply With Quote
  #10  
Old 03-07-2020, 05:47 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

Thank you so much Marcia!
Reply With Quote
  #11  
Old 03-08-2020, 08:15 PM
Karen615 Karen615 is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 64bit Finding Duplicates & Unique Values in More Than One Sheet Office 2010 64bit
Competent Performer
Finding Duplicates & Unique Values in More Than One Sheet
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

How exactly does this formula work?
=COUNTIF(Sheet1!A$2:$A$14,$A2)>0
Reply With Quote
  #12  
Old 03-08-2020, 09:57 PM
Marcia's Avatar
Marcia Marcia is offline Finding Duplicates & Unique Values in More Than One Sheet Windows 7 32bit Finding Duplicates & Unique Values in More Than One Sheet Office 2013
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Karen615 View Post
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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Duplicates & Unique Values in More Than One Sheet Loop through cells and return only unique values trevorc Excel Programming 4 10-11-2018 03:49 PM
Finding Duplicates & Unique Values in More Than One Sheet Sorting cells with duplicates values allex011 Excel 4 12-27-2017 12:31 PM
Finding Duplicates & Unique Values in More Than One Sheet Finding Duplicates with Multiple Criteria balajigade Excel 2 12-06-2015 10:16 PM
Finding Duplicates & Unique Values in More Than One Sheet 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:35 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft