Compare columns from multiple sources and replacing null fields

[jeet_shah94]

Hello Team,

My aim is to compare two sources and given priority to source1. Wherever the data in source 1 is null then we need to bring data from source2. Here the catch is we have duplicate IDs in same source which reduces the count as it groups the data when doing cross tab while comparing the 2 sources. I have attached the excel which highlights the 2 sources and the expected output. 

[Raj]

PFA

[jeet_shah94]

Thanks Raj for your solution. But there is a slight change in the request, we don't have the data sorted or aligned as per the ID column. Thus the logic of creating a RecordID and creating a transpose on it won't work. Could you please suggest any other way? Thanks in advance.

[apathetichell]

is your nth entry in id - always supposed to cross match to the nth entry in id? If not - how do you know which 123 to join against cross table?

[jeet_shah94]

No the nth entry don't match between sources and if we join on ID column it creates duplicate rows as it is one to many or many to many rows joined. Thus need a solution to tackle this.

[apathetichell]

this is what I'd try.