How to auto merge duplicate cells in a column?

[Pancakey]

Hello,

I am curious to know if there is a way to auto merge cells with duplicate values within a column?
See below sample data.

Names	Values
Jack	65
Arielle	44
Arielle	44
Tim	30
Tim	30
Tim	30
Mary	28
Anna	50
Mary	28
Mary	28
Mary	28
Liam	68
Liam	68
Ken	45

 

[dreid1011]

Maybe this:

Book1
	A	B	C	D	E
1	Names	Values		Unique Values
2	Jack	65		Jack	65
3	Arielle	44		Arielle	44
4	Arielle	44		Tim	30
5	Tim	30		Mary	28
6	Tim	30		Anna	50
7	Tim	30		Mary	10
8	Mary	28		Liam	68
9	Anna	50		Ken	45
10	Mary	28
11	Mary	28
12	Mary	10
13	Liam	68
14	Liam	68
15	Ken	45
Sheet2

Cell Formulas
Range	Formula
D2:E9	D2	=UNIQUE(A2:B15)
Dynamic array formulas.

 

[Pancakey]

Thank you. Would you happen to know if there is an alternative way to do this where I don't have to apply a direct formula to the cell?

 

[Pancakey]

I also forgot to ask, is there a way to also count a sum of cells within a column where it will skip counting duplicates values?

 

[dreid1011]

Yes, if you go to the Data tab on the ribbon, select your data and use Remove Duplicates. Mind you, this is deleting the duplicate rows.

 

[dreid1011]

I also forgot to ask, is there a way to also count a sum of cells within a column where it will skip counting duplicates values?

Are you also trying to avoid using a formula here too?

 

[Pancakey]

Are you also trying to avoid using a formula here too?

Thank you for confirming! Yes, I would prefer to avoid using a formula if there is an option. However, if there is no option, I can maybe reference the appropriate cells.

 

[dreid1011]

Here is a modified version of the first formula I suggested which will sum the unique values in the second column.

Book1
	A	B	C	D	E
1	Names	Values		Unique Values
2	Jack	65		Jack	65
3	Arielle	44		Arielle	44
4	Arielle	44		Tim	30
5	Tim	30		Mary	28
6	Tim	30		Anna	50
7	Tim	30		Mary	10
8	Mary	28		Liam	68
9	Anna	50		Ken	45
10	Mary	28
11	Mary	28
12	Mary	10
13	Liam	68
14	Liam	68
15	Ken	45
16		340
Sheet2

Cell Formulas
Range	Formula
D2:E9	D2	=UNIQUE(A2:B15)
B16	B16	=SUM(TAKE(UNIQUE(A2:B15),,2))
Dynamic array formulas.

Thank you for confirming! Yes, I would prefer to avoid using a formula if there is an option. However, if there is no option, I can maybe reference the appropriate cells.

Where would you want the total to be displayed then?

 

[Pancakey]

Here is a modified version of the first formula I suggested which will sum the unique values in the second column.

Book1
	A	B	C	D	E
1	Names	Values		Unique Values
2	Jack	65		Jack	65
3	Arielle	44		Arielle	44
4	Arielle	44		Tim	30
5	Tim	30		Mary	28
6	Tim	30		Anna	50
7	Tim	30		Mary	10
8	Mary	28		Liam	68
9	Anna	50		Ken	45
10	Mary	28
11	Mary	28
12	Mary	10
13	Liam	68
14	Liam	68
15	Ken	45
16		340
Sheet2

Cell Formulas
Range	Formula
D2:E9	D2	=UNIQUE(A2:B15)
B16	B16	=SUM(TAKE(UNIQUE(A2:B15),,2))
Dynamic array formulas.

Where would you want the total to be displayed then?

So I am hoping to have the duplicate values merged within the same cells where the data are, and I am trying to see if there is a way without using a formula.

Regarding the sum formula for the unique values. If you don't mind, are you able to explain what the number "2" means in the formula?
=SUM(TAKE(UNIQUE(A2:B15),,2))

 

[dreid1011]

TAKE	Lookup and reference: Returns a specified number of contiguous rows or columns from the start or end of an array

After rereading the description myself, the 2 should be a -1 indicating the last column in the array (1 would be the first column). So in this case, it is using only column B with the values. The 2 would be taking both columns A and B, which still manage to return the correct sum, but I would change it to -1 anyway.