Help with merge two file based on similar column content

Input file 1:

A1BG    
A1BG    
A1BG    
A1CF    
A1CF    
BCAS
BCAS
A2LD1   
A2M     
A2M     
HAT
.
.

Input file 2:

A1BG    All
A1CF    TEMP
A2LD1   STOP
A2M     KEEP

Desired output:

A1BG    All
A1BG    All
A1BG    All
A1CF    TEMP
A1CF    TEMP
BCAS    Undefined
BCAS    Undefined  
A2LD1   STOP
A2M      KEEP
A2M      KEEP
HAT      Undefined
.
.

The desired output file is based on the comparison between the column 1 in both input file, Input file 1 and Input file 2. If exactly the same content of column 1 in both file, print out the content 2's info but based on the location and number of appearance in Input file 1.
For those different of column 1's content in both file, print out the "Undefined" after the column 1's text in Input file 1.

Thanks.

I changed the order of those two files ie. file 1 is f2 and file 2 is f1 in your case:

awk 'NR==FNR{_[$1]=$2; next} _[$1] {print $1,_[$1]; next} {print $1,"Undefined"}' f1 f2
A1BG All
A1BG All
A1BG All
A1CF TEMP
A1CF TEMP
BCAS Undefined
BCAS Undefined
A2LD1 STOP
A2M KEEP
A2M KEEP
HAT Undefined
1 Like
awk 'NR==FNR{_[$1]=$NF;next}{print $0,(_[$1])?_[$1]:"Undefined"}'  file2 file1
1 Like

danmero's answer is a tad more intelligent than mine but what did not work with mine? I am curious to know, just in terms of interesst and politeness, thanks :slight_smile:

1 Like

Hi zaxxon,

your awk command work nice as well :slight_smile:
Really thanks a lot ^^
Sorry that I forget to confirm with you.

Oki, thanks^^