mjhjmtu

Here is the text files we have:

file1.txt

A B C D
4 9
5 8

file2.txt:

H I J K L M N O P Q R
1 2 3 5 8 9 5 3 3 2 4
4 7 8 9 5 6 8 9 0 8 7
7 8 0 7 4 7 7 9 0 6 7
2 7 9 5 7 9 0 7 6 5 4

I want extract the two columns (H and N) from file2.txt when A column in file1.txt matches with the L column in file2.txt, and the output text file to be shown should be like this:

output.txt

A B C D
4 9 7 7
5 8 4 8

How can I do that?

Not possible using grep, but doable using awk:

$ awk 'NR==1 print $0;next # Print header
 NR==FNR a[$1]=$2;next # collect data from file1.txt
 if($5 in a) # If column L match
 print($5,a[$5],$1,$7) # print columns (H and N)
 
 ' file1.txt file2.txt

As a one-liner:

$ awk 'NR==1print $0;next NR==FNRa[$1]=$2;next if($5 in a)print($5,a[$5],$1,$7)' file1.txt file2.txt

A B C D
5 8 4 8
4 9 7 7

The order is as the fields appear in the file2.txt.

If you need that sorted, then add a sort step.

I would go with a step by step approach, to explain

1. 
   

   Sort the file 1

   
   
   

   sort file1.txt > file1.sorted.txt

   
   


2. 
   

   Take the interesting portion out from file2 (and sort it too)

   
   
   

   awk 'print $5,$1,$7' file2.txt | sort > file2_of_interest.txt

   
   


3. 
   

   Join the two outputs based on the key, which is column 1 in both the files

   
   
   

   join file1.sorted.txt file2_of_interest.txt -1 1 -2 1

   
   

You can combine the three steps using indirection - but in the end you would need to complete parse at least one file. There would also be concerns on cases where column L in file2.txt is not unique and/or column 1 in file1.txt is not unique - you would need to resolve those by putting in your business logic to make the key unique.