How do I compare data on 4 sheets?
Hello,
I have a [PCN Checklist] that holds active and non-active PCN#'s. I need to check each PCN # on the [PCN Checklist] smartsheet to see if it's in any of the 3 other smartsheets:
[PCN Processing Log]
[PCN Archive Log 1]
[PCN Archive Log 2]
If it's not in any of these 3 smartsheets, it did not go through the PCN Process.
The columns in all 4 smartsheets is a text column with the title {PCN #}. However, 2 of the above smartsheets, the PCN # has a hyperlink attached to it. I've tried different formula's and also created a "Clean PCN #" column without the hyperlink and can't get it to work.
=IF(OR(ISNUMBER(MATCH([PCN #]@row, {PCN Processing Log Range}, 0)), ISNUMBER(MATCH([PCN #]@row, {Archived 1 Range}, 0)), ISNUMBER(MATCH([PCN #]@row, {Archived 2 Range}, 0))), "Match", "No Matches")
This returns: #No Match
It would be ideal if it could return the following if Matched or Not Matched per sheet:
Processing Log:
If matched return, "Matched Processing Log"
If not matched, return "Check Archive Log 1"
Archive Log 1:
If matched return, "Matched Archive Log 1"
If not matched return, "Check Archive Log 2"
Archive Log 2:
If matched return, ""Matched Archive Log 2"
If not matched return, ""Not Processed"
This might be a long shot.
Any help would be greatly appreciated!
Thanks in advance for your help!
Answers
-
Jamie, I would recommend creating helper columns for each archive sheet to simplify the complexity of the overall formula. I tested an example and have included a video for your reference. I was able to get a functioning PCN with a hyperlink using a helper column that used a VALUE function to convert it to a number, but would only work if the PCN is only a number.
Formulas Used:
For each of the helper columns that checks for the PCN number in each of the archive sheets. These are split into separate columns, but could potentially be merged into one if preferred. There is just a higher likelihood of the formula "breaking" the more complex it gets.
=IF(COUNTIF({PCN Processing Log - PCN Number}, [PCN Number]@row) > 0, 1, 0) =IF(COUNTIF({PCN Archive Log 1 - PCN Number}, [PCN Number]@row) > 0, 1, 0) =IF(COUNTIF({PCN Archive Log 2 - PCN Number}, [PCN Number]@row) > 0, 1, 0)
For the formula that checks if the PCN has gone through the PCN Process, it just checks all the helper columns and writes yes or no based on the results.
=IF(COUNTIF([PCN Processing Log Match]@row:[PCN Archive Log 2 Match]@row, 1) > 0, "Yes", "No")
To convert a hyperlinked PCN into an actual number (this only works with numbers, no text)
=VALUE([PCN Number]@row)
Let me know if this answers your questions!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!