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]

.mis-read

 

[Comfy]

A simple Data Validation Rule can do this:

Highlight your Date column starting with the first cell. My formula assumes this starts in C2 and your Region is in Column B.

=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)<=1

 

[WiseJoe]

I stated in my first post:

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.

And...

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.

Your rule works, yes, but I already have this. I need help with point #1, which is fixing this formula:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; border: 0px; vertical-align: baseline; 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></pre>

 

[Comfy]

Can I clarify your setup?

You have two tables (Table & Entry)?

Which is the table you posted and what is contained in the other?

 

[WiseJoe]

Sorry! There is only one table.

The idea is that as the user is entering data week after week, we want to prevent duplicate value entries for the same date in the same region. Think of a region as an individual project, one that shouldn't have an update more than once a day.

That's why I want to dynamically generate a unique list of dates (that have been entered) for a given region (which is a subset of a city), and then of course if my conditional formatting formula (already in place) checks the newly entered date against this dynamic list to ensure it isn't on there, else flag (this part works, but I my dynamic list isn't, so it generates the wrong/mixed/or duplicate list).

 

[Comfy]

I'm sorry Joe, I can't understand why it needs to be so complicated.

You can prevent duplicate values being entered (by region and date) using the formula in Post 3, this will mean you won't need conditional formatting to highlight duplicates.

But, if you don't want to use data validation you can use a simple Countifs formula in your conditional formatting to highlight duplicates.

 

[WiseJoe]

The date entry references a list of week-ending dates. Not sure how I can prevent entry of duplicate values while at the same time provide a list of dates....

You might be right on the complexity factor though. I will give it a shot on the conditional formatting end and will report back.

Update: Yes, the simple countifs worked. Not sure why my head was buried in complexity. Thanks, Comfy!

 

[Comfy]

Update: Yes, the simple countifs worked. Not sure why my head was buried in complexity.

Glad to know it's working.

 

[WiseJoe]

In case anyone needs to know the exact formula semantics used for this situation, we input this formula as a Conditional Formatting rule:

=COUNTIFS(INDIRECT("Table[Date]"),INDIRECT("Table[@Date]"),INDIRECT("Table[Region]"),INDIRECT("Table[@Date]"))>1

INDIRECT("") is required when referring to Table columns within Conditional Formatting rules.


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.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; border: 0px; vertical-align: baseline; 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(List of Dates!$A$1,0,0,1,1),MATCH(0,COUNTIF(Table[@Date],Date[Region]),0)-1,0,SUM(COUNTIFS(Table[Region],Date[@Region])))</code></pre>