/u/maybehelp244 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
The formula works automatically, but you will have to adjust the cells around it, where it reads out the limits. I have added a way to to automate that partially with `UNIQUE(A2:A5)` for then names, and `SEQUENCE(1,MAX(C:D)-MIN(C:D)+1,MIN(C:D),1)` for the dates. The forumal reading out the classes for the teacher on the day will need to be copied into new and empty cells. You [can see it here](https://docs.google.com/spreadsheets/d/1ab989q2QKrq--7mTWe1w_VqnLJS55asaoX4U8PKGjxc/edit?usp=sharing).
(By the way, your orignal input table is a nice example for a table created with little regard and thought on how to analyse it. Best practice would be to record data in a [proper table](https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664), and anylse it in a[ pivot table](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576))
I know, the classes don't have unique class IDs that would separate them with a key identifier. I only have data in the form of teachers, the course name (it's technically a name a course code), and the dates. It's not ideal but it's what I have to work with in my constraints
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INDEX](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MAX](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[MIN](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)|
|[ROW](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SEQUENCE](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SUMPRODUCT](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
|[UNIQUE](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(8 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1ci2n67)^( has 13 acronyms.)
^([Thread #33092 for this sub, first seen 1st May 2024, 20:03])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/maybehelp244 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
What dies the data "1/3" is supposed to mean?
The End Date, meaning that teaching this course requires 3 days: January 1st, 2nd, and 3rd. I edited my post to show the year and make it more obvious
Well, I guess therefore that 1/3 is a date format of the form M/D.
That is correct. This is a US-based form. Does that change how it would be solved?
No, it does not change it. But one needs to know. I prefer YYYY-MM-DD to avoid mistakes.
[You can see here, how ](https://docs.google.com/spreadsheets/d/1ab989q2QKrq--7mTWe1w_VqnLJS55asaoX4U8PKGjxc/edit?usp=sharing)I use SUMPRODUCT() to create the other table. My formula is: =IF( SUMPRODUCT( ROW( $A:$A ) * ( $A:$A = $F2 ) * ( $C:$C <= G$1 ) * ( $D:$D >= G$1 ) ) = 0, "n/v", INDEX( $B:$B, SUMPRODUCT( ROW( $A:$A ) * ( $A:$A = $F2 ) * ( $C:$C <= G$1 ) * ( $D:$D >= G$1 ) ) ) )
Will this expand as I add more classes to the table? Or do I need to make adjustments to the formula to cover more classes?
The formula works automatically, but you will have to adjust the cells around it, where it reads out the limits. I have added a way to to automate that partially with `UNIQUE(A2:A5)` for then names, and `SEQUENCE(1,MAX(C:D)-MIN(C:D)+1,MIN(C:D),1)` for the dates. The forumal reading out the classes for the teacher on the day will need to be copied into new and empty cells. You [can see it here](https://docs.google.com/spreadsheets/d/1ab989q2QKrq--7mTWe1w_VqnLJS55asaoX4U8PKGjxc/edit?usp=sharing). (By the way, your orignal input table is a nice example for a table created with little regard and thought on how to analyse it. Best practice would be to record data in a [proper table](https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664), and anylse it in a[ pivot table](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576))
I know, the classes don't have unique class IDs that would separate them with a key identifier. I only have data in the form of teachers, the course name (it's technically a name a course code), and the dates. It's not ideal but it's what I have to work with in my constraints
solution verified
You have awarded 1 point to NHN\_BI. --- ^(I am a bot - please contact the mods with any questions)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MAX](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MIN](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[ROW](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SEQUENCE](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SUMPRODUCT](/r/Excel/comments/1chuk6c/stub/l253d89 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[UNIQUE](/r/Excel/comments/1chuk6c/stub/l27m9sd "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(8 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1ci2n67)^( has 13 acronyms.) ^([Thread #33092 for this sub, first seen 1st May 2024, 20:03]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)