Match index issue?

Hello Excel experts,

I need some help with following problem that I have been struggling with for days. I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result that I need. Can you guys help????????????


Here's how the Data looks like:

Order Part numbers
123 A
123 B
123 C
123 D
234 A
234 B
234 E
346 A
346 C

Answer for above would be 2 orders that have A and B part numbers on the same order.

Thanks in advance,

MP

Not very elegant... but perhaps:

=SUMPRODUCT(--(B2:B10="A"),--(ISNUMBER(MATCH(A2:A10&":B",A2:A10&":"&B2:B10,0))))

(flawed if you have multiple instances of Order & Part A)

Are you able to use a column of helper cells ?

Well for ex... if you could use another column adjacent to your data such that (based on example)

C2: =A2&":"&B2
copied down to C10

Then you could use the below for your total:

=SUMPRODUCT(--(B2:B10="A"),--(ISNUMBER(MATCH(A2:A10&":B",C2:C10,0))),1/COUNTIF(C2:C10,C2:C10&""))

This would account for multiple instances of Order:A

even uglier is this youwill get at least one true for each id if both conditions are matched part numbers to match go in d1 and e1

For the sake of simplicity & consistency let's assume:

A2:A10: Orders
B2:B10: Part Nos.
C2:C10: Quantity

D2: =$A2&":"&$B2
copied to D10

COUNT of Orders with A & B:
=SUMPRODUCT(--($B$2:$B$10="B"),--(ISNUMBER(MATCH($A$2:$A$10&":A",$D$2:$D$10,0))),1/COUNTIF($D$2:$D$10,$D$2:$D$10&""))

SUM of B Quantity for A & B Orders
=SUMPRODUCT(--($B$2:$B$10="B"),--(ISNUMBER(MATCH($A$2:$A$10&":A",$D$2:$D$10,0))),$C$2:$C$10)

Per your PM:

Originally Posted by Mparekh

Hi DonkeyOte,

Your formula works great and gives me exactly what I need but unfortunatley I don't understand the logic. Anyway possible for you to explain me what's happening behind the scenes?

I am curious because I want to do more with the data, like find total price of Part A and Part B (sum of both the prices) on each order and I cannot do that unless I understand how the formula works.

Thanks for your help,
MP

First off it is imperative that you have basic grasp of the Sumproduct function itself and how it works, please read through Bob's page in the first instance - http://www.xldynamic.com/source/xld.SUMPRODUCT.html
(this was the means by which I learnt the formula myself)

Let's first deal with the SUMPRODUCT function that Sums Qty, assuming you've read the above link the below should be relatively straight forward to understand

=SUMPRODUCT(--($B$2:$B$10="B"),--(ISNUMBER(MATCH($A$2:$A$10&":A",$D$2:$D$10,0))),$C$2:$C$10)

The only tricky part here is we're saying we only want to sum the values in Col C where the value in Col B = "B" and where we can find the Order:Part A equivalent value in the other values... ie both Order:A and Order:B exist in the data set.

The Count is slightly more convoluted because of the issue that we may have multiple instances of Part B for any given Order

=SUMPRODUCT(--($B$2:$B$10="B"),--(ISNUMBER(MATCH($A$2:$A$10&":A",$D$2:$D$10,0))),1/COUNTIF($D$2:$D$10,$D$2:$D$10&""))

So the above is identical to the SUM (ie only interested in rows where Col B = B and we can find an Order:A match for the Order:B record) but instead of Summing Col C as the final argument we conduct a COUNT

1/COUNTIF($D$2:$D$10,$D$2:$D$10&"")

What the above will do is handle multiple instances of say 123:B ... if we have say the following data set in D2:D10 (remember D2:D10 is concatenation of A & B)

Please Login or Register  to view this content.

Then our 1/COUNTIF(D2:D10,D2:D10&"") will generate an array of values which can be viewed as:

Please Login or Register  to view this content.

The divisor is the COUNT of the concatenation code in the range - ie 123:B appears three times so we get a divisor of 3 -> 1/3 whereas 234:A appears only once so we get a divisor of 1 -> 1/1... so the above array results in the following array of values:

Please Login or Register  to view this content.

So the above array of values acts in the same manner as our summation range in the SUM version (ie like C2:C10) ... we multiply the Sumproduct results together so continuing with the above data set example our Sumproduct:

=SUMPRODUCT(--($B$2:$B$10="B"),--(ISNUMBER(MATCH($A$2:$A$10&":A",$D$2:$D$10,0))),1/COUNTIF($D$2:$D$10,$D$2:$D$10&""))

Will generate the following 3 array of values:

Please Login or Register  to view this content.

The Product of these arrays can thus be viewed as:

Please Login or Register  to view this content.

The SUM of these Products thus being: 2

This makes sense as we have only 2 distinct Order Numbers that have both Part A & B listed namely Orders 123 & 234