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.)
Hi
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 .
Malkal
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:
=IF(B2=B1,A1,A1+1)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.