Creating Dynamic, Unique Arrays Dependent on Multiple Criteria or Subset of Data

[WiseJoe]

Excel 2010.

I have a Table structured in this manner:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;"></code>

City	Region	Date	Value
ABC	123	JAN1	10%
DEF	987	FEB1	7%

<tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">(arbitrary values, of course)</code>​

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">
</code>The table is fed by user-entry, but there is an important rule:

1. No region can have more than one of the same date, even if the values are different.

E.g., Region 123 can't have two values, whether different or the same, for January 1st.


To control this, I utilize two formulas:

1. A Dynamic Named Range that creates a list of unique dates.
2. A conditional formatting rule that flags entries that have duplicate dates.

I am having trouble with #1. #2 I have tested to work fine, and I know it works.

My attempt was to create this named range, simply called UniqueDates.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">=OFFSET(OFFSET(Table!$C$9,0,0,1,1),MATCH(0,COUNTIF(Table[@Region],Entry[Region]),0)-1,0,SUM(COUNTIFS(Table[Region],Entry[@Region])))</code>

I have experimented with trying different criteria combinations, but cannot seem to create a list of unique dates that have been entered thus far. Instead, I get either the wrong dates, or non-distinct results.

It is important to note that the result of this formula is not stored in Data Validation. I use the named range directly in the conditional formatting rule.

 

[Comfy]

Comfy: Out of curiousity, can duplicate entries be prevented while at the same providing a list of dates one can choose from? Perhaps it is in this case that we would use a modified form of the above offset formula to dynamically generate a list of dates that have not been chosen.

Quite possibly, but that would be beyond me I'm afraid.

You would also need to create another Table that contained all the dates, which is just more data that you will need to maintain.

I'll have a little Google today as I'm quite curious but I don't imagine I'll find an answer for you.

/Comfy