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]

Welcome to the Board,

You can use the =COUNTIFS() function to count how many rows in your list match the multiple criteria:
Shop# of Current Row = Shop# of any Row
Product# of Current Row = Product# of any Row
End Date of Current Row >= Start Date of any Row
Start Date of Current Row <= End Date of any Row

If the count for the current row is greater than one, then the row can be marked with Conditional Formatting.

 

[RZ100]

Hey , JS !

Many thanks for Help .

I tried to finish the formula is not very successful
Because I am not very good with formulas - Somewhere confused with ">" <..
Have posted a complete formula to try your version.

<table width="404" border="0" cellpadding="0" cellspacing="0"><col style="width: 85pt;" width="113"> <col style="width: 77pt;" width="103"> <col style="width: 83pt;" width="111"> <col style="width: 58pt;" width="77"> <tbody><tr style="height: 25.5pt;" height="34"> <td class="xl66" style="height: 25.5pt; width: 85pt;" width="113" height="34">SHOPS</td> <td class="xl66" style="width: 77pt;" width="103">Start Data</td> <td class="xl66" style="width: 83pt;" width="111">End Data</td> <td class="xl66" style="width: 58pt;" width="77">Products</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">shop1</td> <td class="xl65" align="right">01/01/2011</td> <td class="xl65" align="right">10/01/2011</td> <td>product 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">shop2</td> <td class="xl65" align="right">05/01/2011</td> <td class="xl65" align="right">11/01/2011</td> <td>product 1</td> </tr> </tbody></table>

Тhank you !

 

[Jerry Sullivan]

Maybe something like this....

Excel Workbook
	A	B	C	D	E
1	SHOPS	Start Data	End Data	Products	Count
2	shop1	1/1/2011	10/1/2011	product 1	3
3	shop2	5/1/2011	11/1/2011	product 1	2
4	shop1	1/1/2011	4/30/2011	product 2	1
5	shop2	5/1/2011	11/1/2011	product 2	1
6	shop1	1/1/2011	10/1/2011	product 3	1
7	shop2	1/1/2011	10/1/2011	product 4	1
8	shop3	1/1/2011	1/1/2011	product 1	2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	1. / Formula is =$E2>1	Abc

Note in my first post, I listed as one of the criteria:
Shop# of Current Row = Shop# of any Row

That is incorrect, since you will want to find overlaps among different shops.

 

[RZ100]

Thank you very much Js, you 'r the best It helped me ...

Currently works very well, will test more shops ....

Small problem I have is that one cell does not account if more than 1 product..



<table width="525" border="0" cellpadding="0" cellspacing="0"><col style="width: 79pt;" width="105"> <col style="width: 79pt;" width="105" span="3"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl66" style="height: 30pt; width: 79pt;" width="105" height="40">SHOPS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">Start Data</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">End Data</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">PRODUCTS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">COUNT</td> </tr> <tr style="height: 38.25pt;" height="51"> <td class="xl71" style="height: 38.25pt; border-top: medium none;" height="51">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl72" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">product 1
product 1
product 4

</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 38.25pt;" height="51"> <td class="xl71" style="height: 38.25pt; border-top: medium none;" height="51">shop2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl72" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">product 4
product 1
product 4

</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">04/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 44</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

But this is probably normal because it is quite complicated - in the cell to account as a different name ..

I will try to arrange .
You help me alot Thank you !

Nice day ! for Now

 

[Jerry Sullivan]

Glad to have helped.

I suggest you use a separate row for each product instead of listing multiple products in the same cell. You'll need to repeat the other data (Shop, Start/End).

 

[RZ100]

Thanks again JS !
I did it in one cell, because I did not want to repeat the information in rows
I just wanted everything on 1 line

Like this :

<table width="525" border="0" cellpadding="0" cellspacing="0"><col style="width: 79pt;" width="105"> <col style="width: 79pt;" width="105" span="3"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl66" style="height: 30pt; width: 79pt;" width="105" height="40">SHOPS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">Start Data</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">End Data</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">PRODUCTS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">COUNT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl72" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">pro 11</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl72" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">pro 12</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">pro 13</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop3</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>
shop 1 all repeate in 3 cell ,many of shops heave 10 articles ) is to match repeats....
So that is not possible to be in 1 row - multiple products right?

So to do not match in products in same shop,must be incorporated in the formulation shop- row right?

maybe you should think about some of rows to have less repates
I try this ,but wrong formula i try to add
like this:

