About Match in same Period - That possible or ?..

You can't extend the formula to multiple columns of products like that.
The formula's approach is to look for overlaps in the dates for 1 column of products with 1 product per row.

Maybe a completely different approach will work better for you. If you explain more about your data set (approximate numbers only), I'll try to help you re-organize it in a way that will be less burdensome than listing one product per row.

1. Number of stores?
2. Maximum number of promotions campaigns that could be listed at the same time?
3. Number of products (regardless of whether they are on promotion)?
4. Maximum number of products listed for a single promotion?
5. Is this worksheet being used for any other purpose other than comparing promotions to avoid overlaps? If so, what other functions does it have?
6. Are you currently using any VBA macros?

Hello, JS
did you receive my message ?

Thanks for ALL!

Hi RZ100,

I've been away on holiday for the past few weeks. I'm sorry that I wasn't able to reply to you sooner.

Let's use these assumptions extracted from your PM to me...

Max. # stores: 40
Max. # of products: 400 now (could increase in future).
Max. # products listed for a single promotion? 40
Worksheet used for any other purpose: No
VBA Okay: Yes
Max. # promotions listed at the same time? 100
(You answered 1, but I think you misinterpreted the question.
See if my guess of 100 makes sense based on example below).

If you decide to use VBA, then you could achieve the result of testing for overlaps with almost any layout of the organization.
So the key decision for you will what is the best way to organize your data to make it easy to maintain and read.

This is somewhat a matter of personal style, but some layouts will definately require more effort to maintain.
For example, the layout shown in post #5 of this thread looks like it would be a real pain to work with since you might need to type 40 product numbers in the same cell using linefeed (Alt-Enter) between rows.

If I had to maintain this worksheet, I would probably use this layout organization:



One of the benefits of something like this is you could use conditional formatting or VBA to show you which products were "off limits" for promotion for a given date range before you list them, instead of typing them in a cell and then clicking a button to check.

If you like this layout, I could help you with the VBA or conditional formatting. If you prefer some other layout, please post that and I will try to help with some VBA.

Hey Ali , i have last question
I try to change cell range,but have some problem.
I want to be - 300
I Change all formulas

but In this formula have problem.

{=IF(ROWS($F$2:F2)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F2))),"")}

I change all - (15 with 300 )

But is not work .
Work only with old range...

See ..
http://www.flickr.com/photos/77956853@N07/6838461808/

RZ100 said:

Hey Ali , i have last question
I try to change cell range,but have some problem.
I want to be - 300
I Change all formulas

but In this formula have problem.

{=IF(ROWS($F$2:F2)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F2))),"")}

I change all - (15 with 300 )

But is not work .
Work only with old range...

See ..
http://www.flickr.com/photos/77956853@N07/6838461808/

Click to expand...

Hi RZ100, I think you intended to post this on a different thread....
http://www.mrexcel.com/forum/showthread.php?t=621265

Pff) Sorry its my mistake )

Thanks for your help , for old issue