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

RZ100

New Member
Joined
Aug 26, 2011
Messages
21
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>
 
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?
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
ABCDE
1Promotion #P 11-001P 11-002P 11-003P 11-004
2NameNew YearsRed TagSale-a-brationBuy 1-Get 1
3ShopShop 2Shop 32Shop 11Shop 2
4Start Data01-Jan-1107-Jan-1111-Jan-1101-Feb-11
5End Data10-Jan-1115-Jan-1121-Jan-1110-Feb-11
6
7Products
8Product 001X
9Product 002XX
10Product 003X
11Product 004XX
12Product 005XX
13Product 006XX
14X
15Product 400XX
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.
 
Last edited:
Upvote 0
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/
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,611
Messages
6,179,884
Members
452,948
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top