Highest No. of Complaints on ID

[prasademany]

Dear All,

Please find the attached file Sample Data.I need to pull the list of ID's based on the highest no of complaints in particular Complaint Category as shown in sheet "Summary"

Customer Complaint No.of Complaints
40145 13
40123 10
43212 09

[mikeTRON]

I would likely create a simple pivot table filtering on each particular complaint, couting the complaints by ID, then sort from low to high. Pulling the data to the summary tab will then be very easy.

[FlameRetired]

I used 5 helper columns for this. They are in columns J:N of the Data sheet.

The base formula for column J is:

Please Login or Register  to view this content.

The formula for column K is:

Please Login or Register  to view this content.

It is a variation on the first one with the added variable. Each formula for the remaining columns will need to have the text "Non-Meter" replaced according to need. In case I am permitted to upload this file (it is quite large) I left AutoFilters in place including the helper columns so you can confirm the results; there are mostly blank cells in the right-most columns.

On the Summary sheet in column C is this formula:

Please Login or Register  to view this content.

This the base formula for columns F, L, I and O. It sorts the results of the helper columns. You will need to edit the cell references for these other columns.

This is the base formula for listing the ID's. This one goes in column B. It is an array-entered** formula:

Please Login or Register  to view this content.

Make necessary changes to the cell references for columns E, H, K and N.

**Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Then copy / fill down the rest of the column.

I will attempt to upload the file.

Edit: There is an error in the helper columns K:N. MarvinP's pivot table alerted me to it. I would see what I could do to remedy it, but the PT looks like a much better approach.....so I probably won't.

[MarvinP]

Is this pivot structure what you are asking for?

[samba_ravi]

as MarvinP said use pivot table other wise
you have to create supporting columns and some array formulas - it may slow your system due to lot of calculations, I think you have huge data (rows)

[prasademany]

Thanks a ton!!!
For your support ...

I have used the Pivot table option given by Marvin P

Thanks once again.