Generate Unique ID Using Criteria and across multiple sheets

**sinspawn56** · 01-10-2017, 07:55 PM

Hello Folks!

I'll jump right in. I have a training plan and I want to automatically generate class IDs. The IDs are in a specific format, and I have a formula written up to the last item I need which is the sequential order of the class by room. The syntax of the class ID is as follows Location_Room_Subject_yyyy_mm_dd_'unique number'

That unique number where I need the assistance. The number is sequential by the room. So regardless of the Location, Subject, and Date, the number will increase by one for each class as long as it is in the same room. Furthermore, I have two types of classes, we'll call them NH and T and they are located on separate sheets. I need the formula to be able to also look at the classes that are in the same room in both sheets and track the sequentially.

For example. I have the following class on the NH sheet: LOC_ONE_Ivory_2016_10_17_6 that started on 17-Oct. On the T tab I have a class that starts in the same room (ONE) on 30-Oct so it would be the next class and would be called LOC_ONE_Maroon_2016_10_30_7.

I am not the best at using words to describe my issue, so I have attached an example file with the formula as far as I'd gotten with it as well as the desired outcome. I hope it explains things a bit better. But please let me know if I can clarify further.

**spitfireblue** · 01-10-2017, 10:31 PM

Hi,

Not sure if I am missing something, but in your desired outcome for Loc_One it jumps from 6 to 10 at the end!

I think this is what you are after though...

Formula:

Please Login or Register  to view this content.

**sinspawn56** · 01-10-2017, 11:34 PM

Hey spitfireblue, thank you for your reply. The reason why LOC_One jumps from 6 to 10 is because there are three classes on the T sheet that was in LOC_One before what I have labeled as class 10 that I want to take into account. That's where it became tricky for me, as I can't figure out how to also reference the classes that are in Sheet T.

**sinspawn56** · 01-13-2017, 12:53 PM

I just want to add that spitfireblue is very close to what I need, but I need that last bit of formula to also be able to look at another sheet to determine the next number in the sequence.

**JeteMc** · 01-15-2017, 09:54 PM

Perhaps this will get you a little further down the road to a solution. Sheet 1 is filled by formulas that get the values of the tables on the NH and T sheets, then the following array entered formula* in column O sorts those tables by date:

Formula:

Please Login or Register  to view this content.

A derivation of spitfireblue's formula then produces the UID.
Column B of both the NH and T sheets is populated by the array entered formula:

Formula:

Please Login or Register  to view this content.

The numbering of B7 and B8 on the T sheet is different from the expected, which could be remedied by putting the entries in time sensitive order within the dates when the data is entered.
*Array entered formulas are activated by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Generate Unique ID Using Criteria and across multiple sheets

LinkBack

Thread Tools

Rate This Thread

Display

Generate Unique ID Using Criteria and across multiple sheets

Re: Generate Unique ID Using Criteria and across multiple sheets

Re: Generate Unique ID Using Criteria and across multiple sheets

Re: Generate Unique ID Using Criteria and across multiple sheets

Re: Generate Unique ID Using Criteria and across multiple sheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Generate Field with Multiple Criteria

[SOLVED] List unique values corresponding to certain criteria from across multiple sheets

Generate Unique List Meeting Criteria

[SOLVED] Generate a pending list from multiple sheets

Excel Function to Generate list of unique entries from multiple columns

[SOLVED] Count unique values once with multiple criteria across multiple sheets

Excel Generate List from Multiple Sheets

Bookmarks

Bookmarks

Posting Permissions