Conditional column based on values in multiple rows

[tgjones43]

Hi all

I have 2 columns, the first a survey number, the second an observation value. There can be one or many observation values for each survey. Each survey was either collected using an old method, where the values are essentially categories given the number 3, 33, 333, 3333 or 33333, or a new method, where the values are real numbers. I need a way of adding a column that can report Old for the old method, New for the new method. So in the example below, surveys 1 and 3 used the old method because all values are only 3, 33, etc.

Is this possible?

Survey	Value	Required
1	3	Old
1	33	Old
1	3	Old
1	333	Old
1	33333	Old
1	33	Old
1	3	Old
1	33	Old
1	3	Old
1	333	Old
1	3	Old
1	3	Old
2	4	New
2	7	New
2	17	New
2	1	New
2	3	New
2	45	New
2	32	New
3	3	Old
4	3	New
4	7	New

[Anonymous · Accepted solution]

HI @tgjones43 ,

You can use following calculate column formula to achieve your requirement:

Tag Column =
IF (
    [Survey] IN { 1, 3 }
        && [Value] IN { 3, 33, 333, 3333, 33333 },
    "Old",
    "New"
)

Regards,

Xiaoxin Sheng