Comparing Data in two Sheets
Hello,
I have a large list of 926 individuals who are identified by IDs in one sheet. some of these individuals are no longer active. I am trying to find a simple solution to compare the individuals in one sheet to the 3+ separate Rosters that hold the active individuals in other sheets.
Basically, if they are in a the large list but not in the individual rosters they are inactive and I can remove them from the larger list.
I have no problem setting up additional helper sheets/columns for formulas. I just need to figure out who is inactive on this large list.
Thank you for any ideas!
Best Answers
-
It may be a complicated formula to write, but you could conceivably run a COUNT(COLLECT( or INDEX(MATCH( on each sheet's references to determine if they exist in the other lists. For instance:
Column 1 is the employee IDs
Reference 1,2,3,etc is the employee IDs column in roster doc 1,2,3,etc
=IF( ( IFERROR( INDEX( {Reference 1} , MATCH( [Column 1]@row , {Reference 1} , 0 ) ), "" ) +(repeat previous section for Reference 2,3,etc) )="","Inactive","Active")
Each section (in italics) will return a blank through IFERROR if there's no results found, and if all of the nested INDEX/MATCH functions return zero results, it should all equal a null value "" and let you label them as inactive via the bolded wrap. Otherwise, it returns the Active label.
-
You would need to write out 3 separate COUNTIFS (one for each of the three active rosters). Adding them together and then saying if the total is equal to zero (meaning the id was not found in any of the three sheets), flag as "Inactive".
=IF(COUNTIFS({Sheet 1 ID Column}, @cell = [ID Column]@row) + COUNTIFS({Sheet 2 ID Column}, @cell = [ID Column]@row) + COUNTIFS({Sheet 3 ID Column}, @cell = [ID Column]@row) = 0, "Inactive")
Answers
-
Hi @Dakota Haeffner - can you please elaborate on your question? However, here is a small tip to help you start comparing sheets -
Cheers!
Ipshita Mukherjee
-
It may be a complicated formula to write, but you could conceivably run a COUNT(COLLECT( or INDEX(MATCH( on each sheet's references to determine if they exist in the other lists. For instance:
Column 1 is the employee IDs
Reference 1,2,3,etc is the employee IDs column in roster doc 1,2,3,etc
=IF( ( IFERROR( INDEX( {Reference 1} , MATCH( [Column 1]@row , {Reference 1} , 0 ) ), "" ) +(repeat previous section for Reference 2,3,etc) )="","Inactive","Active")
Each section (in italics) will return a blank through IFERROR if there's no results found, and if all of the nested INDEX/MATCH functions return zero results, it should all equal a null value "" and let you label them as inactive via the bolded wrap. Otherwise, it returns the Active label.
-
You would need to write out 3 separate COUNTIFS (one for each of the three active rosters). Adding them together and then saying if the total is equal to zero (meaning the id was not found in any of the three sheets), flag as "Inactive".
=IF(COUNTIFS({Sheet 1 ID Column}, @cell = [ID Column]@row) + COUNTIFS({Sheet 2 ID Column}, @cell = [ID Column]@row) + COUNTIFS({Sheet 3 ID Column}, @cell = [ID Column]@row) = 0, "Inactive")
-
Hey thank you both for the detailed responses.
Each of these seem like viable options I'm running with Pauls becomes the count is easier to sort with.
Thank you!
-
@Jonathan Gann Really you would only need to use the MATCH function nested inside of a series of IFERRORs.
=IF(IFERROR(IFERROR(IFERROR(MATCH([ID Column]@row, {Sheet 1 ID Column}, 0), MATCH([ID Column]@row, {Sheet 2 ID Column}, 0)), MATCH([ID Column]@row, {Sheet 1 ID Column}, 0)), 0) <> 0, "Active", "Inactive")
-
@Paul Newcome thanks for this, it almost works for my application - any chance there's a way to exclude zero/null values?
My application is 2 columns of numbers on a "master" sheet to compare against a "new" sheet that has 6 columns, need to verify/identify where matches occur across the 6 lanes, but over the years a zero or empty cell has gathered on the master, and i get a true result where there is no numbers in the "new" sheet.
screenshot below, formula so far is thus:
=IF(COUNTIFS({FY24_SO#}, @cell = [ORDER2]@row) + COUNTIFS({FY24_WO#}, @cell = [ORDER2]@row) = 0, "none", "DEFECT!")
Your help is always appreciated, TIA
-
Now I've been asked if I can validate the DEPT column at the same time, any help is appreciated.
-
I'm not sure I follow that first bit about the zero and blank. In your screenshots, which one is the master sheet?
To include a Dept validation, you would include another range criteria set inside each of the COUNTIFS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!