Match columns several files

Hey fellas!

Here come my problem. I appreciate if you have a look at it.

I have several files with following structure:

file_1:

1 21
4 45

file_2:

2 31
4 153
6 341

and so on...

and I have a 'reference' file look like this:

File_ref:

A 1 
B 2
C 3
D 4 
E 5
F 6

And this is the desired output:

output:

  file_1 file_2
A_1 21  
B_2       31
C_3     
D_4 45    153
E_5     
F_6       341

My files are much more complicated. I am not just sure which commands should I use. it seems so simple but I haven't managed to make it! :expressionless:
Thanks in advance.

$ awk 'FNR==1{s=count?s"\t"FILENAME:"\t";count++}NR==FNR{A[$2]=$1"_"$2;B=$2;next}NR!=FNR{A[$1,count]=$2;next}END{
print s;
for(i=1;i<=B;i++){printf A;
for(j=1;j<=count;j++){printf "\t%s",A[i,j]}printf "\n"}}' file_final file_1 file_2
                file_1  file_2
A_1             21
B_2                     31
C_3
D_4             45      153
E_5
F_6                     341

Thanks Pamu for your time.

here are two problems! :expressionless:

  1. I have several files. instead of writing the names manually can I use something like *.txt ?

  2. I am more dumb than what I thought! :expressionless: :expressionless: actually the columns that I wanna check similarity are $4 from all files and ref file. and in the output should comes the $3 of ref file to $1 of output and $13 of each file as the values in the right positions.

I tried to modify it myself but it goes forever and then crashes! :expressionless:

could you please give me some updates?

Thanks A LOT!

Could you please give me sample input for this....
Not clear from your description.

and for many files you can use sth like this...

awk {} file_ref *.txt

I am sorry for not being clear...

I have attached one of my several files.
And the reference file.
and based on those the desired output File.
It's OK if you don't have time to make it. But if you do I appreciate it.

awk '{if (FILENAME="ref.txt"){if (a[$4]) { print $3,a[$4]}}a[$4]=a[$4]"\t"$13;}' *.txt ref.txt