Sequential Numbering of Duplicates

[Diddy]

Hi everyone,

I’ve got a sheet with the following data.

A = Branch Number
C = Category (text from a data validation list)
D = Category Number (from a lookup) can be 1-14
H = Number in the Category

I was going to summarise this using Sumproduct to count the number in each
category in each branch.

However, what I didn’t realise was that there could be more than one lot of
each category and they need to be kept as the separate counts.

So I thought I would number the duplicates but I need to start the re-number
for each branch.

What I need is a unique number for CategoryA(1), CategoryA(2) and
CategoryA(3) for each branch.

Hope someone will have some idea of what I need to do ïŠ based on this pretty
scrappy description (body here – brain catching up – it is Monday!)
Cheers
Diddy

 

[Patrick Molloy]

why not just create the unique ID concatenating the first three fields
=A1 & B1 & C1

 

[Joel]

I would sort the data by the 3 fields. Then write a very simple macro to add
the the new count and make each branch start at 1.

Or just give an ID number to each line starting at 1 and don't worry if each
branch starts at 1. As long as the ID is different for each line you can
tell can distinquish between each line.

 

[Diddy]

Thank you
I've gone with the concatenate idea and added another column and formula
=A4&D4&"#"&IF(COUNTIF($D$45,D5)>1,COUNTIF($D$45,D5)-1,"") copied down
from E5 onwards and with =A4&D4&"#" in E4

Cheers
Diddy

 

[Diddy]

Thanks Joel,

Went for simple in the end and did a formula to ID and number duplicates and
to concatenate with Branch no. That'll give me something to work with.

Cheers
Diddy

 

[Bernd P]

Hello Diddy,

I suggest to use my UDF Pfreq:
http://sulprobil.com/html/listfreq.html

Regards,
Bernd