Merging data without VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-2279878%22%20slang%3D%22en-US%22%3EMerging%20data%20without%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279878%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20issue%20with%20Excel%2C%20I%20have%20to%20merge%20data%20but%20I%20can't%20use%20VLOOKUP%20because%20it's%20not%20showing%20duplicates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20data%20I%20have%3A%3C%2FP%3E%3CTABLE%20width%3D%22456%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22114%22%3EEXG%3C%2FTD%3E%3CTD%20width%3D%22114%22%3ESTB%3C%2FTD%3E%3CTD%20width%3D%22114%22%3ESSS%3C%2FTD%3E%3CTD%20width%3D%22114%22%3ESTB%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01300%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3CTD%3ESTB_SSI_01300%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01490%3C%2FTD%3E%3CTD%3ESSS_TEST_00470%3C%2FTD%3E%3CTD%3ESTB_SSI_01670%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1650%3C%2FTD%3E%3CTD%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%3ESSS_TEST_00471%3C%2FTD%3E%3CTD%3ESTB_SSI_01670%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1655%3C%2FTD%3E%3CTD%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3CTD%3ESTB_SSI_01000%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01270%3C%2FTD%3E%3CTD%3ESSS_TEST_00472%3C%2FTD%3E%3CTD%3ESTB_SSI_01270%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1001%3C%2FTD%3E%3CTD%3ESTB_SSI_01670%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3CTD%3ESTB_SSI_01200%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20here%20is%20what%20I'd%20like%20to%20have.%3C%2FP%3E%3CTABLE%20width%3D%22328%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22104%22%3EEXG%3C%2FTD%3E%3CTD%20width%3D%22107%22%3ESTB%3C%2FTD%3E%3CTD%20width%3D%22117%22%3ESSS%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01300%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1001%3C%2FTD%3E%3CTD%3ESTB_SSI_01670%3C%2FTD%3E%3CTD%3ESSS_TEST_00470%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1001%3C%2FTD%3E%3CTD%3ESTB_SSI_01670%3C%2FTD%3E%3CTD%3ESSS_TEST_00471%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESTB_SSI_01000%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01270%3C%2FTD%3E%3CTD%3ESSS_TEST_00472%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ESTB_SSI_01200%3C%2FTD%3E%3CTD%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%3ESTB_SSI_01490%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1650%3C%2FTD%3E%3CTD%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEXG_SSI_1655%3C%2FTD%3E%3CTD%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThx%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2279878%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2291751%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20data%20without%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2291751%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20try%20XLOOKUP%2C%20here%20is%20the%20result%3A%3C%2FP%3E%3CTABLE%20width%3D%22328%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01300%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1001%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01670%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00470%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1001%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01670%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00471%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01000%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01270%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00472%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01200%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3ESSS_TEST_00802%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1500%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01490%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3EEXG_SSI_1650%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22108.8px%22%20height%3D%2229px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3EEXG_SSI_1650%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22115.2px%22%20height%3D%2229px%22%3ESTB_SSI_01470%3C%2FTD%3E%3CTD%20width%3D%22126.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20last%20one%20should%20be%20EXG_SSI_1655%20and%20not%201650...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I have an issue with Excel, I have to merge data but I can't use VLOOKUP because it's not showing duplicates.

 

Here is the data I have:

EXGSTBSSSSTB
EXG_SSI_1500STB_SSI_01300SSS_TEST_00802STB_SSI_01300
EXG_SSI_1500STB_SSI_01490SSS_TEST_00470STB_SSI_01670
EXG_SSI_1650STB_SSI_01470SSS_TEST_00471STB_SSI_01670
EXG_SSI_1655STB_SSI_01470SSS_TEST_00802STB_SSI_01000
EXG_SSI_1500STB_SSI_01270SSS_TEST_00472STB_SSI_01270
EXG_SSI_1001STB_SSI_01670SSS_TEST_00802STB_SSI_01200

 

And here is what I'd like to have.

EXGSTBSSS
EXG_SSI_1500STB_SSI_01300SSS_TEST_00802
EXG_SSI_1001STB_SSI_01670SSS_TEST_00470
EXG_SSI_1001STB_SSI_01670SSS_TEST_00471
 STB_SSI_01000SSS_TEST_00802
EXG_SSI_1500STB_SSI_01270SSS_TEST_00472
 STB_SSI_01200SSS_TEST_00802
EXG_SSI_1500STB_SSI_01490 
EXG_SSI_1650STB_SSI_01470 
EXG_SSI_1655STB_SSI_01470 

 

Thx !

1 Reply

If I try XLOOKUP, here is the result:

EXG_SSI_1500STB_SSI_01300SSS_TEST_00802
EXG_SSI_1001STB_SSI_01670SSS_TEST_00470
EXG_SSI_1001STB_SSI_01670SSS_TEST_00471
0STB_SSI_01000SSS_TEST_00802
EXG_SSI_1500STB_SSI_01270SSS_TEST_00472
0STB_SSI_01200SSS_TEST_00802
EXG_SSI_1500STB_SSI_01490 
EXG_SSI_1650STB_SSI_01470 
EXG_SSI_1650STB_SSI_01470 

 

The last one should be EXG_SSI_1655 and not 1650...

 

Any idea ?

 

Thanks !