Enumerating unique text entries (Revision 2)

[elk2]

This query is a development of a previously answered question which develops the theme of enumeration of unique text entries.

The original question:-
I need to enumerate unique text entries entered in column B by entering a function (or some other mechanism) in column A. This requires that the enumeration of each text entry takes place at the time of when the text is entered sequentially as it is entered in the column B cells- precluding the use of a library with predesignated numbers thus:-
D H
1 Apples (Apples is numbered "1" being the first entry)
1 Apples (Apples repeated and designated "1")
2 Pears (Pears is numbered "2" being the second unique entry)
3 Oranges (Oranges is numbered "3" being the third unique entry)
4 Lemons (Lemons is numbered "4" being the forth unique entry)
2 Pears (Pears is numbered "2" already designated)
5 Grapes
3 Oranges

The following formula, copied into column D, provided a complete answer to this problem:-

IF(H35<>"",IF(ISNUMBER(MATCH(H35,$H$14:H34,0)),INDEX($D$14:D34,MATCH(H35,$H$14:H34,0)),MAX($D$14:D34)+1),"")


However, the application for which the formula is intended has section breaks. These breaks require the numbering sequence to revert to 1, and all items are then enumerated with reference to their positon within that section only, thus:-

Col D H
SECTION A
15 1 Apples
16 1 Apples
17 2 Pears
18 1 Apples
19 3 Lemons
20
21 SECTION B
22
23 1 Pears
24 2 Grapes
25 3 Oranges
26 1 Pears
27 4 Apples

This has impications to the absolute references "$H$14" and "$D$14" as these need to accommodate the sections as indicated. The breaks between the sections will always be three rows.

Thanks for bearing with me

Elk2

 

[NBVC]

Possibly, one of the easiest ways is to add a separate table, with predefined numbers, then use a vlookup to insert the numbers as appropriate:

Formula in J1: =VLOOKUP(K1,$N$1:$O$5,2,0) copied down.

Book1
	J	K	L	M	N	O
1	1	Apples			Apples	1
2	1	Apples			Pears	2
3	2	Pears			Oranges	3
4	3	Oranges			Lemons	4
5	4	Lemons			Grapes	5
6	2	Pears
7	5	Grapes
8	3	Oranges
Sheet1

 

[Richard Schollar]

Or if in J as before:

=MATCH(K1,{"apples","pears","oranges","Lemons","Grapes"},0)

and copy down.

Regards

Richard

 

[Aladin Akyurek]

What follows does not presuppose a list of distinct items in order to number them...

Book4
	A	B	C	D
1	Unique code	Item
2	1	Apples
3	1	Apples
4	2	Pears
5	3	Oranges
6	4	Lemons
7	2	Pears
8	5	Grapes
9	3	Oranges
Sheet1

A2, copied down:

=IF(B2<>"",IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),INDEX($A$1:A1,MATCH(B2,$B$1:B1,0)),MAX($A$1:A1)+1),"")