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!

Find more posts tagged with

Formulas

Accepted answers

Jonathan Gann

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.

Paul Newcome

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")

All comments

Ipshita

Hi @Dakota Haeffner - can you please elaborate on your question? However, here is a small tip to help you start comparing sheets -

Cheers!

Jonathan Gann

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")

Paul Newcome

=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")

Dakota Haeffner

@Paul Newcome @Jonathan Gann

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!

Paul Newcome

@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")

Jaime Holder

https://community.smartsheet.com/discussion/comment/362068#Comment_362068

@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

Jaime Holder

https://community.smartsheet.com/discussion/comment/406398#Comment_406398

Now I've been asked if I can validate the DEPT column at the same time, any help is appreciated.

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.