Comparing Lists

[bowker678]

Hi, I was wondering if anyone could help.

I am trying to compare two lists, these are in text format and delimited with a comma, appsheet only seems to be counting one as a list and the other as a full string.

Simple example below:

Required Training (Column)
Abrasive Wheels, Confined Spaces

Completed Training (Column)
Confined Spaces, Abrasive Wheels, Agricultural Tractor

When the above match there are no issues (even if they are in the wrong order), however as soon as you add another value into the mix (Agricultural Tractor) the comparison returns a False value.

Basically one person can complete more training courses than is mandatory, I want to check the Completed Training Column against the Mandatory Requirements column, if the Completed Training Column contains all the values present in the Mandatory Requirements column then it should return True.

Formula for the check below:

IN(LIST([Mandatory Requirements]), LIST([Completed Training]))

If you need any more info let me know!

Thanks

[Lynn]

Hi @bowker678 Have you got your lists the right way around, have you tried IN(LIST([Completed Training]), LIST([Mandatory Requirements]))

[bowker678]

Yes, i have tried it both ways and doesn’t seem to work, however I have found a work around.

I added a virtual column with the following formula:

[Mandatory Requirements] - [Completed Training]

This leaves me with any data not contained in the [Completed Training] column, that meaning if there were any [Mandatory Requirements] left over that were not covered in [Completed Training] then I could use ISNOTBLANK to return either True of False.

Thanks for the help anyway, seems to have worked but need to do more testing!

[Aleksi]

How about COUNT(SPLIT([Required Training]," , “) - SPLIT([Completed Training],” , "))=0

[bowker678]

Thanks Aleksi, was trying to do something similar but couldn’t work out the formula!

Managed to sort a workaround (I hope), thanks for the help anyway!

[Aleksi]

The syntax should be correct. May I ask what was the reason it didn’t work?

[bowker678]

bowker678:

Managed to sort a workaround (I hope), thanks for the help anyway!

I didn’t try your formula, I was thinking something along the same lines but I couldn’t work out the correct formula by myself.

Thanks though

[Aleksi]

Allright