Count non unique values

[batchjb69]

Hi,

The attached is a subset of a large spreadsheet the sheet has two columns. I want to count which Counties have more than 1 city in the spreadsheet and ideally show this in a message box with the number of times it appears

So it would show

Essex Chelmsford 9
Southend 1


but would skip Norfolk and go to Kent
etc etc

Cities.xlsm

James

[shg]

Please Login or Register  to view this content.

Requires a reference to Microsoft Scripting Runtime.

[batchjb69]

Thanks for your help

Is it possible to have the county in the result as well. So it would look something like this

Essex - Chelmsford (9)
Essex - Southend (1)

[Leith Ross]

Hello batchjb69,

The attached workbook will display as requested. The following macro has been added and attached to a button on the worksheet.

A Message Box can only display a maximum of 1024 characters, including carriage return/ line feed combinations. If you have a large number of counties and cities then creating a UserForm to display them is a better approach.

Please Login or Register  to view this content.

[batchjb69]

Cities.xlsm

Thanks for your help

OK so Ive put the control in a user form but the result is displayed in a message box still. How to I use the user form to display the result as I believe the result could be greater than 1024 characters

[TMS]

I've added a TextBox to your User Form and output the Text to it.

Adjust as required ...

Regards, TMS

[batchjb69]

Thanks very much only trouble is the macro doesn't work properly.
ESSEX should have anther line Southend (1)

any ideas why?


ESSEX - Chelmsford (9)
NORFOLK - Norwich (14)
KENT - Canterbury (8)
KENT - Maidstone (2)
CORNWALL - Truro (9)
CORNWALL - Plymouth (2)
DEVON - Exeter (7)

[Leith Ross]

Hello batchjb69,

I have updated the attached workbook to display a UserForm for you.

[batchjb69]

Cool cheers for your help only trouble is the macro doesn't quite work properly as per comment in 11:06 of the thread

[Leith Ross]

Hello batchjb69,

Sorry, I misunderstood your original post.This will return the count of all cities in all counties.

[TMS]

The text box on the user form displayed the same information you had in the MsgBox. I didn't change the logic, only the presentation.

Regards, TMS

[batchjb69]

Cheers but I wanted it to just Display Counties with two or more Cities. So Norfolk would not be displaced in this instance. Is it possible to do this?

[Trebor76]

Hi batchjb69,

Try this:

Please Login or Register  to view this content.

HTH

Robert

[batchjb69]

City Count.xlsm

Thanks for all your help this is the best solution the trouble is I need it to ignor citys with one county. SO of the current results Norfolk,Devon & London would not display in the text box



ESSEX - Chelmsford (9)
ESSEX - Southend (1)
NORFOLK - Norwich (14)
KENT - Canterbury (8)
KENT - Maidstone (2)
KENT - Whitstable (1)
CORNWALL - Truro (9)
CORNWALL - Plymouth (2)
DEVON - Exeter (7)
LONDON - London (1)

[shg]

You've now changed this requirement a half-dozen times -- duplicates cities, counties and their duplicate cities, all cities in all counties, only counties with two or more duplicated cities, ...

Before people lose their sense of humor, you might take the time to decide exactly what you want, and then describe it very clearly.

[batchjb69]

Apologies what I want is for the macro to return All counties in Column A that have more than 1 city in column B so in the current spread sheet. So in the example Norfolk, Devon & London would not show in the result as they only have 1 city attached to them.

[batchjb69]

Apologies what I want is the Macro to display all counties in column A that have more than 1 city in Column B.

So in the example Norfolk,Devon & London would not display in the text box

[WHER]

Based on the previously posted attachments: