Extracting name once & amount totaled how can I do it with Formulas?

[RudeBoy]

I currently I have users with amount and they show up like this on my excel ledger:

James Smith 100
James Smith 50
Bob James 45
Jim Kraig 30
Tom Jones 75
Tom Jones 10
Billy Jack 36
Billy Jack 75
Billy Jack 75


Now I do a Sort to group them all together by name & numbers.
But I want to break them out with an =INDEXor array etc to be like this:

James Smith = 150
Bob James = 45
Jim Kraig = 30
Tom Jones = 85
Billy Jack = 186

Can some one Helpppppppppppp Thanks so much

 

[Cubist]

1) Are the names and amounts inside the same cell?
2) Which version of XL are you using?
3) Have you considered Pivot tables?

 

[alansidman]

Can do a groupby either in Excel or Power Query as well as a Pivot table as mentioned earlier. Knowing the XL version would let us know which of these would work for you.

 

[rlv01]

I cannot tell how your data looks.

Book2
	A
1	James Smith 100
2	James Smith 50
3	Bob James 45
4	Jim Kraig 30
5	Tom Jones 75
6	Tom Jones 10
7	Billy Jack 36
8	Billy Jack 75
9	Billy Jack 75
Sheet1

Book2
	A	B
1	James Smith	100
2	James Smith	50
3	Bob James	45
4	Jim Kraig	30
5	Tom Jones	75
6	Tom Jones	10
7	Billy Jack	36
8	Billy Jack	75
9	Billy Jack	75
Sheet2

Book2
	A	B	C
1	James	Smith	100
2	James	Smith	50
3	Bob	James	45
4	Jim	Kraig	30
5	Tom	Jones	75
6	Tom	Jones	10
7	Billy	Jack	36
8	Billy	Jack	75
9	Billy	Jack	75
Sheet3

Consider using this free tool instead to post some sample data.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[RudeBoy]

1) Are the names and amounts inside the same cell?
2) Which version of XL are you using?
3) Have you considered Pivot tables?

First name is in its own cell last name in its own cell and amount in its own cell. I use office 2007 and office 365 the excel version in each.

Thanks for your help

 

[Georgiboy]

You may need to decide which version you want it to work in, for example the below options will work in 365 but not older version. There is a new function called GRPOUPBY in newer (demo) versions of Excel 365 but I don't have it:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	James	Smith	100		James	Smith	150		James	Smith	150
2	James	Smith	50		Bob	James	45		Bob	James	45
3	Bob	James	45		Jim	Kraig	30		Jim	Kraig	30
4	Jim	Kraig	30		Tom	Jones	85		Tom	Jones	85
5	Tom	Jones	75		Billy	Jack	186		Billy	Jack	186
6	Tom	Jones	10
7	Billy	Jack	36
8	Billy	Jack	75
9	Billy	Jack	75
Sheet1

Cell Formulas
Range	Formula
E1:G5	E1	=LET(r,A1:C9, f,INDEX(r,,1),l,INDEX(r,,2),n,INDEX(r,,3), HSTACK(UNIQUE(f:l),SUMIFS(n,f,UNIQUE(f),l,UNIQUE(l))) )
I1:K5	I1	=HSTACK(UNIQUE(A1:B9),SUMIFS(C1:C9,A1:A9,UNIQUE(A1:A9),B1:B9,UNIQUE(B1:B9)))
Dynamic array formulas.

Could also use a pivot table?

 

[FormR]

Hi, here's another couple of XL365 options pending the full roll out of GROUPBY().

Book1
	A	B	C	D	E	F	G	H	I
1	James	Smith	100		James Smith = 150		James	Smith	150
2	James	Smith	50		Bob James = 45		Bob	James	45
3	Bob	James	45		Jim Kraig = 30		Jim	Kraig	30
4	Jim	Kraig	30		Tom Jones = 85		Tom	Jones	85
5	Tom	Jones	75		Billy Jack = 186		Billy	Jack	186
6	Tom	Jones	10
7	Billy	Jack	36
8	Billy	Jack	75
9	Billy	Jack	75
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=BYROW(UNIQUE(A1:B9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1)))))
G1:I5	G1	=LET(u,UNIQUE(A1:B9),HSTACK(u,BYROW(u,LAMBDA(br,SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1))))))
Dynamic array formulas.

@Georgiboy - I think your suggestion might run into problems if there are two people that share a first name or two people that share a surname.

 

[Joe4]

First name is in its own cell last name in its own cell and amount in its own cell. I use office 2007 and office 365 the excel version in each.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alansidman]

Here are three more ways to do it.
1. GroupBy formula
2. Pivot Table
3. Power Query

Book6
	A	B	C	D	E
1	Name	Value
2	James Smith	100		Billy Jack	186
3	James Smith	50		Bob James	45
4	Bob James	45		James Smith	150
5	Jim Kraig	30		Jim Kraig	30
6	Tom Jones	75		Tom Jones	85
7	Tom Jones	10		Total	496
8	Billy Jack	36
9	Billy Jack	75
10	Billy Jack	75
Sheet1

Cell Formulas
Range	Formula
D2:E7	D2	=GROUPBY(A2:A10,B2:B10,SUM)
Dynamic array formulas.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Total", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

 

[Georgiboy]

@Georgiboy - I think your suggestion might run into problems if there are two people that share a first name or two people that share a surname.

Well spotted, you are indeed correct.