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

R

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
428
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
Here is an updated version of what I (tried) to offer originally:
Book1
ABCDEFGHIJKL
1JamesSmith100JamesSmith150JamesSmith150
2JamesSmith50BobJames45BobJames45
3BobJames45JimKraig30JimKraig30
4JimKraig30TomJones85TomJones85
5TomJones75BillyJack186BillyJack186
6TomJones10BillyDavis10BillyDavis10
7BillyJack36TomDavis99TomDavis99
8BillyJack75
9BillyJack75
10BillyDavis10
11TomDavis99
Sheet1
Cell Formulas
RangeFormula
F1:H7F1=LET(r,A1:C11, f,INDEX(r,,1),l,INDEX(r,,2),n,INDEX(r,,3), HSTACK(UNIQUE(f:l),BYROW(UNIQUE(f&l),LAMBDA(x,SUM(IF(x=f&l,n))))) )
J1:L7J1=HSTACK(UNIQUE(A1:B11),BYROW(UNIQUE(A1:A11&B1:B11),LAMBDA(x,SUM(IF(x=A1:A11&B1:B11,C1:C11)))))
Dynamic array formulas.
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
FormR said:
=BYROW(UNIQUE(A1:B9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1)))))
Click to expand...

I am getting close but I am still Lost :(

But not getting the totals? Helpppppppppppppppppppp
01c Robert Contreras = 0
28a Willie Parawan = 0
09d Brian Caandoy = 0
18b Joseph Martinez = 0
01a Raymann Patao = 0
19d Mark Near = 0
03d Edgar Jurado = 0
14c Joel Cheng = 0
03c Jay Butler = 0


=BYROW(UNIQUE(A9:C38),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(D9:D38,B9:B38,TAKE(br,,1),C9:C38,TAKE(br,,-1)))))

I will upload t






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

Book1
ABCDEFGHI
1JamesSmith100James Smith = 150JamesSmith150
2JamesSmith50Bob James = 45BobJames45
3BobJames45Jim Kraig = 30JimKraig30
4JimKraig30Tom Jones = 85TomJones85
5TomJones75Billy Jack = 186BillyJack186
6TomJones10
7BillyJack36
8BillyJack75
9BillyJack75
Sheet1
Cell Formulas
RangeFormula
E1:E5E1=BYROW(UNIQUE(A1:B9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(C1:C9,A1:A9,TAKE(br,,1),B1:B9,TAKE(br,,-1)))))
G1:I5G1=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.
Click to expand...
 

Attachments

  • Pic2.jpg
    Pic2.jpg
    69.5 KB · Views: 6
  • Pic1.jpg
    Pic1.jpg
    87.3 KB · Views: 6
Upvote 0
Hi, it looks like you now have 3 columns and that you want the results in a single column - if so, you can try like this.

Book1
ABCDEF
101CJamesSmith10001C James Smith = 150
201CJamesSmith5001D Bob James = 45
301DBobJames45A03 Jim Kraig = 30
4A03JimKraig30F05 Tom Jones = 75
5F05TomJones75D06 Tom Jones = 10
6D06TomJones10T06 Billy Jack = 186
7T06BillyJack36
8T06BillyJack75
9T06BillyJack75
Sheet1
Cell Formulas
RangeFormula
F1:F6F1=BYROW(UNIQUE(A1:C9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(D1:D9,A1:A9,INDEX(br,0,1),B1:B9,INDEX(br,0,2),C1:C9,INDEX(br,,3)))))
Dynamic array formulas.


If column A is unique for each person then it can be simplified to this.

Book1
ABCDEF
101CJamesSmith10001C James Smith = 150
201CJamesSmith5001D Bob James = 45
301DBobJames45A03 Jim Kraig = 30
4A03JimKraig30F05 Tom Jones = 75
5F05TomJones75D06 Tom Jones = 10
6D06TomJones10T06 Billy Jack = 186
7T06BillyJack36
8T06BillyJack75
9T06BillyJack75
Sheet1
Cell Formulas
RangeFormula
F1:F6F1=BYROW(UNIQUE(A1:C9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(D1:D9,A1:A9,INDEX(br,0,1)))))
Dynamic array formulas.


