How can I use Sumproduct & Concatenate together?

[eurasoglu]

Hi,
I have such a complexity matrix for a product table. Every time I update the table, I'd like to do a final check to see if the all assemblies are marked properly, so that they are pulling at least one "Part" from each Product Group in below table. Depending the usage, I want to concatenate part names (Part1,2,3...) with sumproduct functionality as below in red.
How can I use Sumproduct & Concatenate functions to form such string?

		Assembly1	Assembly2	Assembly3
Group1	Part1	1
Group1	Part2			1
Group1	Part3		1
Group2	Part4	1
Group2	Part5		1
Group2	Part6			1
Group3	Part7	1
Group3	Part8		1
Group3	Part9			1
Group4	Part10	1
Group4	Part11			1
Group4	Part12		1
	Check	Part1Part4Part7Part10	Part3Part5Part8Part12	Part2Part6Part9Part11

<tbody>
</tbody>

 

[eurasoglu]

I want to set up a array formula in the check line for visual check of each assembly against errors. (Each assembly must have at least one Part selected out of 4 Groups.)

 

[James006]

Sorry for my misunderstanding of your question ...

I know Aladin will come up with the perfect answer ...

 

[Aladin Akyurek]

I want to set up a array formula in the check line for visual check of each assembly against errors. (Each assembly must have at least one Part selected out of 4 Groups.)

Row\Col	A​	B​	C​	D​	E​
1​			Assembly1	Assembly2	Assembly3
2​	Group1	Part1	1​
3​	Group1	Part2			1​
4​	Group1	Part3		1​
5​	Group2	Part4	1​
6​	Group2	Part5		1​
7​	Group2	Part6			1​
8​	Group3	Part7	1​
9​	Group3	Part8		1​
10​	Group3	Part9			1​
11​	Group4	Part10	1​
12​	Group4	Part11			1​
13​	Group4	Part12
14​
15​	Check	4​	OK​	Not OK​	OK​

B15, control+shift+enter (cse), not just enter:

=SUM(IF(FREQUENCY(IF(A2:A13<>"",MATCH(A2:A13,A2:A13,0)),
    ROW(A2:A13)-ROW(A2)+1),1))

C16, cse and copy across:

=(SUM(IF(FREQUENCY(IF($A$2:$A$13<>"",
    IF(ISNUMBER(1/C$2:C$13),MATCH($A$2:$A$13,$A$2:$A$13,0))),
    ROW($A$2:$A$13)-ROW($A$2)+1),1))=$B$15)+0

Custom format C16:E6 as: [=0]"Not OK";"OK"

 

[James006]

Aladin,

Brilliant solution ... (as usual ...)