Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

autonumbering the same number for duplicated item



I have   many  duplicates items  in  column B    should  incerement  in  column A  ,  but  when  incerement  should  take  the  same  number   for   each duplicates item  , I  don't want to  affect  autonumbering  when  filter  or  delete rows . should  keep   the  numbers as  in  first  case when autonumbering  .

I  put  the  numbers  in  column A  for  each  duplicates item in column B .

I  no  know  if  it's possible  doe  that  by  formula.

formula  or  vba  doesn't  make  difference to  me .



Selected Answer


In the attached file, I put a zero in cell A1 (but it isn't visible since the font colour matches the cell fill) then the formula in A2 is:


and that is copied down column A

This means the number repeats but increases by 1 when the value in column B changes.

You can use Autofilter on that column successfully but an error will occur if you delete a row (for example, since the A1 referenced cell will no longer exist).

If that is a possibility, you can use the OFFSET function instead of absolute addressing. This produces a longer formula but the values in column A are not affected when a row is deleted. Here's the formula used in A2 (and copied down) in the second file attached below:

=IF(B2=OFFSET(B2,-1,0), OFFSET(A2,-1,0),OFFSET(A2,-1,0)+1)

In other rows, the bold bits will be changed but the offset amounts remain unaltered.

Hope this is what you need.



thanks ,
  but  I  don't  want  affecting   when  delete or  filter row   as  I  said.
I  tested  when  delete  the  number  in  column A  will  show error.
Malkal (rep: 6) Oct 5, '22 at 3:38 am
I didn't understand the wording of your question, sorry. Might look later. 

You should expect errors when formula is used and you delete rows! 
John_Ru (rep: 4282) Oct 5, '22 at 4:30 am
when I  delete row  should  not  affect  autonumbering   in  column A  if  you  don't  have  any  formula  in  column A  and  write  manually  numbers  in  column A    and  delete  one  of  the  rows  , it  will  keep  numbers  for  the  others  rows without  any change  . this  is  what  I  look  for  it , I  no  know  if  the  formula  can  do  it.
Malkal (rep: 6) Oct 5, '22 at 5:56 am
Maklal- please see my revised Answer and second file.
John_Ru (rep: 4282) Oct 5, '22 at 7:15 am
perfect !
many  thanks  John ! 
Malkal (rep: 6) Oct 5, '22 at 7:41 am
Great! Thanks for selecting my Answer, Maklal.

I just corrected a couple of typos in my Answer.
John_Ru (rep: 4282) Oct 5, '22 at 7:44 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login