CONTAINS with Trigger Search

Charles Elam

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

Charles Elam

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

Paul Newcome

Are you able to provide some screenshots for reference?

Charles Elam

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:

1. Index the addresses and find the duplicates and Machine type A
2. If it is a Machine type A I need to hold this address in memory
3. If any address is found with the Type A then it gets an indicator saying Service Provider Texas
4. I can then create a conditional formatting for this flag

Thank you in advance for your help!

-Charles

Paul Newcome

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

Charles Elam

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

Charles Elam

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.

Paul Newcome

But how are you supposed to know which device the COIN goes to?

Charles Elam

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