Sequential Numbering of Duplicates

  • Thread starter Thread starter Diddy
  • Start date Start date
D

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
 
why not just create the unique ID concatenating the first three fields
=A1 & B1 & C1
 
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.
 
Thank you
I've gone with the concatenate idea and added another column and formula
=A4&D4&"#"&IF(COUNTIF($D$4:D5,D5)>1,COUNTIF($D$4:D5,D5)-1,"") copied down
from E5 onwards and with =A4&D4&"#" in E4

Cheers
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
 
Back
Top