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?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Assembly1[/TD]
[TD]Assembly2[/TD]
[TD]Assembly3[/TD]
[/TR]
[TR]
[TD]Group1[/TD]
[TD]Part1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group1[/TD]
[TD]Part2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group1[/TD]
[TD]Part3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group2[/TD]
[TD]Part4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group2[/TD]
[TD]Part5[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group2[/TD]
[TD]Part6[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group3[/TD]
[TD]Part7[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group3[/TD]
[TD]Part8[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group3[/TD]
[TD]Part9[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group4[/TD]
[TD]Part10[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Group4[/TD]
[TD]Part11[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Group4[/TD]
[TD]Part12[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Check [/TD]
[TD]Part1Part4Part7Part10[/TD]
[TD]Part3Part5Part8Part12[/TD]
[TD]Part2Part6Part9Part11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td]Assembly1[/td][td]Assembly2[/td][td]Assembly3[/td][/tr]

[tr][td]

2​

[/td][td]Group1[/td][td]Part1[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Group1[/td][td]Part2[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td]

4​

[/td][td]Group1[/td][td]Part3[/td][td][/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Group2[/td][td]Part4[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Group2[/td][td]Part5[/td][td][/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Group2[/td][td]Part6[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td]

8​

[/td][td]Group3[/td][td]Part7[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Group3[/td][td]Part8[/td][td][/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Group3[/td][td]Part9[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td]

11​

[/td][td]Group4[/td][td]Part10[/td][td]

1​

[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Group4[/td][td]Part11[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td]

13​

[/td][td]Group4[/td][td]Part12[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]Check[/td][td]

4​

[/td][td]

OK​

[/td][td]

Not OK​

[/td][td]

OK​

[/td][/tr]
[/table]

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 ...)