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

R

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
431
Office Version
  1. 365
Platform
  1. Windows
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
 
Sort by date Sort by votes
FormR said:
Hi, do you have any comments about post #17 - referencing a single row in the BYROW(UNIQUE(...)) part really doesn't make much sense! A clear and concise example of the input you have and the output you expect, posted using XL2BB, would really help to clear up any confusion and will allow your potential helpers to suggest the most appropriate function(s) to use.
Click to expand...
I think I did the XL2BB right maybe not but last column want the same result but a different order for results like this: 290 = Raymann Patao 01a
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi, this requires a header row, the formula in G2 should be copied down as required (which I think is your intention, rather than a formula that spills).

Book1
ABCDEFG
1
201aRaymannPatao1616 Place170290 = Raymann Patao 01a
301aRaymannPatao2020th Place120 
401cRobertContreras11st Place3203026 = Robert Contreras 01c
501cRobertContreras22nd Place310 
601cRobertContreras33rd Place300 
701cRobertContreras55 Place280 
801cRobertContreras77 Place260 
901cRobertContreras88 Place250 
1001cRobertContreras99 Place240 
1101cRobertContreras1111th Place220 
1201cRobertContreras1111th Place210 
1301cRobertContreras1313th Place200 
1401cRobertContreras1818 Place150 
1501cRobertContreras2424th Place90 
1601cRobertContreras2525 Place76 
1701cRobertContreras2727 Place70 
1801cRobertContreras2929 Place50 
1903cJayButler2828 Place6060 = Jay Butler 03c
2003dEdgarJurado2222nd Place110110 = Edgar Jurado 03d
2109dBrianCaandoy1010th Place230460 = Brian Caandoy 09d
2209dBrianCaandoy1414th Place190 
2309dBrianCaandoy2929 Place40 
2414cJoelCheng2323rd Place100100 = Joel Cheng 14c
2518bJosephMartinez1515 Place180320 = Joseph Martinez 18b
2618bJosephMartinez1919 Place140 
2719dMarkNear2020th Place130130 = Mark Near 19d
2828aWillieParawan44th Place290800 = Willie Parawan 28a
2928aWillieParawan66 Place270 
3028aWillieParawan1616 Place160 
3128aWillieParawan2525 Place80 
32FALSEFALSEFALSEFALSEFALSE0 
33FALSEFALSEFALSEFALSEFALSE0 
34FALSEFALSEFALSEFALSEFALSE0 
35FALSEFALSEFALSEFALSEFALSE0 
36FALSEFALSEFALSEFALSEFALSE0 
37FALSEFALSEFALSEFALSEFALSE0 
38FALSEFALSEFALSEFALSEFALSE0 
39FALSEFALSEFALSEFALSEFALSE0 
40FALSEFALSEFALSEFALSEFALSE0 
41FALSEFALSEFALSEFALSEFALSE0 
Sheet1
Cell Formulas
RangeFormula
G2:G41G2=IF(AND(A2<>A1,A2<>FALSE),TEXTJOIN(" ",1,SUMIFS($F$2:$F$41,$A$2:$A$41,A2),"=",B2,C2,A2),"")
 
Upvote 0
FormR said:
Hi, this requires a header row, the formula in G2 should be copied down as required (which I think is your intention, rather than a formula that spills).

Book1
ABCDEFG
1
201aRaymannPatao1616 Place170290 = Raymann Patao 01a
301aRaymannPatao2020th Place120 
401cRobertContreras11st Place3203026 = Robert Contreras 01c
501cRobertContreras22nd Place310 
601cRobertContreras33rd Place300 
701cRobertContreras55 Place280 
801cRobertContreras77 Place260 
901cRobertContreras88 Place250 
1001cRobertContreras99 Place240 
1101cRobertContreras1111th Place220 
1201cRobertContreras1111th Place210 
1301cRobertContreras1313th Place200 
1401cRobertContreras1818 Place150 
1501cRobertContreras2424th Place90 
1601cRobertContreras2525 Place76 
1701cRobertContreras2727 Place70 
1801cRobertContreras2929 Place50 
1903cJayButler2828 Place6060 = Jay Butler 03c
2003dEdgarJurado2222nd Place110110 = Edgar Jurado 03d
2109dBrianCaandoy1010th Place230460 = Brian Caandoy 09d
2209dBrianCaandoy1414th Place190 
2309dBrianCaandoy2929 Place40 
2414cJoelCheng2323rd Place100100 = Joel Cheng 14c
2518bJosephMartinez1515 Place180320 = Joseph Martinez 18b
2618bJosephMartinez1919 Place140 
2719dMarkNear2020th Place130130 = Mark Near 19d
2828aWillieParawan44th Place290800 = Willie Parawan 28a
2928aWillieParawan66 Place270 
3028aWillieParawan1616 Place160 
3128aWillieParawan2525 Place80 
32FALSEFALSEFALSEFALSEFALSE0 
33FALSEFALSEFALSEFALSEFALSE0 
34FALSEFALSEFALSEFALSEFALSE0 
35FALSEFALSEFALSEFALSEFALSE0 
36FALSEFALSEFALSEFALSEFALSE0 
37FALSEFALSEFALSEFALSEFALSE0 
38FALSEFALSEFALSEFALSEFALSE0 
39FALSEFALSEFALSEFALSEFALSE0 
40FALSEFALSEFALSEFALSEFALSE0 
41FALSEFALSEFALSEFALSEFALSE0 
Sheet1
Cell Formulas
RangeFormula
G2:G41G2=IF(AND(A2<>A1,A2<>FALSE),TEXTJOIN(" ",1,SUMIFS($F$2:$F$41,$A$2:$A$41,A2),"=",B2,C2,A2),"")
Click to expand...

