Let me go through each category in the original sample input.

Category 1 (cat1 in column 2)

First set of duplicates; 1 and 2

Code:

 
 
1;cat1;val1
2;cat1;val2

Duplicate ids 1 and 2 have different values val1 and val2 in cat1. So we output only the first (1) and call the value ambiguous.

Code:

1;cat1;ambiguous

Moving on to the second set of duplicates,; 3,4 and 5

Code:

 
 
3;cat1;val3
4;cat1;val3
5;cat1;val3

For duplicates 3,4 and 5 in category 1 , they have the same value val3. So we just output the first.

Code:

 
3;cat1;val3

Category 2 (cat2 in column 2)
First set of duplicates; 1 and 2

Code:

 
2;cat2;val2

Only 2 is present, no 1.So we output as it is.

Code:

 
2;cat2;val2

Moving on to second set of duplicates; 3, 4 and 5

Code:

3;cat2;val3
5;cat2;val3

Only 3 and 5 are present, no 4 and they have the same value val3.

So we output the first (3) with value val3.

Code:

 
3;cat2;val3

Moving on to third set of replicates; 6 and 7

Code:

6;cat2;val3

Code:

7;cat2;val4

Both 6 and 7 are present with different values , so output the first (6) and the report the value ambiguous.

Code:

 
6;cat2;ambiguous

Lastly the id 8 is not duplicated in the lookup file.

Code:

 
8;cat2;val4

So report as it is

Code:

 
8;cat2;val4

So the sample input file (combined from the above steps)

Code:

 
varid;category;value
1;cat1;val1
2;cat1;val2
3;cat1;val3
4;cat1;val3
5;cat1;val3
2;cat2;val2
3;cat2;val3
5;cat2;val3
6;cat2;val3
7;cat2;val4
8;cat2;val4

Desired output (combined the red rows from each step)

Code:

 
1;cat1;ambiguous
3;cat1;val3
2;cat2;val2
3;cat2;val3
6;cat2;ambiguous
8;cat2;val4

---------- Post updated at 10:23 AM ---------- Previous update was at 10:10 AM ----------

Aia, this will not be the case because they are different categories.
The first row belongs to cat1 and the second row belongs to cat2, so they must be treated independently.

Code:

1;cat1;ambiguous
2;cat2;ambiguous

It should be

Code:

1;cat1;ambiguous
2;cat2;val2