number all entries for each letter that apply to the same column
Hi all,
I have an automated Excel "front" datasheet for a database which fills itself out based on a specified unique ID that has to be typed into it. This unique ID is a composite of the first letter of Categories (listed as column titles on a separate spreadsheet) + their respective numbers, and first letter + assigned number of Entries, AS they are associated with each Category. *The short of it:* I have a matrix consisting of the list of Entries (in the first column of the spreadsheet), alphabetically ranged, and the categories that apply to these entries are listed in columns. Each time a Category is associated with an Entry, there's an "X" at the intersection. Looks like this: Category 1 Category 2 Category 3 Entry 1 X Entry 2 X X X Entry 3 X X (So, the composite unique ID looks like this: (First Letter Of Category 1)1.(First Letter Of Entry 2)1), like I1.A3) I need to automate the creation of this unique composite ID by numbering each entry like so: €śthis is the THIRD time an Entry starting with €śA€ť has been associated with this particular Category€ť (so, its part in the ID will look like €ś.A3.€ť) and €śthis is the FIRST TIME an Entry starting with a €śB€ť has been associated with the same Category€ť (€ś.B1.€ť) *The long of it:* I need to look at each column (category) separately and to number each entry starting with the same letter, ONLY FOR THAT column/category. The kinky part is this: For each individual column, I have to number each intersection of entry and category (the "X"), but to restart the numbering for each letter of the alphabet as I'm moving down the list of entries. So the numbering for, let's say, Category 2 has to go like this: for the FIRST time an entry starting with an A is associated with Category B (i.e., there's an "X" at the entry-category intersection) it will be numbered 1; for the second time that there is an entry starting with A in the same category, it has to be marked 2. Next, if there's an entry starting with a B in the same column, we have to restart the numbering again (i.e., for the first time there's an entry/line starting with a B in that column, we assign it number 1, as in B1 for Category 2; if there is a next B entry in the same column, it gets number 2. How do I do that? Would greatly appreciate your help! |
All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com