Matching products with the same ID code

[orangesegments]

Hi.

I'm struggling with finding how to solve a particular Excel Problem.

I have a table that comprises of labelled columns with lists below. The columns are as follows with an example of a single transaction:

Unique Transaction Identifier	Business Area	Category	Sub Category	Product Code	Sales Quantity
4343	12	1321	4354	243355	1
4343	2312 (attachment)	3222	1253	249244	1
4343	2313 (attachment)	3212	1586	249443	1

Every time a product is sold, attachments can be sold with it and is shown on a separate line. In the above example, the second two products are attachments. The overall sheet has over 1000 transactions and I am trying to find the number of attachments by sub-category sold with each business area. I've tried using index match to show the business area sold in each transaction but am unable to then use the same formula to show the sub-categories because it just matches onto the first instance of the Unique ID and I'm unsure if offset or pivots will help. My end goal is a table along the lines of:

Business Area 12

Sub-Category	Sales Quantity
1253	1
1586	1
....	....

Is this possible?

 

[QuietRiot]

You could add 2 columns after Category say (D,E). In Column E (attachment): =IF(ISERROR(FIND("attachment",B2,1)),FALSE, TRUE)
Then in Column D (Parent Business Area): =INDEX(B:B,MATCH(1, (A2=A:A) * (E:E=FALSE), 0))... ctrl shift enter

This will allow you to filter Parent Business area and attachments