ExcelBanter - Help needed...

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Help needed... (https://www.excelbanter.com/excel-discussion-misc-queries/69560-help-needed.html)

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

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 ,
l.o.c.o.s
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...

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

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
.

In article ,
l.o.c.o.s
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 |
+-------------------------------------------------------------------+

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 :cool:

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

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: http://www.excelforum.com/member.php...o&userid=31182
View this thread: http://www.excelforum.com/showthread...hreadid=508491

I've emailed you a sample file...

In article ,
l.o.c.o.s
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 :cool:

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

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: http://www.excelforum.com/member.php...o&userid=31182
View this thread: http://www.excelforum.com/showthread...hreadid=508491

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.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 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 ,
l.o.c.o.s
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 |
+-------------------------------------------------------------------+

All done with your help... :cool:

Thank you for everything...

Cheers

:)

--
l.o.c.o.s
------------------------------------------------------------------------
l.o.c.o.s's Profile: http://www.excelforum.com/member.php...o&userid=31182
View this thread: http://www.excelforum.com/showthread...hreadid=508491

You're very welcome!

Cheers!

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

All done with your help... :cool:

Thank you for everything...

Cheers

:)