excel - matching elements in multi columns from two files, then update or union them -

excel - matching elements in multi columns from two files, then update or union them -

file1:

77, 4, -3, a0080 235, 5, -1, k0511

file2:

a0132, 77, -1, -2, 19.776 00000, 77, 4, -3, 18.608, a0794, 235, -2, -2, 22.81 a0796, 235, -2, -5, 12.27 00000, 235, 5, -1, 18.992

desired output:

a0132, 77, -1, -2, 19.776 a0080, 77, 4, -3, 18.608, a0794, 235, -2, -2, 22.81 a0796, 235, -2, -5, 12.27 k0511, 235, 5, -1, 18.992

basically match column1, column2, column3 of file1 column2, column3, column4 of file2, if match replace column1 of file2 value of column4 of file1.

i used:

awk 'fnr==nr {a[$1,$2,$3]++;next} a[$2,$3,$4] {print $0}' file1 file2

to output

00000, 77, 4, -3, 18.608, 00000, 235, 5, -1, 18.992

then stuck. please help. btw, 2 files, how's more 2 files in general.

apparently there problem trailing spaces. complicates things little bit, because need trick $field+=0 overcome (it removes trailing spaces).

you can seek this:

awk -f"," -v ofs="," 'fnr==nr {$1+=0; $2+=0; $3+=0; a[$1,$2,$3]=$4;next} {$2+=0; $3+=0; $4+=0 if (($2,$3,$4) in a) {$1=a[$2,$3,$4]} print }' f1 f2

basically, stores value in 4th column index (1st, 2nd, 3rd) columns. then, when reading sec file, checks if given index matches 2nd, 3rd , 4th columns there; if so, replaces 1st field.

for given input, returns:

$ awk -f"," -v ofs="," 'fnr==nr {$1+=0; $2+=0; $3+=0; a[$1,$2,$3]=$4;next} {$2+=0; $3+=0; $4+=0; if (($2,$3,$4) in a) {$1=a[$2,$3,$4]} print}' f1 f2 a0132,77,-1,-2, 19.776 a0080,77,4,-3, 18.608, a0794,235,-2,-2, 22.81 a0796,235,-2,-5, 12.27 k0511,235,5,-1, 18.992

excel awk sed