software

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--------1I 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
------------------------------------------------------------------------
l.o.c.o.s's Profile: www.excelforum.com/member.php...oamp;userid=31182
View this thread: www.excelforum.com/showthread...hreadid=508491Assumptions:

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))gt;0)
))

....confirmed with CONTROL SHIFT ENTER.

For the last operational day, try...

=LOOKUP(9.99999999999999E 307,A2:A5)

Hope this helps!

In article gt;,
l.o.c.o.s gt;
wrote:

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


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: www.excelforum.com/attachment.php?postid=4318 |
-------------------------------------------------------------------

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: www.excelforum.com/member.php...oamp;userid=31182
View this thread: www.excelforum.com/showthread...hreadid=508491I 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
.

In article gt;,
l.o.c.o.s gt;
wrote:

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


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
------------------------------------------------------------------------
l.o.c.o.s's Profile: www.excelforum.com/member.php...oamp;userid=31182
View this thread: www.excelforum.com/showthread...hreadid=508491I've emailed you a sample file...

In article gt;,
l.o.c.o.s gt;
wrote:

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


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: www.excelforum.com/attachment.php?postid=4319 |
-------------------------------------------------------------------

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: www.excelforum.com/member.php...oamp;userid=31182
View this thread: www.excelforum.com/showthread...hreadid=508491You're very welcome! Glad I could help!

To create a dynamic range...

Insert gt; Name gt; Define

Name: ProdNums

Refers to:

=Sheet1!$H$2:INDEX(Sheet1!$H$2:$H$65536,MATCH(9.99 999999999999E 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 gt; Conditional Formatting gt; 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 gt; Conditional Formatting gt; Formula Is

3) Enter the following formula:

=($A2lt;gt;quot;quot;)*(COUNTIF($I$2:$I$5,$A2))

4) Choose your formatting

5) Click Ok

Hope this helps!

In article gt;,
l.o.c.o.s gt;
wrote:

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


All done with your help...

Thank you for everything...

Cheers

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: www.excelforum.com/member.php...oamp;userid=31182
View this thread: www.excelforum.com/showthread...hreadid=508491You're very welcome!

Cheers!

In article gt;,
l.o.c.o.s gt;
wrote:

gt; All done with your help...
gt;
gt; Thank you for everything...
gt;
gt; Cheers
gt;
gt;