Filling missing IDs in column

Jacob_

New Member
Joined
Apr 13, 2019
Messages
1
Hi,
can anyone help me with this?

I have in column IDs they look like this:
A0002
A0010
A0003
Not sorted in any way and some of them are missing. I need to find the blank cells and fill it with what's missing.
It has to be done using vba.
Thanks.
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,669
Hi,

With your data in range A1:A3 ... you can test in cell B1 The following array formula :

Code:
=IFERROR("A000"&SMALL(IF(ISNA(MATCH(ROW(INDIRECT(MIN(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))&":"&MAX(VALUE(SUBSTITUTE($A$1:$A$3,"A",""))))),VALUE(SUBSTITUTE($A$1:$A$3,"A","")),0)),ROW(INDIRECT(MIN(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))&":"&MAX(VALUE(SUBSTITUTE($A$1:$A$3,"A","")))))),ROWS($1:1)),"")
Hope this will help
 

Forum statistics

Threads
1,078,365
Messages
5,339,765
Members
399,322
Latest member
jpalleyne

Some videos you may like

This Week's Hot Topics

Top