search cells for data and increment numbering

[simonp91]

(specifically looking for something to total the number of entries of particular text, and add 1 to gain a new number id for another entry of that type, on enter-key-hit)

Hi,
I have a basic table for listing code names, with 2 columns. Column B - name code (string, e.g. cell B1 is DOG, cell B2 is CAT, B3 is DOG, B4 is RAB, B5 is CAT, and B6 is DOG). Column C is where the id number goes for each entry in column B. So row 2 would read (B2)DOG (C2)001, and respectively, row 3 would be CAT 001, row 4 would be DOG 002, row 5 would be RAB 001, row 6 would be CAT 002, and row 7 would be DOG 003 :

A B C (automatically generated numbering)
DOG 001
CAT 001
DOG 002
RAB 001
CAT 002
DOG 003

What I want to be able to do is to enter the code (CAT/DOG/RAB) into any cell in column B and the numbering in column C to be automatically generated when I hit enter for any entry in column B. Post-sorting is not an option. It needs to be auto, and on entry of the text part. And if I add another entry in half way up the chart, it will automatically adjust the numbering...so if I have DOG 001, DOG 002 and DOG 003 already, but I add a DOG in after DOG 001, then the new one becomes DOG 002, the old DOG 002 becomes DOG 003, etc etc. I may have thousands of entries, so its important that its auto. I also need a count-up for each type of entry in columnB on a second excel sheet. So say, the last DOG entry is DOG 014, then on sheet 2, the cell to the right of the one that says "Number of DOGs" says 14. I know this is better suited to using Access, but for compatibilities sake with other things, it would be fantastic to get this working in excel.

I have gone through as much of the VBA tutorials I can find as I can in what limited spare time I have, but I'm just not getting any real answers. Can someone please help. I would be hugely grateful.

[StephenR]

What about this formula in C1 and down?

=COUNTIF($B$1:B1,B1)

For the second sheet, a pivot table sounds like it would do the job.

Or to automate it, in the sheet module:

Please Login or Register  to view this content.

[MickG]

Hi, Another Option:-
NB:- This is a Worksheet Change Event Code.
Item Count on sheet (2)

Please Login or Register  to view this content.

Regards Mick

[simonp91]

WOW!!! that was REALLY fast. I cannot thank you guys enough, that is a massive massive help.