The formula you provided is perfect.

=BYROW(UNIQUE(A1:C1),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(F1:F38,A1:A38,INDEX(br,0,1),B1:B38,INDEX(br,0,2),C1:C38,INDEX(br,,3)))))

The ONLY thing needed now is to change the Order of the RESULTS.
The Current formula above gives me the following results: 01a Raymann Patao = 290

I need the following result so that the highest number sorts to the top like this: 290 = Raymann Patao 01a
 
Upvote 0
FormR said:
it does not produce anything like the results you say you expect
Click to expand...
Here it is not copied down.
Book1
ABCDEFG
101aRaymannPatao1616 Place17001a Raymann Patao = 290
201aRaymannPatao2020th Place120
301cRobertContreras11st Place320
401cRobertContreras22nd Place310
501cRobertContreras33rd Place300
601cRobertContreras55 Place280
701cRobertContreras77 Place260
801cRobertContreras88 Place250
901cRobertContreras99 Place240
1001cRobertContreras1111th Place220
1101cRobertContreras1111th Place210
1201cRobertContreras1313th Place200
1301cRobertContreras1818 Place150
1401cRobertContreras2424th Place90
1501cRobertContreras2525 Place76
1601cRobertContreras2727 Place70
1701cRobertContreras2929 Place50
1803cJayButler2828 Place60
1903dEdgarJurado2222nd Place110
2009dBrianCaandoy1010th Place230
2109dBrianCaandoy1414th Place190
2209dBrianCaandoy2929 Place40
2314cJoelCheng2323rd Place100
2418bJosephMartinez1515 Place180
2518bJosephMartinez1919 Place140
2619dMarkNear2020th Place130
2728aWillieParawan44th Place290
2828aWillieParawan66 Place270
2928aWillieParawan1616 Place160
3028aWillieParawan2525 Place80
31FALSEFALSEFALSEFALSEFALSE0
32FALSEFALSEFALSEFALSEFALSE0
33FALSEFALSEFALSEFALSEFALSE0
34FALSEFALSEFALSEFALSEFALSE0
35FALSEFALSEFALSEFALSEFALSE0
36FALSEFALSEFALSEFALSEFALSE0
37FALSEFALSEFALSEFALSEFALSE0
38FALSEFALSEFALSEFALSEFALSE0
39FALSEFALSEFALSEFALSEFALSE0
40FALSEFALSEFALSEFALSEFALSE0
Sheet1
Cell Formulas
RangeFormula
G1G1=BYROW(UNIQUE(A1:C1),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(F1:F38,A1:A38,INDEX(br,0,1),B1:B38,INDEX(br,0,2),C1:C38,INDEX(br,,3)))))


