Compare and Combine Columns into One Column

[desibouy]

Hello All,

I have 3 columns, all using different Vlookups to find me the Internal ID of a product. I've used Vlookup against, SKU, MPN, and Vendor Code hence the 3 columns. I want to combine all 3 into 1 but first compare them.

• At first, I tried to use 'Concat' but if the value exists in 2 columns or 3 columns then it gets displayed as '1234512345' etc.
• I have tried to use an IF statement to see if I use that and then concat if the value is empty in row 3 then use row 2 and so forth but no luck
• lastly tried using the countIF formula but, to be honest, I have no idea what I'm doing LOL.

Here is an example - Sorry I used the Data from Line 697 I was doing something there.

LATEST KENNY-NIKKI FILE.xlsx
	A	B	C	D
697	AP840	45120	45120	45120
698	AP840-BIP	45121	45121	45121
699	DRV10416-1
700	DRVRTL10400	11972
701	DRVRTL10370BK	72705		72705
702	DRVRTL10370BC	72708
703	DRVRTL10370RC	72707		72707
704	RMA76100		1096
705	RMA75600EA		1110
706	DRVRTL10372BC	72703
707	DRVRTL10372RC	72702
708	DRV10350-1
709	DRVNRS185006	30742
710	DRVR728BL	11135
711	DRVRTL10304-SH
712	ZCH9201BL
713	ZCH9201BLK
714	ZCH9201BUR
715	AXLLG100
716	EW17148PM			28535
717	EW17358M			28536
718	EW17152			28539
Final List of Items

Cell Formulas
Range	Formula
D697:D700,D704:D718,D702	D697	=IFNA(VLOOKUP($A697,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$E:$F,2,FALSE),"")
C697:C715	C697	=IFNA(VLOOKUP($A697,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$D:$F,3,FALSE),"")
B697:B718	B697	=IFNA(VLOOKUP($A697,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$C:$F,4,FALSE),"")

Any help is appreciated. I'm not an expert in excel so if someone can break it down for me then at least I can follow instructions.

thanks

 

[desibouy]

bump. Anyone, please? I have 9700 lines to go through

 

[Fluff]

How about

=TEXTJOIN(", ",,UNIQUE(XLOOKUP($A697,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$C:$C,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$D:$F,"",0)))

 

[desibouy]

Hi, this does not work. I am only looking for B, C, and D joins, If #I haven't explained this above then I am sorry.

Ankita -Nikki file.xlsx
	B	C	D	E
695
696	44850	44850	44850	MSC263701, MSC263701, 44850
697	44851	44851	44851	MSC263701H, MSC263701H, 44851
698
699
700
701
702
703
704
705
706	51749		51749	ALO100, DRVALO100, 51749
707
708
709
710
711
712	45120	45120	45120	AP840, AP840, 45120
713	45121	45121	45121	AP840-BIP, AP840-BIP, 45121
714
715	11972			RTL10400, RTL10400, 11972
716	72705		72705	RTL10370BK, RTL10370BK, 72705
717	72708			RTL10370BC, RTL10370BC, 72708
718	72707		72707	RTL10370RC, RTL10370RC, 72707
719		1096
Sheet1

Cell Formulas
Range	Formula
E696:E718	E696	=TEXTJOIN(", ",,UNIQUE(XLOOKUP($A696,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$C:$C,'[SKU MPN MATCH.xlsx]SKUMPNMATCHResults'!$D:$F,"",0)))

 

[Fluff]

I thought you wanted to concat the values from the vlookup you had in columns B:C.