SUMPROD and OTHER FUNCTIONS!!! VERY URGENT

[PaulinaDi]

Expecting to have better luck! Here I go again. Thanks in advanced

Hi:

I have a document that I need to constantly update as I receive new data for
it (as you may see in column named "Rev").

Rev Item Bulk Descr Price
1 1001 1 Tube 2.00
1 1001 1 Tube 2.00
1 1001 2 Tube 3.00
1 1002 6 Valve 5.00
2 1001 1 Tube 4.00
2 1002 6 Valve 8.00
2 1002 8 Valve 6.00
3 1001 1 Tube 4.00
3 1002 8 Valve 6.00
3 1001 2 Tube 4.00
3 1001 2 Tube 4.00
3 1002 8 Valve 10.00
4 1001 2 Tube 6.00
4 1001 1 Tube 5.00
4 1001 1 Tube 5.00
4 1001 2 Tube 6.00
4 1001 2 Tube 6.00
4 1002 6 Valve 10.00

Is there any way to get the price for another sheet using the SUMPRODUCT and
at the same time or just in another step compare if, this spreadsheet
contains this itemcode and bulk size just consider the last version on qty
and price and eliminate all the info before it? and if not, just leave the
info from the latest Revision version?

You can see it an eg in item 1002: the bulk size 8 "valve" version 3
desappears in version 4, but bulk size 6 valve version 2, dissapears in
version 3 but appears again in version 4.

The formula I'm using to get the Price from the other sheet is:
=SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12),AccCad!$AJ$15:$AJ$1415)/SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12))

BY THE WAY... how may I be able to reduce the formula above as I have to get
the info and then divide it from the values I get in order to get just the
price for one piece? (E contains Item Code, F contains de Bulk Size and AJ
contains the Price)

 

[smartin]

Expecting to have better luck! Here I go again. Thanks in advanced

Hi:

I have a document that I need to constantly update as I receive new data for
it (as you may see in column named "Rev").

Rev Item Bulk Descr Price
1 1001 1 Tube 2.00
1 1001 1 Tube 2.00
1 1001 2 Tube 3.00
1 1002 6 Valve 5.00
2 1001 1 Tube 4.00
2 1002 6 Valve 8.00
2 1002 8 Valve 6.00
3 1001 1 Tube 4.00
3 1002 8 Valve 6.00
3 1001 2 Tube 4.00
3 1001 2 Tube 4.00
3 1002 8 Valve 10.00
4 1001 2 Tube 6.00
4 1001 1 Tube 5.00
4 1001 1 Tube 5.00
4 1001 2 Tube 6.00
4 1001 2 Tube 6.00
4 1002 6 Valve 10.00

Is there any way to get the price for another sheet using the SUMPRODUCT and
at the same time or just in another step compare if, this spreadsheet
contains this itemcode and bulk size just consider the last version on qty
and price and eliminate all the info before it? and if not, just leave the
info from the latest Revision version?

You can see it an eg in item 1002: the bulk size 8 "valve" version 3
desappears in version 4, but bulk size 6 valve version 2, dissapears in
version 3 but appears again in version 4.

The formula I'm using to get the Price from the other sheet is:
=SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12),AccCad!$AJ$15:$AJ$1415)/SUMPRODUCT(--(AccCad!$E$15:$E$1415='TIE-INS-MAD'!N12),--(AccCad!$F$15:$F$1415='TIE-INS-MAD'!I12))

BY THE WAY... how may I be able to reduce the formula above as I have to get
the info and then divide it from the values I get in order to get just the
price for one piece? (E contains Item Code, F contains de Bulk Size and AJ
contains the Price)

I thought I was close to handing over a solution, but this case breaks
my logic:

3 1002 8 Valve 6.00
3 1002 8 Valve 10.00

There are two different prices for the same rev/product/bulk. What is
the correct one-piece price?

This is what I have so far. I did not use your ranges (sorry) but
hopefully you can follow along.

First, your sample data in A1:E19. In column F, this helper formula:
=SUMPRODUCT(--(B2&"|"&C2=B3:$B$99&"|"&C3:$C$99))

This returns zero when no higher "Rev" exists for the current "Item" and
"Bulk" combination key.

In H2 and I2, your search terms, E.g.,
1001 ... 1

In J2, price each of search (array formula):
=INDEX(E1:E99,MATCH(1,(H2=B1:B99)*(I2=C1:C99)*(0=F1:F99),0))

In K2, the Rev the search points to (array formula):
=INDEX(A1:A99,MATCH(1,(H2=B1:B99)*(I2=C1:C99)*(0=F1:F99),0))

In L2, the total price (/not/ array formula):
=J2*SUMPRODUCT(--(H2=B1:B99),--(I2=C1:C99),--(K2=A1:A99))

Throughout, "99" is a sufficiently large row to encapsulate your data.
Replace as needed.

HTH

 

[PaulinaDi]

Hi SMartin:

Maybe I could not explain myself as I would like to.
I follow your instructions and I got some results.
By the way, it was a mistake about the valves of from rev 3. The correct
price was 6. I'm very sorry.
My table is just a little example of the big one. I'm really working with
4000 rows and about 15 or 16 columns.

In some cases I have the article form the revision 1, but in some cases the
article comes from a new revision or from the latest one. I have to review
always the database and update the price from another database to have the
final price on a Central Data Base. That's why I have to use the SUMPRODUCT
funcion to take the Prices from other spreadsheets, but as I have new
versions it is too crazy to know if the price is going up and up and up again
or going lower that before of just staying on the same level. Basically I
need to know how the price is moving and to take the latest price no matter
if it is the version 1, 2, 3, 4, etc. but I need to know which is the version
to have a reference.

On the other hand.... could you explain me the formulas you used before? I
only understood the first one that decrease the number of items for each new
version. But I got lost using the others and I didn´t know if I had to use
them in each row or just on the first line.

THANKS IN ADVANCED! You are a genious!!!!!!!!

 

[smartin]

Hello,

You explained yourself well; I'm glad you pointed out the anomaly was
actually an error.

If you are not comfortable adapting my simplified solution to your
workbook I will be happy to do it for you, but it will be much easier if
you email it to me. The address is

smartin!!!@X
where !!!=108 and X=gmail.com

Meanwhile, here is an explanation of the formulas:

In column F, this helper formula:
=SUMPRODUCT(--(B2&"|"&C2=B3:$B$99&"|"&C3:$C$99))
Let's call this "MORE_REV_EXIST". Concatenates the Item and Bulk values,
and counts how many times the same combination appears below the current
row. If the result is zero, /this/ is the last revision row.


In H2 and I2, your search terms, E.g.,
1001 ... 1
No formulas here, just entering the Item and Bulk you want to get
pricing for.


In J2, price each of search (array formula):
=INDEX(E1:E99,MATCH(1,(H2=B1:B99)*(I2=C1:C99)*(0=F1:F99),0))
Matches the search terms against the data with the stipulation that
MORE_REV_EXIST is zero, i.e., fetch the most recent price each.


In K2, the Rev the search points to (array formula):
=INDEX(A1:A99,MATCH(1,(H2=B1:B99)*(I2=C1:C99)*(0=F1:F99),0))
Same as above, but fetch the actual revision number.


In L2, the total price (/not/ array formula):
=J2*SUMPRODUCT(--(H2=B1:B99),--(I2=C1:C99),--(K2=A1:A99))
Counts the number of times the Item & Bulk appears in the maximum
revision, and multiplies by price each.