And here it is, copied down (which it is not intended for).
Book1
ABCDEFG
101aRaymannPatao1616 Place17001a Raymann Patao = 290
201aRaymannPatao2020th Place12001a Raymann Patao = 120
301cRobertContreras11st Place32001c Robert Contreras = 3026
401cRobertContreras22nd Place31001c Robert Contreras = 2706
501cRobertContreras33rd Place30001c Robert Contreras = 2396
601cRobertContreras55 Place28001c Robert Contreras = 2096
701cRobertContreras77 Place26001c Robert Contreras = 1816
801cRobertContreras88 Place25001c Robert Contreras = 1556
901cRobertContreras99 Place24001c Robert Contreras = 1306
1001cRobertContreras1111th Place22001c Robert Contreras = 1066
1101cRobertContreras1111th Place21001c Robert Contreras = 846
1201cRobertContreras1313th Place20001c Robert Contreras = 636
1301cRobertContreras1818 Place15001c Robert Contreras = 436
1401cRobertContreras2424th Place9001c Robert Contreras = 286
1501cRobertContreras2525 Place7601c Robert Contreras = 196
1601cRobertContreras2727 Place7001c Robert Contreras = 120
1701cRobertContreras2929 Place5001c Robert Contreras = 50
1803cJayButler2828 Place6003c Jay Butler = 60
1903dEdgarJurado2222nd Place11003d Edgar Jurado = 110
2009dBrianCaandoy1010th Place23009d Brian Caandoy = 460
2109dBrianCaandoy1414th Place19009d Brian Caandoy = 230
2209dBrianCaandoy2929 Place4009d Brian Caandoy = 40
2314cJoelCheng2323rd Place10014c Joel Cheng = 100
2418bJosephMartinez1515 Place18018b Joseph Martinez = 320
2518bJosephMartinez1919 Place14018b Joseph Martinez = 140
2619dMarkNear2020th Place13019d Mark Near = 130
2728aWillieParawan44th Place29028a Willie Parawan = 800
2828aWillieParawan66 Place27028a Willie Parawan = 510
2928aWillieParawan1616 Place16028a Willie Parawan = 240
3028aWillieParawan2525 Place8028a Willie Parawan = 80
31FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
32FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
33FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
34FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
35FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
36FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
37FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
38FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
39FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
40FALSEFALSEFALSEFALSEFALSE0FALSE FALSE FALSE = 0
Sheet1
Cell Formulas
RangeFormula
G1:G40G1=BYROW(UNIQUE(A1:C1),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(F1:F38,A1:A38,INDEX(br,0,1),B1:B38,INDEX(br,0,2),C1:C38,INDEX(br,,3)))))
 
Upvote 0
OK let me try to explain the layout.

First faze I extract the data in tab1

01cRobertContreras72411st Place320
01cRobertContreras72422nd Place310
01cRobertContreras72433rd Place300
28aWillieParawan69344th Place290
01cRobertContreras72455 Place280
28aWillieParawan69366 Place270
01cRobertContreras72477 Place260
01cRobertContreras72488 Place250
01cRobertContreras72499 Place240
09dBrianCaandoy6781010th Place230
01cRobertContreras7241111th Place220
01cRobertContreras7241111th Place210
01cRobertContreras7241313th Place200
09dBrianCaandoy6781414th Place190
18bJosephMartinez6691515 Place180
01aRaymannPatao6681616 Place170
28aWillieParawan6931616 Place160
01cRobertContreras7241818 Place150
18bJosephMartinez6691919 Place140
19dMarkNear6602020th Place130
01aRaymannPatao6682020th Place120
03dEdgarJurado6592222nd Place110
14cJoelCheng6582323rd Place100
01cRobertContreras7242424th Place90
28aWillieParawan6932525 Place80
01cRobertContreras7242525 Place76
01cRobertContreras7242727 Place70
03cJayButler6542828 Place60
01cRobertContreras7242929 Place50
09dBrianCaandoy6782929 Place40
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSEFALSE0


Faze 2 I grab the info from TAB1 into TAB2 and do an auto sort on the refrence # attached to each person so they are all sorted together like this.
SportingNews.com Fantasy Source - NFL
01aRaymannPatao1616 Place17001a Raymann Patao = 290
01aRaymannPatao2020th Place120
01cRobertContreras11st Place32001c Robert Contreras = 3026
01cRobertContreras22nd Place310
01cRobertContreras33rd Place300
01cRobertContreras55 Place280
01cRobertContreras77 Place260
01cRobertContreras88 Place250
01cRobertContreras99 Place240
01cRobertContreras1111th Place220
01cRobertContreras1111th Place210
01cRobertContreras1313th Place200
01cRobertContreras1818 Place150
01cRobertContreras2424th Place90
01cRobertContreras2525 Place76
01cRobertContreras2727 Place70
01cRobertContreras2929 Place50
03cJayButler2828 Place6003c Jay Butler = 60
03dEdgarJurado2222nd Place11003d Edgar Jurado = 110
09dBrianCaandoy1010th Place23009d Brian Caandoy = 460
09dBrianCaandoy1414th Place190
09dBrianCaandoy2929 Place40
14cJoelCheng2323rd Place10014c Joel Cheng = 100
18bJosephMartinez1515 Place18018b Joseph Martinez = 320
18bJosephMartinez1919 Place140
19dMarkNear2020th Place13019d Mark Near = 130
28aWillieParawan44th Place29028a Willie Parawan = 800
28aWillieParawan66 Place270
28aWillieParawan1616 Place160
28aWillieParawan2525 Place80
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0

