Hi all,

I have a list of 400,000 lines like this:

But I need grouped by amount of 10 and count the number of clients grouped, something like this:

I hope the pictures explain better my problem.

Thanks for your support

Carlos

• Moved by Fei XueMicrosoft contingent staff Friday, April 10, 2015 5:13 AM

Hi Carlos,

first you should create a bucket-table for your ranges: From-To-Range (no overlaps)

Then you add an additional column to your list called "Group": VLOOKUP([@Amount],G:I,3,TRUE)

Where G:I is the reference to your bucket-table.

The important aspect of this formula is the last element set to TRUE: This will actually allocate the correct Group to each of your line.

The rest is Pivot: With "Account" & "Group" in Rows, "Amount" & "Client ID" into values, but Client ID with a Count Aggregation instead of standard-Sum.