I have 2 tables with the same IDs but different values for some cells. I want to find the different values on the new tab in the following format: Id, sheet1_field, sheet2_field, sheet1_data, sheet2_data.
What is the best way to do this?
Please find the example and the expected result in the following spreadsheet - https://docs.google.com/spreadsheets/d/1W9aLJZoypoFT7xyKdpk1rXhTL1BVruZpbpoPhzKswmU/edit?gid=1041107819#gid=1041107819
You may try:
=let(Λ,tocol(,1), reduce(Λ,Sheet1!B2:index(Sheet1!E:E,match(,0/(Sheet1!A:A<>""))),lambda(a,x,vstack(if(iserr(+a),Λ,a),let(r,row(x),c,column(x),z,index(Sheet1!A:A,r),
Σ,vlookup(z,choosecols(Sheet2!A:E,1,c),2,),if(x=Σ,Λ,hstack(z,index(Sheet1!1:1,c),index(Sheet2!1:1,c),x,Σ)))))))