Then I must go INTO TAB2 and DO SOME MANUAL activities by copying your Perfect Code to each persons first entry that occurs to GET a TOTAL like this
SportingNews.com Fantasy Source - NFL
01aRaymannPatao1616 Place17001a Raymann Patao = 290
01aRaymannPatao2020th Place120
01cRobertContreras11st Place32001c Robert Contreras = 3026
01cRobertContreras22nd Place310
01cRobertContreras33rd Place300
01cRobertContreras55 Place280
01cRobertContreras77 Place260
01cRobertContreras88 Place250
01cRobertContreras99 Place240
01cRobertContreras1111th Place220
01cRobertContreras1111th Place210
01cRobertContreras1313th Place200
01cRobertContreras1818 Place150
01cRobertContreras2424th Place90
01cRobertContreras2525 Place76
01cRobertContreras2727 Place70
01cRobertContreras2929 Place50
03cJayButler2828 Place6003c Jay Butler = 60
03dEdgarJurado2222nd Place11003d Edgar Jurado = 110
09dBrianCaandoy1010th Place23009d Brian Caandoy = 460
09dBrianCaandoy1414th Place190
09dBrianCaandoy2929 Place40
14cJoelCheng2323rd Place10014c Joel Cheng = 100
18bJosephMartinez1515 Place18018b Joseph Martinez = 320
18bJosephMartinez1919 Place140
19dMarkNear2020th Place13019d Mark Near = 130
28aWillieParawan44th Place29028a Willie Parawan = 800
28aWillieParawan66 Place270
28aWillieParawan1616 Place160
28aWillieParawan2525 Place80
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0
FALSEFALSEFALSEFALSEFALSE0


Then I pull all of TAB2's info into another TAB which is 3 but this time I SORT on Column G Descending so all the numbers your CODE gathering during my Manual
activity in TAB2 is all sorted to the TOP like this:

SportingNews.com Fantasy Source - NFL
28aWillieParawan44th Place29028a Willie Parawan = 800
19dMarkNear2020th Place13019d Mark Near = 130
18bJosephMartinez1515 Place18018b Joseph Martinez = 320
14cJoelCheng2323rd Place10014c Joel Cheng = 100
09dBrianCaandoy1010th Place23009d Brian Caandoy = 460
03dEdgarJurado2222nd Place11003d Edgar Jurado = 110
03cJayButler2828 Place6003c Jay Butler = 60
01cRobertContreras11st Place32001c Robert Contreras = 3026
01aRaymannPatao1616 Place17001a Raymann Patao = 290
01aRaymannPatao2020th Place120
0​
01cRobertContreras22nd Place310
0​
01cRobertContreras33rd Place300
0​
01cRobertContreras55 Place280
0​
01cRobertContreras77 Place260
0​
01cRobertContreras88 Place250
0​
01cRobertContreras99 Place240
0​
01cRobertContreras1111th Place220
0​
01cRobertContreras1111th Place210
0​
01cRobertContreras1313th Place200
0​
01cRobertContreras1818 Place150
0​
01cRobertContreras2424th Place90
0​
01cRobertContreras2525 Place76
0​
01cRobertContreras2727 Place70
0​
01cRobertContreras2929 Place50
0​
09dBrianCaandoy1414th Place190
0​
09dBrianCaandoy2929 Place40
0​
18bJosephMartinez1919 Place140
0​
28aWillieParawan66 Place270
0​
28aWillieParawan1616 Place160
0​
28aWillieParawan2525 Place80
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​
FALSEFALSEFALSEFALSEFALSE0
0​


Now the Only Piece Left is to have the Highest total person in column G sort to the top so it should be like this:

01cRobertContreras11st Place3203026 = Robert Contreras 01c

28aWillieParawan44th Place290800 = Willie Parawan 28a

09d Brian Caandoy 10 10th Place 230 460 = Brian Caandoy 09d


18bJosephMartinez1515 Place180320 = Joseph Martinez 18b


01aRaymannPatao1616 Place170290 = Raymann Patao 01a


19dMarkNear2020th Place130130 = Mark Near 19d

So all I need is to Grab TAB3 and get my FINAL RESULTS in TAB4 :)

That is the process happening here :)
 
Last edited:
Upvote 0
I used the code a few weeks ago and it worked great accept for the last person it left out the last total?
Here was the code used:

=@BYROW(UNIQUE(A19:C19),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(F19:F56,A19:A56,@INDEX(br,0,1),B19:B56,@INDEX(br,0,2),C19:C56,@INDEX(br,,3)))))

I have enclosed a pic of the data. The total for all of Anthony Vanderaa should be 594 but the formula above returned a total of 484. I am not sure why it left out the last total of 110?

Thanks for your help - this is a post you had helped me on back in June of this year.
 

Attachments

  • Mr Excel pic.jpg
    Mr Excel pic.jpg
    123.8 KB · Views: 2
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA copy set amount of rows into forms until end of data set
2
Replies
15
Views
708
willow1985
willow1985
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
98
HighAndWilder
H
D
  • Question Question
Duplicate cells not formatting problem.
Replies
6
Views
274
etaf
etaf
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
2K
pawcoyote
P
C
  • Solved
Ranking on a Criterion Within a Second Criterion
Replies
3
Views
391
Fluff
Fluff

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top