CODEWITHSUNDEEP
excelexcel-formula
user8452670
user8452670

Reputation: 5

Excel - Group Rows together Sequentially

In Excel, I am working with a dataset of around 2000 rows.

The rows can have multiples of the same account but different charges. (For example, Account 3745 shows up 3 times with charges $1000, $500, and $250)

I want to have a column that will sequentially label the account with numbers 1-3; 1 being the account with the largest charge, 2 being the next largest, and 3 being the lowest.(Example: 1 - $1000, 2 - $500, - 3 - $250)

Then the next grouping of accounts will have it's own sequential numbering 1-x number, doing the same thing as above.

Is there a formula for this or do I have to get fancy somehow?

Thanks!

Upvotes: 0

Views: 230

Answers (3)

P.b
P.b

Reputation: 11653

If you have Office 365 you can make use of SORTBY.

If your data is in A1:B10, where A1:A10 is the Account and B1:B10 is the Charges then use: SORTBY(A1:B10,A1:A10,1,NA:B10,-1)

This will sort your data on Account ascending and on Charges descending.

Upvotes: 0

basic
basic

Reputation: 11968

Try COUNTIFS:

=COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,">"&B2)+1

enter image description here

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6446

you can use countif() for this

if your account data is in column A adding a column with a formula like below and copy to all rows

=countif(A$1:A1,A1)

so on row 2 it would read

=countif(A$1:A2,A2)

Upvotes: 0

Related Questions