Fetch matches from a file based on its corresponding ID list in second file?

Shriram

New Member
Joined
Mar 1, 2018
Messages
2
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">I have a 2 text file. file1 contains IDs:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">0 ABCD
3 ABDF
4 ACGFR
6 FGHTRSS
7 GFHTRSFS</code>And file2:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">ID001 AB ACGFR DF FD GF TYFJ ANH
ID002 DFR AG ABDF HGT MNJ POI YUI
ID003 DGT JHY ABCD YTRE NHYT PPOOI IUYNB
ID004 GFHTRSFS MJU UHY IUJ POL KUH KOOL
</code>If the second column of file 1 matches to any entry in file 2 then the first column of file 2 should be the answer for it.
The output should be like:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">0 ID003
3 ID002
4 ID001
7 ID004</code>(2nd column of file1 (ABCD) found match to 3rd row of file 2 which has ID003. So, ID003 should be the answer to it).
I have tried examples form other posts too, but somehow they are not matching to this.
Any help will be grateful.
Kind Regards
</code></pre>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Shriram

New Member
Joined
Mar 1, 2018
Messages
2
The edits for my post is as below (as i could not edit it i have to reply to it).
I have a 2 text file. file1 contains IDs:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">0 ABCD
3 ABDF
4 ACGFR
6 FGHTRSS
7 GFHTRSFS

</code>And file2:


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">ID001 AB ACGFR DF FD GF TYFJ ANH
ID002 DFR AG ABDF HGT MNJ POI YUI
ID003 DGT JHY ABCD YTRE NHYT PPOOI IUYNB
ID004 GFHTRSFS MJU UHY IUJ POL KUH KOOL
</code>
If the second column of file 1 matches to any entry in file 2 then the first column of file 2 should be the answer for it.

The output should be like:


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">0 ID003
3 ID002
4 ID001
7 ID004</code>

(2nd column of file1 (ABCD) found match to 3rd row of file 2 which has ID003. So, ID003 should be the answer to it).

I have tried examples form other posts too, but somehow they are not matching to this.
Any help will be grateful.
Kind Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,095,392
Messages
5,444,209
Members
405,274
Latest member
go2nivas

This Week's Hot Topics

Top