Create an index keeping original index reference and appending new where necessary

[BRISBANEBOB]

I am trying to index a series of arrays in which some products have index numbers and some not. Where index numbers exist, I need to keep these and just give the unindexed products the next reference number. The attached workbook has some examples which might explain it a bit better.

Grateful any help - I suspect it is an array-type solution and I am trying to avoid VBA.

[FlameRetired]

Do your Indexing dumps ever start with 3 or always with a 1 or 2 ?

[FlameRetired]

Array-enter in D8; fill to D10

=IF(C8,C8,SMALL(IF(ISNA(MATCH(ROW($1:$3),$C$8:$C$10,0)),ROW($1:$3)),COUNTIF($C$8:C8,"")))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE)


D14 to D16

=IF(C14,C14,SMALL(IF(ISNA(MATCH(ROW($1:$3),$C$14:$C$16,0)),ROW($1:$3)),COUNTIF($C$14:C14,"")))



D20 to D22

=IF(C20,C20,SMALL(IF(ISNA(MATCH(ROW($1:$3),$C$20:$C$22,0)),ROW($1:$3)),COUNTIF($C$20:C20,"")))

Row\Col	A	B	C	D	E
5		Data Dump
6
7	Example 1		Index	This one	Final
8		Prod A	2	2	2
9		Prod B	1	1	1
10		Prod C		3	3
11
12
13	Example 2		Index		Final
14		Prod G	2	2	2
15		Prod B		1	1
16		Prod A		3	3
17
18
19	Example 3		Index		Final
20		Prod B	1	1	1
21		Prod K	2	2	2
22		Prod D		3	3