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

 

[alansidman]

An alternative with Power Query. No formula. Only the UI.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

Book1
	A	B	C	D	E
1	Names	Values		Names	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
Sheet1

Cell Formulas
Range	Formula
B16	B16	=SUM(TAKE(UNIQUE(A2:B15),,2))