CODEWITHSUNDEEP
excel
MCP_infiltrator
MCP_infiltrator

Reputation: 4179

Row number and partition in Excel

I have data in excel such as:

ID        | Fee
123456789 | 100
987654321 | 100
987654321 | 75
987654321 | 50

I need to calculate a fee reduction for the items that are not the max price. The spreadsheet is sorted by ID, then Fee in the fashion needed. What I do not know how to do is use a similar row_number() over(partition by) in excel that I would normally do in SQL

Desired output would be

ID        | Fee   | rn
123456789 | 100   | 1
987654321 | 100   | 1
987654321 | 75    | 2
987654321 | 50    | 3

Upvotes: 6

Views: 49158

Answers (5)

watering bridges
watering bridges

Reputation: 11

For anyone who needs more performance, you can adjust the above query slightly to work better on large data sets.

Step one is count the maximum number of entries of a specific value in the column you wish to partition by. I did this with a pivot table.

For example, '987654321' shows up 3 times. With that information you can now limit the number of rows which the countif statement is applied to.

Then sort your data on column A. And use the below formula.

=COUNTIF(A2:INDIRECT("A"&ROW(A2)+3),A2)

This creates a rolling window for your countif function to apply too.

Upvotes: 1

zodiac3785
zodiac3785

Reputation: 11

  1. Arrange everything in column A (in any order).
  2. In B1 type this : =IF(A1=A2, (B2+1),1), extent this over the entire column B.

Upvotes: 1

Filipe Freitas
Filipe Freitas

Reputation: 61

you can use =COUNTIF($A$2:A2,A2); note that only the first $A$2 will not move.

Upvotes: 6

janek_kos
janek_kos

Reputation: 119

This formula will do the job:

=COUNTIF($A$2:INDIRECT("A"&ROW(A2)),A2)

There is no need for sorting the data and you won't fall out of the range.

ROW() is used to make the range dynamic, so if we drag the formula down, ROW() will always give us ending point:

screen of an excel table

Upvotes: 9

JNevill
JNevill

Reputation: 50034

There's probably a more complex formula one could just throw at the data without having to monkey with the data, but I think this may be an easier solution:

  1. Sort the data by ID (smallest to largest) and Fee (Smallest to largest) enter image description here

  2. Use formula =Countif(A2:A5, A2) to count how many times the same id appears in the data for the current cell and every cell below it. Copying this down to fill out the missing column. enter image description here

Upvotes: 4

Related Questions