Return Frequently occuring Rows, based on COUNT

[Brooks]

Hi:

I receive spreadsheets each quarter that identify customers who pay
invoices late. Every quarter, I receive a new worksheet. I have
copied about 16 worksheets (4 years total) into one master worksheet.
What I am interested in doing is asking Excel to show me customers who
appear over a certain number of times, say 4 or more out of the 16
worksheets. (I.e., there are 4 or more rows within the worksheet that
have the identical entry in the field in question) The field in
question could be either a customer number, which is 5 digit number
that could be treated as text or a number, or I could use the mailing
address field, or last name field, etc.. Regardless, is there a way I
can ask excel to look at all records/rows, identify which field has 4
or more identical entries, and then display those records. The max
possible would 16 occurances, if they were late every quarter. Let me
know if I am not being clear enough in my request.

Thank you.

 

[Pete_UK]

Assuming that you have all this data in one sheet called "combined",
then insert a new sheet, highlight the customer number field, click
<copy> and then paste the column into the new sheet in column A. You
need to have a header row, so insert one if necessary and call the
column Cust_ID.

Highlight all the data including the header and click on Data | Filter
| Advanced Filter and in the pop-up panel check the data range (should
be the same as you have highlighted) and click Unique Records Only and
Different Location - specify $C$1 as the location. When you click OK
you will have a list of unique Customer IDs in column C, and you could
now delete columns A and B to put this in column A.

Then you can introduce this formula in B2:

=COUNTIF(combined!A2:A1000,A2)

which assumes that your customer numbers in the combined sheet occupy
cells A2 to A1000 - adjust as necessary. You can then copy this
formula down column B for as many entries as you have in column A. By
sorting A and B in descending order of column B, you will then have a
league table of bad payers - you can always use VLOOKUP in columns C
onwards to return the name and address details of the customer from
the combined sheet.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, you will need to make the formula:

=COUNTIF(combined!A$2:A$1000,A2)

Pete

 

[Brooks]

Pete:

I will try this tomorrow at work and let you know how it works.

What does the advanced filter function do for me? It seems like I
will be eliminating any instance where the customer has several
entries. Do I really want to do this, as that is what I am trying to
count.

Thanks,
Brooks

 

[Pete_UK]

The advanced filter will give you a unique list from the combined
sheet and the formula will count them, so you will end up with
something like this after sorting:

Cust_ID Number
01723 6
00457 5
00046 3
01945 3
01321 2

etc, with perhaps the names and addresses in columns C onwards. You
only need a single entry in this second sheet for each late payer.

Pete