Enumerating unique text entries (Revision 2)

elk2

New Member
Joined
Nov 16, 2005
Messages
1
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
JKLMNO
11ApplesApples1
21ApplesPears2
32PearsOranges3
43OrangesLemons4
54LemonsGrapes5
62Pears
75Grapes
83Oranges
Sheet1
 
Upvote 0
What follows does not presuppose a list of distinct items in order to number them...
Book4
ABCD
1Unique codeItem
21Apples
31Apples
42Pears
53Oranges
64Lemons
72Pears
85Grapes
93Oranges
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),"")
 
Upvote 0

Forum statistics

Threads
1,212,094
Messages
6,105,925
Members
447,983
Latest member
tarcisioal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top