I have to look up the value from two columns (I.e. Record ID Type I & II) to get the desired result. I won't be able to do that with the help of Find & Replace tool because it will find the value from one column only.
Input :-
Record ID -Type I | Record ID -Type II | Science | Maths | English |
25 | 500 | 50 | 60 | 35 |
50 | 5000 | 55 | 65 | 45 |
75 | 6000 | 65 | 85 | 65 |
100 | 8000 | 70 | 95 | 75 |
Output :-
Record ID | Science | Maths | English |
25 | |||
50 | |||
5000 | |||
6000 | |||
I have attached my input & output file for your reference. Please help me with relevant solution
It would be helpful if the excel file included the formula(s) you're using. I don't understand what logic you need with the current attachment.
Could you simply aggregate the 2 record ID columns with a formula tool and then use that as your lookup?
Hi @Vicky92 ,
You can use a formula tool to combine the two fields (e.g. [Record ID - Type 1] + [Record ID - Type 2] ) in both data sets and then do the Find/Replace based on that field. Then you can drop your combined field in the final output. Alternatively, you can use a join tool and add both Record ID - Type 1 and Record ID - Type 2 as unique keys in the JOIN tool.