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

[RZ100]

Hello Guys ,
I try to mark the product 1 from store 2 in any color when is in during period of the promotion of a product 1 from store 1.

Briefly:
I do not want two identical product to match the same period of the promotion.

At all possible?

I would be grateful if you helped me!

EXAMPLE:

<table width="481" border="0" cellpadding="0" cellspacing="0"><col style="width: 85pt;" width="113"> <col style="width: 77pt;" width="103"> <col style="width: 97pt;" width="129"> <col style="width: 102pt;" width="136"> <tbody><tr style="height: 24.75pt;" height="33"> <td class="xl65" style="height: 24.75pt; width: 85pt;" width="113" height="33">Magazines</td> <td class="xl65" style="width: 77pt;" width="103">Products</td> <td class="xl65" style="width: 97pt;" width="129">Start date </td> <td class="xl65" style="width: 102pt;" width="136">End date</td> </tr> <tr style="height: 24.75pt;" height="33"> <td style="height: 24.75pt;" height="33">shop 1</td> <td>product 1</td> <td class="xl66" align="right">01 January 2011</td> <td class="xl66" align="right"> 10 January 2011</td> </tr> <tr style="height: 24.75pt;" height="33"> <td style="height: 24.75pt;" height="33">
</td> <td>
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height: 24.75pt;" height="33"> <td style="height: 24.75pt;" height="33">shop 2</td> <td>product 1 </td> <td class="xl66" align="right">01 January 2011</td> <td class="xl66" align="right"> 05 January 2011</td> </tr> </tbody></table>

 

[Jerry Sullivan]

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?

 

[RZ100]

Hello, JS
did you receive my message ?

Thanks for ALL!

 

[Jerry Sullivan]

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:

Excel Workbook
	A	B	C	D	E
1	Promotion #	P 11-001	P 11-002	P 11-003	P 11-004
2	Name	New Years	Red Tag	Sale-a-bration	Buy 1-Get 1
3	Shop	Shop 2	Shop 32	Shop 11	Shop 2
4	Start Data	01-Jan-11	07-Jan-11	11-Jan-11	01-Feb-11
5	End Data	10-Jan-11	15-Jan-11	21-Jan-11	10-Feb-11
6
7	Products
8	Product 001	X
9	Product 002			X	X
10	Product 003	X
11	Product 004		X		X
12	Product 005	X			X
13	Product 006	X	X
14					X
15	Product 400		X		X
Promotions

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.

 

[RZ100]

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/

 

[Jerry Sullivan]

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/

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

 

[RZ100]

Pff) Sorry its my mistake )

Thanks for your help , for old issue