Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
As part of our data checks, I have a table like below:
Material Bought | Material Used |
---|---|
Brick; Stone; Mortar | Brick |
Stone; Rock | Rock; Wall Paper |
Brick, Pebbles | Stone |
I need to ensure that what is in column B is also in column A.
In the above example, row 1 is fine, but rows 2 and 3 are an issue. How can I compare these two fields for "likeness" without writing a bunch of if statements?
Thanks in advance!
Hi,
But in second you have also Rock string do you find the first or second position of the string or it just presence of the strings in the field.
Regards
Anand
Do you want a "one-to-one" comparison?
Could you describe better your requirement, since "Brick" appears on first column, but what if have more values?
I am looking for just the presence of the string. For row 2, column B should only include either Stone and/or Rock. Wall Paper would not be allowed.
I would essentially like to write something like below:
IF([Material Used] like [Material Bought],'Okay','Not Okay')
and this would evaluate the two to see if the string from Material Used is contained within Material Bought. I want to avoid having to write if statements containing all the possible values.
Hi Drew,
if I get you right, you have defined items, separated by a semicolon?
or is it undefined strings you want to compare like
Material Bought | Material Used |
---|---|
BrickStone; Mortar | Bricks |
Stone | Cobblestone |
That would make a different story, guess you would have to run loops on that. Ghastly.
As long as you can split up the items using a defined separator (";") as given in your sample, you may want to extract data (using the subfield command) into two tables, one for column 1 and one for the other, each with an id for the line and a field for the text.
The data would look like this:
Original table:
Bought | Used |
---|---|
Brick; Mortar | Brick |
Stone | Sand; Mortar |
Table_Bought:
Line_ID | item |
---|---|
1 | Brick |
1 | Mortar |
2 | Stone |
Table_used:
Line_ID | item |
---|---|
1 | Brick |
2 | Sand |
2 | Mortar |
Then, grouped per line_id, you count matches of both fields,
here resulting in a sum of 1 for line_id 1 and 0 for line_id 2.
Any line with >0 matches is what you are looking for.
Presto.
Would that help?