4.18 Merge by row and column simultaneously - keep data that firstly appear

 

Before merging:

CustomerFruits1.xlsx
imagepng

CustomerMeats1.xlsx
imagepng

According to the order of CustomerFruits1.xlsx first and CustomerMeats1.xlsx later, the duplicate records that appear in CustomerFruits1.xlsx first are kept. The result after merging is:
imagepng

Script:

A
1 =file(“CustomerFruits1.xlsx”).xlsimport@t()
2 =file(“CustomerMeats1.xlsx”).xlsimport@t()
3 =A1.pivot@r(Name;col,val)
4 =A2.pivot@r(Name;col,val)
5 =(A3|A4).group@1(Name,col)
6 =A5.pivot(Name;col,val)
7 =file(“CustomerFoods1.xlsx”).xlsexport@t(A6)

A3: Transpose the data of original cross layout to a list
A5: Select the record that appears firstly after grouping
A6: Transpose the data back to cross layout