Sum the values in one column associated with the unique ID in another column. and concatenate the data

[sontho]

I come from Vietnam . I am having over 100k data. But my data has duplicate phone numbers. But the data related to these duplicate phone numbers is different and inconsistent.

Thanks for everyone's help according to the desired result in excel

Thank you very much

 

[sontho]

Apologize for the ABOVE RESULTS I WAS WRONG. The EXACT RESULTS WILL BE

 

[sontho]

PLEASE HELP ME

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is any use.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 05 17.xlsm
	A	B	C	D	E	F	G
1	Phone	Name	Job	address	sex	revenue	real mone
2	854857858	John	Beautician	England	female	100	50
3	415245215	B		Sweden	male	200	100
4	854857858	K		Sweden	male	300	200
5	415245215	C	Beautician	England	female	400	200
6	854569856	D	Barber	Sweden	female	500	300
7	854857858	K	Astronaut	England	female	600	200
8	854857858	John	Beautician	England	female	700	300
9
10
11	Phone	Name	Job	address	sex	revenue	real mone
12	854857858	John K K John	Beautician	England	female	1700	750
13	415245215	B C	Beautician	Sweden	male	600	300
14	854569856	D	Barber	Sweden	female	500	300
sontho

Cell Formulas
Range	Formula
A12:A14	A12	=UNIQUE(A2:A8)
B12:B14	B12	=TEXTJOIN(" ",1,FILTER(B$2:B$8,A$2:A$8=A12,""))
C12:E14	C12	=INDEX(FILTER(C$2:C$8,($A$2:$A$8=$A12)*(C$2:C$8<>""),""),1)
F12:G14	F12	=SUMIF($A$2:$A$8,$A12,F$2:F$8)
Dynamic array formulas.

 

[sontho]

Welcome to the MrExcel board!

See if this is any use.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 05 17.xlsm
	A	B	C	D	E	F	G
1	Phone	Name	Job	address	sex	revenue	real mone
2	854857858	John	Beautician	England	female	100	50
3	415245215	B		Sweden	male	200	100
4	854857858	K		Sweden	male	300	200
5	415245215	C	Beautician	England	female	400	200
6	854569856	D	Barber	Sweden	female	500	300
7	854857858	K	Astronaut	England	female	600	200
8	854857858	John	Beautician	England	female	700	300
9
10
11	Phone	Name	Job	address	sex	revenue	real mone
12	854857858	John K K John	Beautician	England	female	1700	750
13	415245215	B C	Beautician	Sweden	male	600	300
14	854569856	D	Barber	Sweden	female	500	300
sontho

Cell Formulas
Range	Formula
A12:A14	A12	=UNIQUE(A2:A8)
B12:B14	B12	=TEXTJOIN(" ",1,FILTER(B$2:B$8,A$2:A$8=A12,""))
C12:E14	C12	=INDEX(FILTER(C$2:C$8,($A$2:$A$8=$A12)*(C$2:C$8<>""),""),1)
F12:G14	F12	=SUMIF($A$2:$A$8,$A12,F$2:F$8)
Dynamic array formulas.

Thanks you very much .
Please help me optimize the above formula. My data is 800,000 lines when I try to run =TEXTJOIN(" ",1,FILTER(B$2:B$8,A$2:A$8=A12,"")) excel is not responsive

 

[Peter_SSs]

when I try to run =TEXTJOIN(" ",1,FILTER(B$2:B$8,A$2:A$8=A12,"")) excel is not responsive

Presumably that is not what you actually ran, but rather with your large range?

I don't know of a formula that will do what you want in the Name column any more efficiently. A macro could do what you want though, would that be acceptable?
If so, about how many unique phone numbers would you expect to have in your 800,000 rows?

 

[sontho]

Presumably that is not what you actually ran, but rather with your large range?

I don't know of a formula that will do what you want in the Name column any more efficiently. A macro could do what you want though, would that be acceptable?
If so, about how many unique phone numbers would you expect to have in your 800,000 rows?

exactly i have 851,830 lines. After using Unique, the data is 842,657

 

[Peter_SSs]

...

A macro could do what you want though, would that be acceptable?

 

[sontho]

...

yes please help me this way

 

[Peter_SSs]

OK. It could be a day or so before I can look at it.