If this isn't what you want, try to download and use the XL2BB tool (see here) to post your sample data, also be sure to include a mock up of your expected results.
 
Upvote 0
Solution
FormR said:
=BYROW(UNIQUE(A1:C9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(D1:D9,A1:A9,INDEX(br,0,1),B1:B9,INDEX(br,0,2),C1:C9,INDEX(br,,3)))))
Click to expand...
It is getting Close - I do not need all the other items that seem to kick out with that BYROW Code

Every-time a persons name changes I want to push his name once & then the total his name or names have associated with it.
I have enclosed a picture of the desired end result. I will also post a pic of what is coming out now from the first row using the BYROW CODE
Thanks for your help

I am using this code
=BYROW(UNIQUE(A1:C9),LAMBDA(br,TEXTJOIN(" ",1,br,"=",SUMIFS(D1:D59,A1:A59,INDEX(br,0,1),B1:B59,INDEX(br,0,2),C1:C59,INDEX(br,,3)))))
 

Attachments

  • Example using the BYROW Code-Current result.jpg
    Example using the BYROW Code-Current result.jpg
    190.2 KB · Views: 8
  • Excel Formula Data-Result Wanted.jpg
    Excel Formula Data-Result Wanted.jpg
    121.2 KB · Views: 10
Upvote 0
You are only showing us from row 9 in your picture. On what row does the list of names start?

In the desired end result image your showing a blank row between the names, do you really want that?? Your also showing the results in separate columns, if that's the result you want then why did you opt for the suggested formula that returns them in a single column?

Is "column A" unique for each person?

Images (especially ones that don't show us all the cells the formula is referencing) are not very helpful and require us to manually re-create your set-up for testing. Please post your sample data using the XL2BB add-in:
www.mrexcel.com

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.
www.mrexcel.com www.mrexcel.com
 
Last edited:
Upvote 0
FormR said:
You are only showing us from row 9 in your picture. On what row does the list of names start?

In the desired end result image your showing a blank row between the names, do you really want that?? Your also showing the results in separate columns, if that's the result you want then why did you opt for the suggested formula that returns them in a single column?

Is "column A" unique for each person?

Images (especially ones that don't show us all the cells the formula is referencing) are not very helpful and require us to manually re-create your set-up for testing. Please post your sample data using the XL2BB add-in:
www.mrexcel.com

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.
www.mrexcel.com www.mrexcel.com
Click to expand...
OK I cleaned all the blank columns out and it works Awesomeeeeeeeeeeeeeeeeeeeeeeeeeeee
 

Attachments

  • It Works.jpg
    It Works.jpg
    194.6 KB · Views: 9
Upvote 0
Hi, this part
Excel Formula: 
=BYROW(UNIQUE(A1:C1)....
should refer to all the rows - i.e. for your sample
Excel Formula: 
=BYROW(UNIQUE(A1:C17)....
- the results will then "spill" down without gaps and without needing to copy the formula down. If you want a formula that you copy down that has the gaps then a different approach would be more applicable.

www.mrexcel.com

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.
www.mrexcel.com www.mrexcel.com
 
Upvote 0
FormR said:
Hi, this part
Excel Formula: 
=BYROW(UNIQUE(A1:C1)....
should refer to all the rows - i.e. for your sample
Excel Formula: 
=BYROW(UNIQUE(A1:C17)....
- the results will then "spill" down without gaps and without needing to copy the formula down. If you want a formula that you copy down that has the gaps then a different approach would be more applicable.

https://www.mrexcel.com/board/help/...//www.mrexcel.com/board/help/xl2bb/ [/QUOTE]
My results come out like this =
1722011275524.png
using your code = =IF($O$1>=99,('M-Mix-Dbls-3'!G27), FALSE)
Click to expand...
Everything is working Great but can I switch the code around to give this result? = 800 = Willie Parawan 28a

Here is the CODE currently being used you created which is AWESOME :)
=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
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.
 
Upvote 0
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
 
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
697
willow1985
willow1985
S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
91
HighAndWilder
H
D
  • Question Question
Duplicate cells not formatting problem.
Replies
6
Views
269
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
389
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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