Help needed...

[l.o.c.o.s]

Hi! I need some help with my table, the problem is this....


Date---------Basket1 Basket2 Basket3 Basket4 Basket5
01-01-2006-----12-----5-------28--------44--------50
02-01-2006-----1------4-------32--------44--------6
03-01-2006-----12-----2-------45--------34--------49
04-01-2006-----22-----8-------19--------32--------1


I want to create a code that shows if anyday there was the same products buyed, exemple: If i write 12,5,28,44,50 the code should answer Yes, in 01-01-2006 or given this litle table, if i write 1,5,32,44,6 the code should anwser No. The code should only analyse row by row (By Date) and it could be able to answer if i write more that five products, exemple, if i write products 22,18,19,32,1,4,44,6, it should answer Yes 02-01-2006 and in 04-01-2006.
Because the combination of this products result in two diferent buys!

The other problem is this, imagine that the every mumber written is an product , now i want a code that show me how long a product ins't buyed, exemple, if i write Product Number: 12, the code should answer: Product 12 was last buyed 03-01-2006, 1 times since the last operational day (04-01-2006)


Thank you very much if someone could help me....

[Domenic]

Assumptions:

A1:F1 contains the headers/labels

A2:A5 contains the data

B2:F5 contains the data

Formulas:

For a list of days, let H2:H9 contain the products of interest, then
try...

I2, copied down:

