|
One part number with several substitute numbers
Hi, I have the below data sample:
A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
um, maybe i'm not understanding the big picture, but in another column
why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13*pm, Sergio wrote: Hi, I have the below data sample: A * * * * * * * * * * * * * * B * * * * * * * * * * * * C * * * * * * * * * * * * D PRT * * * * * * * * *STK * * * *SUB_SUB_PART * *SUBSTK 0068605568 * * *0 * * * 4970450487 * * *0 0068605726 * * *0 * * * 4970450826 * * *16 0068605734 * * *0 * * * 0068611051 * * *0 0068612133 * * *1 * * * 0068612722 * * *17 0068612133 * * *1 * * * 0068611051 * * *0 0068612409 * * *0 * * * 0068603220 * * *0 0068612409 * * *0 * * * 0068605046 * * *0 0068612721 * * *4 * * * 0068612132 * * *3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in
total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Hi,
Try =IF(A2<A1,SUMIF(A$2:A$100,A2,B$2:D$100),"") this assumes the first part number is on row 2. enter this formula in cell E2 and copy it down -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Unfortunately, I'm not seeing the big picture either (remember, no one
online here knows how your business model works unless you tells us in detail). For example, you have this... 0068612133 1 0068612722 17 0068612133 1 0068611051 0 and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17 associated with the 0068612133 number in Column A... which "1" is being added to the 17 and why is the other one being ignored? Or am I misreading where the 1 is coming from? -- Rick (MVP - Excel) "Sergio" wrote in message ... Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
You are right, is hard to explain, in english this would read; of part
0068612133 i have 1 part, plus substitute 0068612722 i have 17 parts. The next line reads part 0068612133 i still have 1 part and subst 0068611051 i have 0 parts. This is why it adds to 17 parts. This is part of a bigger formula which would calculate "of this part and all it's substitutes" I have X parts total. "Rick Rothstein" wrote: Unfortunately, I'm not seeing the big picture either (remember, no one online here knows how your business model works unless you tells us in detail). For example, you have this... 0068612133 1 0068612722 17 0068612133 1 0068611051 0 and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17 associated with the 0068612133 number in Column A... which "1" is being added to the 17 and why is the other one being ignored? Or am I misreading where the 1 is coming from? -- Rick (MVP - Excel) "Sergio" wrote in message ... Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Sorry, I'm still a little fuzzy on this. Maybe this will help me
understand... what would the correct answer be for these scenarios? #1 0068612133 1 0068612722 3 0068612133 2 0068611051 4 #2 0068612133 4 0068612722 8 0068612133 2 0068611051 5 #3 0068612133 1 0068612722 4 0068612133 2 0068611051 3 #4 0068612133 4 0068612722 5 0068612133 2 0068611051 8 -- Rick (MVP - Excel) "Sergio" wrote in message ... You are right, is hard to explain, in english this would read; of part 0068612133 i have 1 part, plus substitute 0068612722 i have 17 parts. The next line reads part 0068612133 i still have 1 part and subst 0068611051 i have 0 parts. This is why it adds to 17 parts. This is part of a bigger formula which would calculate "of this part and all it's substitutes" I have X parts total. "Rick Rothstein" wrote: Unfortunately, I'm not seeing the big picture either (remember, no one online here knows how your business model works unless you tells us in detail). For example, you have this... 0068612133 1 0068612722 17 0068612133 1 0068611051 0 and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17 associated with the 0068612133 number in Column A... which "1" is being added to the 17 and why is the other one being ignored? Or am I misreading where the 1 is coming from? -- Rick (MVP - Excel) "Sergio" wrote in message ... Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Just a general comment...
Excel usually doesn't handle numbers that start with leading 0s very well. They almost always cause unforseen problems! Try something this: E1 = some part number =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E1,D:D) -- Biff Microsoft Excel MVP "Sergio" wrote in message ... You are right, is hard to explain, in english this would read; of part 0068612133 i have 1 part, plus substitute 0068612722 i have 17 parts. The next line reads part 0068612133 i still have 1 part and subst 0068611051 i have 0 parts. This is why it adds to 17 parts. This is part of a bigger formula which would calculate "of this part and all it's substitutes" I have X parts total. "Rick Rothstein" wrote: Unfortunately, I'm not seeing the big picture either (remember, no one online here knows how your business model works unless you tells us in detail). For example, you have this... 0068612133 1 0068612722 17 0068612133 1 0068611051 0 and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17 associated with the 0068612133 number in Column A... which "1" is being added to the 17 and why is the other one being ignored? Or am I misreading where the 1 is coming from? -- Rick (MVP - Excel) "Sergio" wrote in message ... Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Those scenarios can't happen, let me explain, column A is the prime part
number, so if this number is the same, it only has one quantity, the number that could change is in column D. For instance scenario #1; 0068612133 has 1 part but you can have 2 substitute numbers with 3 and 4 parts each number. This totals 1+3+4=8 in total. Not sure if this is clear.. "Rick Rothstein" wrote: Sorry, I'm still a little fuzzy on this. Maybe this will help me understand... what would the correct answer be for these scenarios? #1 0068612133 1 0068612722 3 0068612133 2 0068611051 4 #2 0068612133 4 0068612722 8 0068612133 2 0068611051 5 #3 0068612133 1 0068612722 4 0068612133 2 0068611051 3 #4 0068612133 4 0068612722 5 0068612133 2 0068611051 8 -- Rick (MVP - Excel) "Sergio" wrote in message ... You are right, is hard to explain, in english this would read; of part 0068612133 i have 1 part, plus substitute 0068612722 i have 17 parts. The next line reads part 0068612133 i still have 1 part and subst 0068611051 i have 0 parts. This is why it adds to 17 parts. This is part of a bigger formula which would calculate "of this part and all it's substitutes" I have X parts total. "Rick Rothstein" wrote: Unfortunately, I'm not seeing the big picture either (remember, no one online here knows how your business model works unless you tells us in detail). For example, you have this... 0068612133 1 0068612722 17 0068612133 1 0068611051 0 and you say the "answer" is 18 (1+17). Why? There are two 1's, a 0 and a 17 associated with the 0068612133 number in Column A... which "1" is being added to the 17 and why is the other one being ignored? Or am I misreading where the 1 is coming from? -- Rick (MVP - Excel) "Sergio" wrote in message ... Thank you Susan, but if you see the latter example, the qty=1 + 17 = 18 in total. If i add them the way you tell me, i would get 19 in total. Also, sometimes i can have as many as 23 substitute part numbers, each one with it's own qty but the original part will still have the same qty row after row. "Susan" wrote: um, maybe i'm not understanding the big picture, but in another column why can't you have: =b2+d2 =b3+d3 =b4+d4 etc.? that seems like it would give you your total. you could even put it in a column waaaay out if you don't want it to interfere with this data. just a thought susan On Aug 11, 3:13 pm, Sergio wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Hi, Put this formula in E2 and then drag downwords.
=SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime
number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Assuming this is your data:
xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it to: =VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D:D) being at row 2 and it works except where the first part number qty=0 and the substitute qty= XX, this always shows zero as the answer. I'll keep trying.. "T. Valko" wrote: Assuming this is your data: xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Yes your understanding is OK, i tried your formula:
=VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, That wasn't the formula that Biff posted... the E2 in the SUMIF function was an E1 in his original formula. When I tried the formula he posted originally, and given his understanding of what you were looking for, it appeared for work perfectly. I would suggest you retry his original formula again (copy/paste it rather than retype it). -- Rick (MVP - Excel) "Sergio" wrote in message ... Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it to: =VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D:D) being at row 2 and it works except where the first part number qty=0 and the substitute qty= XX, this always shows zero as the answer. I'll keep trying.. "T. Valko" wrote: Assuming this is your data: xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
......A.....B.....C.....D
1..column headers 2..xxx...0...abc...5 3..xxx...0...def...2 4..xxx...0...ghi...3 Lookup part number xxx E2 = xxx =VLOOKUP(E2,A:B,2,0)+SUMIF(A:A,E2,D:D) Result = 10 -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it to: =VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D:D) being at row 2 and it works except where the first part number qty=0 and the substitute qty= XX, this always shows zero as the answer. I'll keep trying.. "T. Valko" wrote: Assuming this is your data: xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
Sorry Rick and Biff, i tried again and i think i can adapt this formula to
the problem i have, it is working. Thanks, Sergio "Rick Rothstein" wrote: Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, That wasn't the formula that Biff posted... the E2 in the SUMIF function was an E1 in his original formula. When I tried the formula he posted originally, and given his understanding of what you were looking for, it appeared for work perfectly. I would suggest you retry his original formula again (copy/paste it rather than retype it). -- Rick (MVP - Excel) "Sergio" wrote in message ... Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it to: =VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D:D) being at row 2 and it works except where the first part number qty=0 and the substitute qty= XX, this always shows zero as the answer. I'll keep trying.. "T. Valko" wrote: Assuming this is your data: xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
One part number with several substitute numbers
It sounds like you just need to get the references pointing to the correct
ranges and it should work for you. -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Sorry Rick and Biff, i tried again and i think i can adapt this formula to the problem i have, it is working. Thanks, Sergio "Rick Rothstein" wrote: Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, That wasn't the formula that Biff posted... the E2 in the SUMIF function was an E1 in his original formula. When I tried the formula he posted originally, and given his understanding of what you were looking for, it appeared for work perfectly. I would suggest you retry his original formula again (copy/paste it rather than retype it). -- Rick (MVP - Excel) "Sergio" wrote in message ... Yes your understanding is OK, i tried your formula: =VLOOKUP(E1,A:B,2,0)+SUMIF(A:A,E2,D:D), but it showed a #N/A, so i changed it to: =VLOOKUP(A2,A:B,2,0)+SUMIF(A:A,E2,D:D) being at row 2 and it works except where the first part number qty=0 and the substitute qty= XX, this always shows zero as the answer. I'll keep trying.. "T. Valko" wrote: Assuming this is your data: xxx...1...abc...5 xxx...1...def...2 xxx...1...ghi...3 My understanding is that for part number xxx the correct result should be: 1+5+2+3 = 11 Did you try the formula I suggested in the other branch of this thread? -- Biff Microsoft Excel MVP "Sergio" wrote in message ... Thanks Vijay, almost works, but it doesn't when i have qty= 0 for the prime number and some other qty in the substitute column.. SB "Vijay" wrote: Hi, Put this formula in E2 and then drag downwords. =SUMIF($A$2:$A$9,IF(COUNTIF($A$2:A2,A2)=1,A2,""),$ D$2:$D$9)+IF(IF(COUNTIF($A$2:A2,A2)=1,A2,"")="",0, B2) Assumed your header row starts from A1. Vijay "Sergio" wrote: Hi, I have the below data sample: A B C D PRT STK SUB_SUB_PART SUBSTK 0068605568 0 4970450487 0 0068605726 0 4970450826 16 0068605734 0 0068611051 0 0068612133 1 0068612722 17 0068612133 1 0068611051 0 0068612409 0 0068603220 0 0068612409 0 0068605046 0 0068612721 4 0068612132 3 In this case, there is a part number (column A), quantity of this part (B), substitute part number (C), and quantity of the substitute. For instance, part 0068605568, has qty=0 and a subst. 4970450487 also with qty=o. Part 0068612133 with qty=1 and a subst. 0068612722 with qty=17, and another subst. 0068611051 with qty=0. My question is, how do i know how many parts i have between the part number and it's substitutes. In the latter case i would have a total of 1+17 parts. Thanks, |
| All times are GMT +1. The time now is 10:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com