Multiple Instances of a Name

[HangMan]

I have a large dataset with a company name in Column A and a Town in Column B, e.g.

ABC Company London
ABC Company London
XYX Company Swindon
STJ Company Bristol
STJ Company Bristol
STJ Company Bristol
DEF Company London
DEF Company London
DEF Company London

and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:

ABC Company London London
ABC Company London London-1
XYX Company Swindon Swindon
STJ Company Bristol Bristol
STJ Company Bristol Bristol-1
STJ Company Bristol Bristol-2
DEF Company London London
DEF Company London London-1
DEF Company London London-2

I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?

Many thanks

[alansidman]

Using MS Query, I was able to create a query in Excel using this SQL statement to get the desired results

Please Login or Register  to view this content.

You would have to change your path and field names to match what you have in your spreadsheet. Here is a tutorial on using MS Query

http://www.exceluser.com/explore/msquery1_1.htm

Alan

[TMS]

Maybe:

C2: =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)-1=0,B2,B2&"-"&COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)-1) ... dragged down


Regards, TMS

[HangMan]

Hi Alan and TMShucks

I don't have Microsoft Query and really want to do this within Excel...

TMShucks' solution works perfectly... Many thanks, that is brilliant!

[TMS]

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

Thanks for the rep.

[alansidman]

Hangman--all copies of Excel carry MS Query built in. If you read the tutorial you would know this. It is a very powerful tool that supplements Excel and should not be dismissed lightly. Good luck with your project.

Alan