CONTAINS with Trigger Search
Hi all,
Thank you in advance for the help. Below is what I am trying to achieve.
The columns are as follows:
Address and Device Type
Explanation:
When Device Type contains the word "Coin". This will use the address of the device type that contains "Coin". This address will then be checked against the address column until it finds a Device Type that is A or B and reports back which one it found first A or B.
My thinking is as follows, but I don't know the link I a missing:
=IF(CONTAINS("COIN", [Device Type]@row), 1, 0) <-- Identifies a TRUE or FALSE to initiate the search
Assume True (1)
=IF(AND(MATCH([Address]@row, [Address]:[Address]), [Device Type]@row = "A"), "A", "B")
Ideally this would then tell me Yes, I found the address that matches this and the device type matches the criteria here is the result A or B.
Appreciate the support.
-Charles
Best Answer
-
Hey Paul,
Apologies for the delay on this. What I ended up doing was adjusting our ERP report export to include a unique identifier for me to flag a keyword in the description of the unit. To answer your question and close this discussion out.
If a unit was flagged as a COIN. We went back and concat on description and product field we had in our system this gave me enough information to know what Product the coin unit was supposed to paired with. I then just created a report that grouped by PO and the filter worked perfectly to easily identify service, pairing, and location.
Thanks for the support while I navigated the rabbit hole!
-Charles
Answers
-
Are you able to provide some screenshots for reference?
-
Nice! THE PAUL! I have read so many of your support posts and used loads of your formulas!!!
I created a sample sheet. I am not sure if I can post a published sheet for learning purposes, but here is the screenshot.
What I am trying to do is group the COIN unit with either Device A or Device B using the address as binding factor.
FYI I added my thinking of steps of how this could work, but I don't know the Smartsheet puzzles pieces needed.
Formulas are as follows:
Address is pulled in via Data Shuttle
Dupe is =IF(COUNTIF(Dupe:Dupe, Dupe@row) > 1, 1)
Device is pulled in via Data Shuttle
service provider =IF(Device@row = "A", "Texas", "Florida")
Conditional formatting applied is used so that if a service provider is Texas then the row is lit up Green.
My thinking of steps:
- Index the addresses and find the duplicates and Machine type A
- If it is a Machine type A I need to hold this address in memory
- If any address is found with the Type A then it gets an indicator saying Service Provider Texas
- I can then create a conditional formatting for this flag
Thank you in advance for your help!
-Charles
-
So basically you want to say that anything with Device "A" goes to Texas, and anything with Device "B" goes to Florida? I am still kind of lost what you are trying to do with "COIN".
-
So when we ship out a device it can be from either Device Type A or B. Anything flagged with COIN must be driven from either a Device Type A and B device because A and B are the brains/computer of the setup. So what I have to do is find a way to pair up Device Type A with a peripheral unit going to the same address.
Example: Data Shuttle pulls in our shipping log from our ERP and I get a list of products hundreds of lines long with addresses, device types, etc.
Phase 1:
I need a way to pair up Device type A with all of the peripherals associated with an address that is going to the same address. I could just as easily have a series of other peripherals other than a COIN system going out and would need to be able to scale the solution
Phase 2:
Once I get all of Device type A peripherals grouped up by address then I apply a new column formula that tells me which state the service provider is depending on the device type and i need to be able to apply this formula so that it captures Device Type and all of the peripherals associated with it.
I hope this helped
-
I think I am overcomplicating this greatly.
What I am after at the end of the day is if a Unit type is A I want to use the address of unit type A and go find all of the other units associated with that unit type and assign the service provider with a state.
-
But how are you supposed to know which device the COIN goes to?
-
Hey Paul,
Apologies for the delay on this. What I ended up doing was adjusting our ERP report export to include a unique identifier for me to flag a keyword in the description of the unit. To answer your question and close this discussion out.
If a unit was flagged as a COIN. We went back and concat on description and product field we had in our system this gave me enough information to know what Product the coin unit was supposed to paired with. I then just created a report that grouped by PO and the filter worked perfectly to easily identify service, pairing, and location.
Thanks for the support while I navigated the rabbit hole!
-Charles
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!