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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
