Find and concat same values in excel

[parthi_75]

HI,
I NEED TO FIND VALUES AND CONCAT IT. BELOW IS THE DETAILS

DATA'S
a 1
b 2
c 3
d 4
a 100
b 101
c 102
d 103


NEED FORMULA AS BELOW
A = 1,100
B= 2,101
C=3,102

THANK YOU & REGARDS
PARTHI

 

[Peter_SSs]

If using Excel 2016 (not the stand-alone version) you could try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down. You could then format the column to show the commas.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
2	a	1		a	1100
3	b	2		b	2101
4	c	3		c	3102
5	d	4		d	4103
6	a	100
7	b	101
8	c	102
9	d	103
Sheet2 (3)

Cell Formulas
Range	Formula
E2	{=TEXTJOIN("",TRUE,IF(A$2:A$9=D2,B$2:B$9,""))+0}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[parthi_75]

thank you for your help and we are useing excel 2007 which i could not able to find "TEXTJOIN" function. thanks again

 

[Peter_SSs]

thank you for your help and we are useing excel 2007 which i could not able to find "TEXTJOIN" function. thanks again

No, Excel 2007 does not have TEXTJOIN, that's why I specified Excel 2016.

Is there always 2, and no more than 2, values to join for each value in the first column?

 

[parthi_75]

thank you for your support and reply.

it will be more than 2 not more than 20. thank you

for example
a 1
b 2
c 3
d 4
a 100
b 101
c 102
d 103
a test1
b test2
c test3
d test4
a test100
b test101
c test102
d test103


Result
a 1,100,test1,test100

thank you

 

[Peter_SSs]

it will be more than 2 not more than 20.

Then without the TEXTJOIN function it isn't feasible with a worksheet formula. You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function myConcat(sMatchValue As String, rData As Range) As String
  Dim aData As Variant
  Dim i As Long
  
  aData = rData.Value
  For i = 1 To UBound(aData)
    If aData(i, 1) = sMatchValue Then myConcat = myConcat & "," & aData(i, 2)
  Next i
  myConcat = Mid(myConcat, 2)
End Function

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1
2	a	1		a	1,100,test1,test100
3	b	2		b	2,101,test2,test101
4	c	3		c	3,102,test3,test102
5	d	4		d	4,103,test4,test103
6	a	100
7	b	101
8	c	102
9	d	103
10	a	test1
11	b	test2
12	c	test3
13	d	test4
14	a	test100
15	b	test101
16	c	test102
17	d	test103
myConcat

Cell Formulas
Range	Formula
E2	=myConcat(D2,A$2:B$17)

 

[parthi_75]

it works in 2k7, thank you very much for your help and support,

kind regards
parthi

 

[Peter_SSs]

it works in 2k7, thank you very much for your help and support,

kind regards
parthi

Glad it worked for you.