<table width="658" border="0" cellpadding="0" cellspacing="0"><col style="width: 79pt;" width="105"> <col style="width: 79pt;" width="105" span="2"> <col style="width: 179pt;" width="238"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl66" style="height: 30pt; width: 79pt;" width="105" height="40">SHOPS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">Start Data</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">End Data</td> <td class="xl66" style="border-left: medium none; width: 179pt;" width="238">PRODUCTS</td> <td class="xl66" style="border-left: medium none; width: 79pt;" width="105">COUNT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td rowspan="3" class="xl76" style="height: 38.25pt; border-right: 0.5pt solid windowtext; border-bottom: medium none; border-left: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228);" height="51">shop1</td> <td rowspan="3" class="xl73" style="border-right: 0.5pt solid windowtext; border-bottom: medium none; border-left: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228);">01/01/2011</td> <td rowspan="3" class="xl73" style="border-right: 0.5pt solid windowtext; border-bottom: medium none; border-left: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228);">10/01/2011</td> <td class="xl72" style="width: 179pt; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228);" width="238">product 1</td> <td class="xl65" style="border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228);" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none; border-left: medium none; width: 179pt;" width="238" height="17">product 2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl72" style="height: 12.75pt; border-top: medium none; border-left: medium none; width: 179pt;" width="238" height="17">product 3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">product 3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>


So what do you think
better option with less reps ..

 

[Jerry Sullivan]

I'm not following where the cells are in the options that you are suggesting.
Is the last image showing shop1 in a merged cell (3 cells high?).

The problem is that it is very difficult to find matches between parts of one cell and parts of other cells in a range.

This could be done with VBA code, but I was trying to keep this formula-based for you.

 

[RZ100]

Thanks for your replay man!
So, then, this is the only way is to repeat them milked in the column?


<table width="525" border="0" cellpadding="0" cellspacing="0"><col style="width: 79pt;" width="105"> <col style="width: 79pt;" width="105" span="3"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 30pt;" height="40"> <td class="xl64" style="height: 30pt; width: 79pt;" width="105" height="40">SHOPS</td> <td class="xl64" style="border-left: medium none; width: 79pt;" width="105">Start Data</td> <td class="xl64" style="border-left: medium none; width: 79pt;" width="105">End Data</td> <td class="xl64" style="border-left: medium none; width: 79pt;" width="105">PRODUCTS</td> <td class="xl64" style="border-left: medium none; width: 79pt;" width="105">COUNT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">pro 1</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl70" style="border-top: medium none; border-left: medium none; width: 79pt;" width="105">pro 2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">pro 13</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; border-top: medium none;" height="17">shop2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">product 2</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; border-top: medium none;" height="17">shop3</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">10/01/2011</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">product 3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">1</td> </tr> </tbody></table>

to add 3 product,its need to repeat 3 times...
but if you i use this,than need to change formula

However I think there should be a shorter way to do this.
But i still think about this ..
i must go now ,thanks for all man !
nice day !

 

[RZ100]

Hello , JS : ))

Outpost I've written before sleep) what you said about row is the best way ... Thanks
But in formula hm .. i give more range and formula give me VALUE? / / Something is wrong ..

Take a look please..

<table border="0" cellpadding="0" cellspacing="0" width="731"><col style="width: 79pt;" width="105"> <col style="width: 79pt;" width="105" span="2"> <col style="width: 114pt;" width="152"> <col style="width: 58pt;" width="77"> <col style="width: 62pt;" width="82"> <col style="width: 79pt;" width="105"> <tbody><tr style="height: 46.5pt;" height="62"> <td class="xl70" style="height: 46.5pt; width: 79pt;" width="105" height="62">shop1</td> <td class="xl68" style="border-left: medium none; width: 79pt;" width="105">01/01/2011</td> <td class="xl68" style="border-left: medium none; width: 79pt;" width="105">10/01/2011</td> <td class="xl71" style="border-left: medium none; width: 114pt;" width="152">pro 3</td> <td class="xl71" style="border-left: medium none; width: 58pt;" width="77">pro 4</td> <td class="xl67" style="border-left: medium none; width: 62pt;" width="82">pro 5 </td> <td class="xl65" style="border-left: medium none; width: 79pt;" width="105"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt; border-top: medium none;" height="17">shop3</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">13/01/2011</td> <td class="xl71" style="border-top: medium none; border-left: medium none; width: 114pt;" width="152">pro 4
</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pro 544</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pro 555</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt; border-top: medium none;" height="17">shop1</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">15/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">20/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">pro 322</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl70" style="height: 12.75pt; border-top: medium none;" height="17">shop2</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">05/01/2011</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">11/01/2011</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">product 2</td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl67" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> </td> </tr> </tbody></table>
may i miss some symbols in the formula:

=COUNTIFS($D$2:$H$8,D2:H2,$B$2:$B$8, "<=" & C2,$C$2:$C$8,">=" & B2)