=INDEX(A$2:A$5,SMALL(IF(MMULT(--ISNUMBER(MATCH(B$2:F$5,H$2:H$9,0)),TRANSP
OSE(COLUMN(B$2:F$5)^0))=5,ROW(B$2:F$5)-ROW(B$2)+1),ROWS(I$2:I2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

A couple of points...

1) For H2:H9 that contains a list of products, a dynamic range can be
defined. This way the range will automatically expand and contract as
data is entered or deleted.

2) As you copy the formula down the column you'll get #NUM! when no more
records satisfy the criteria. For this you can either hide them by
using conditional formatting or modify the formula to leave the cell
empty. If you want the latter, a defined named would be used to make
the formula less cumbersome and more efficient.

[If you need help with either of these two items, post back.]

For the last day a product was bought, let J2 contain the product of
interest, then try...

=INDEX(A2:A5,MATCH(2,1/(MMULT(--(B2:F5=J2),TRANSPOSE(COLUMN(B2:F5)^0))>0)
))

....confirmed with CONTROL+SHIFT+ENTER.

For the last operational day, try...

=LOOKUP(9.99999999999999E+307,A2:A5)

Hope this helps!

In article <[email protected]>,
l.o.c.o.s <[email protected]>
wrote:

> Hi! I need some help with my table, the problem is this....
>
>
> Date---------Basket1 Basket2 Basket3 Basket4 Basket5
> 01-01-2006-----12-----5-------28--------44--------50
> 02-01-2006-----1------4-------32--------44--------6
> 03-01-2006-----12-----2-------45--------34--------49
> 04-01-2006-----22-----8-------19--------32--------1
>
>
> I want to create a code that shows if anyday there was the same
> products buyed, exemple: If i write 12,5,28,44,50 the code should
> answer Yes, in 01-01-2006 or given this litle table, if i write
> 1,5,32,44,6 the code should anwser No. The code should only analyse row
> by row (By Date) and it could be able to answer if i write more that
> five products, exemple, if i write products 22,18,19,32,1,4,44,6, it
> should answer Yes 02-01-2006 and in 04-01-2006.
> Because the combination of this products result in two diferent buys!
>
> The other problem is this, imagine that the every mumber written is an
> product , now i want a code that show me how long a product ins't
> buyed, exemple, if i write Product Number: 12, the code should answer:
> Product 12 was last buyed 03-01-2006, 1 times since the last
> operational day (04-01-2006)
>
>
> Thank you very much if someone could help me...

[l.o.c.o.s]

Thanks for your help, but i can't put the functions to work!!!

I take a screenshot of my excel worksheet...Please help me!

Can you help me ? Also i'm interested in you help with the other two items that you mention, dynamic range and conditional formatting!

Thanks

Worksheet.jpg

[Domenic]

I can see from taking a look at your screenshot that you're using a
non-English version of Excel. I see that you've replaced commas with
semi-colons as a list separator for your version. I also see that you
have a #NOME? error. I guess that's French. Unfortunately, I'm not
familiar with the function names in French.

However, I think if I email you a sample file, Excel will automatically
convert the functions to your computer's language. Would you like me to
email you a sample file? If so, you can send me your email address at
[email protected].

In article <[email protected]>,
l.o.c.o.s <[email protected]>
wrote:

> Thanks for your help, but i can't put the functions to work!!!
>
> I take a screenshot of my excel worksheet...Please help me!
>
> Can you help me ? Also i'm interested in you help with the other two
> items that you mention, dynamic range and conditional formatting!
>
> Thanks
>
> 4318
>
>
> +-------------------------------------------------------------------+
> |Filename: Worksheet.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=4318 |
> +-------------------------------------------------------------------+

[l.o.c.o.s]

Hi, thank you for helping me….

The Excel version is the Portuguese version, but it recognizes the same functions in other languages, of course it gives the errors in Portuguese. The function IF is SE in Portuguese.
I write all the functions in English and it works well.
The error it says is #NOME? that is equal to #NAME? in English.


If you could send me the sample file it would be great.

Thanks

Note: I already send this same post to your email.

[Domenic]

I've emailed you a sample file...

In article <[email protected]>,
l.o.c.o.s <[email protected]>
wrote:

> Hi, thank you for helping me….
>
> The Excel version is the Portuguese version, but it recognizes the same
> functions in other languages, of course it gives the errors in
> Portuguese. The function IF is SE in Portuguese.
> I write all the functions in English and it works well.
> The error it says is #NOME? that is equal to #NAME? in English.
>
>
> If you could send me the sample file it would be great.
>
> Thanks
>
> Note: I already send this same post to your email.

[l.o.c.o.s]

That is fantastic, thank you very much....
That was what i wanted, know can you help with the other two items that you mention, dynamic range and conditional formatting!

And also how can i highlight in bold the result of my search in the table, the numbers and the dates.

Thanks

I add your sample file here to all can use and take impressions and benefit.
Sample.zip

[Domenic]

You're very welcome! Glad I could help!

To create a dynamic range...

Insert > Name > Define

Name: ProdNums

Refers to:

=Sheet1!$H$2:INDEX(Sheet1!$H$2:$H$65536,MATCH(9.99999999999999E+307,Sheet
1!$H$2:$H$65536))

Click Ok

Now you can use the following formula...

=INDEX(A$2:A$5,SMALL(IF(MMULT(--ISNUMBER(MATCH(B$2:F$5,ProdNums,0)),TRANS
POSE(COLUMN(B$2:F$5)^0))=5,ROW(B$2:F$5)-ROW(B$2)+1),ROWS(I$2:I2)))

....confirmed with CONTROL+SHIFT+ENTER.

To hide error values...

1) Select I2:I5, making sure that I2 is the active cell

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=ISERR(I2)

4) Choose 'White' as your font colour

5) Click Ok

To bold dates and numbers in the table...

1) Select A2:F5, making sure that A2 is the active cell

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=($A2<>"")*(COUNTIF($I$2:$I$5,$A2))

4) Choose your formatting

5) Click Ok

Hope this helps!

In article <[email protected]>,
l.o.c.o.s <[email protected]>
wrote:

> That is fantastic, thank you very much....
> That was what i wanted, know can you help with the other two items that
> you mention, dynamic range and conditional formatting!
>
> And also how can i highlight in bold the result of my search in the
> table, the numbers and the dates.
>
> Thanks
>
> I add your sample file here to all can use and take impressions and
> benefit.
> 4319
>
>
> +-------------------------------------------------------------------+
> |Filename: Sample.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4319 |
> +-------------------------------------------------------------------+

[l.o.c.o.s]

All done with your help...

Thank you for everything...

Cheers

[Domenic]

You're very welcome!

Cheers!

In article <[email protected]>,
l.o.c.o.s <[email protected]>
wrote:

> All done with your help...
>
> Thank you for everything...
>
> Cheers
>
>