Count unique numbers

el c

New Member
Joined
Mar 24, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am trying to count the numbers from the first column (ItemCode). Some of those numbers are duplicated so the duplicated ones that are the same need to have the same number in the column "Numbers". Meaning for exapmle 31112802 which is duplicated, has a number of 2 and the next 31112802 that appers on the next rows needs to have the number 2. How can i do this without changing the range of the rows?
Inventories2.xlsx
ABC
1Item CodeNumbers
225708011 1
331112802Duplicate2
425712801 3
525708010 4
625715502 5
725712803Duplicate6
825712103Duplicate7
931112142Duplicate8
1029812022Duplicate9
111030083Duplicate10
1231112144Duplicate11
1331112343Duplicate12
1425712104Duplicate13
1541815001 14
1631112141Duplicate15
1731112344 16
1831112143Duplicate17
1931112342 18
2029812023Duplicate19
2131112341Duplicate20
2225715005Duplicate21
2323636002 22
242801101 23
252402029 24
2641815005 25
2741815002 26
2841815150 27
2916423003Duplicate28
3015711223 29
3115711027 30
3225711522Duplicate31
3315711011 32
3425711020Duplicate33
3525712071 34
3625704003Duplicate35
3719104005 36
3823636001Duplicate37
3916823001 38
401030094Duplicate39
411030094Duplicate39
4215505080Duplicate40
4315505081 41
447102016 42
4525711050 43
46102100 44
4737623004Duplicate45
4836766050 46
4941815030 47
5045316140Duplicate48
5125710831Duplicate49
5225710532 50
5325710831Duplicate51
5425710632Duplicate52
551030017 53
5625715001 54
5732416003 55
5841815032Duplicate56
5946605001Duplicate57
6023636001Duplicate58
611414601 59
621414712 60
6339808002Duplicate61
6425730001 62
6525711521Duplicate63
6633130010Duplicate64
677923001 65
681006006 66
6925715011 67
7025715002Duplicate68
7125715005Duplicate69
Φύλλο1
Cell Formulas
RangeFormula
B2:B71B2=IF(COUNTIF($A$2:$A$400, $A2)>1, "Duplicate", "")
C3:C71C3=IF(A3=A2,C2,C2+1)
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
Like this?

20 03 28.xlsm
AB
1Item CodeNumbers
2141
362
4183
562
694
794
8175
9183
10156
11141
12183
1362
1417
15183
16175
1762
1862
19128
2029
2162
22128
23410
24175
25141
2662
2762
2894
2994
302011
31512
321613
Numbers
Cell Formulas
RangeFormula
B2:B32B2=IFERROR(VLOOKUP(A2,A$1:B1,2,0),MAX(B$1:B1)+1)
 

el c

New Member
Joined
Mar 24, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome. Thanks for the follow-up. :)
Do you know perhaps how i can add those numbers next to the new itemcode that i have? Like this example:
Inventories2.xlsx
IJKLM
1Item Code NumbersNew itemcode
244415552
35552333
43333555
54441785
67854444
ItemCode to Numbers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff.xlsm
ABCDE
1Item CodeNumbersNew itemcode
244415552
355523333
433335552
544417854
678544441
Master
Cell Formulas
RangeFormula
E2:E6E2=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"",0)
 

el c

New Member
Joined
Mar 24, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff.xlsm
ABCDE
1Item CodeNumbersNew itemcode
244415552
355523333
433335552
544417854
678544441
Master
Cell Formulas
RangeFormula
E2:E6E2=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"",0)
Yes but when i copy paste it to excel it shows me this:
Inventories2.xlsx
ABCDE
1Item CodeNumbersNew itemcode
24441555#NAME?
35552333#NAME?
43333555#NAME?
54441785#NAME?
67854444#NAME?
Sheet1
Cell Formulas
RangeFormula
E2:E6E2=XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"",0)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
In that case how about
=VLOOKUP(D2,$A$2:$B$6,2,0)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,479
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,114,577
Messages
5,548,861
Members
410,881
Latest member
toonces
Top