How can I use Sumproduct & Concatenate together?

eurasoglu

New Member
Joined
Dec 23, 2014
Messages
8
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?


Assembly1Assembly2Assembly3
Group1Part11
Group1Part21
Group1Part31
Group2Part41
Group2Part51
Group2Part61
Group3Part71
Group3Part81
Group3Part91
Group4Part101
Group4Part111
Group4Part121
Check Part1Part4Part7Part10Part3Part5Part8Part12Part2Part6Part9Part11

<tbody>
</tbody>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi,

Below is a ready-made ConcatenateIf function :
Code:
[COLOR=#333333][FONT=Segoe UI]Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        ConcatenateRange As Range, Optional Separator As String = ",") As Variant[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    Dim i As Long[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    Dim strResult As String[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    On Error GoTo ErrHandler[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    If CriteriaRange.Count <> ConcatenateRange.Count Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        ConcatenateIf = CVErr(xlErrRef)[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        Exit Function[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    End If[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    For i = 1 To CriteriaRange.Count[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        If CriteriaRange.Cells(i).Value = Condition Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        End If[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    Next i[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    If strResult <> "" Then[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]        strResult = Mid(strResult, Len(Separator) + 1)[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    End If[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    ConcatenateIf = strResult[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    Exit Function[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]ErrHandler:[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]    ConcatenateIf = CVErr(xlErrValue)[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]End Function[/FONT][/COLOR]
 
Upvote 0
Not quite clear what you are after. It's harder to understand a problem when the problem is discussed in terms of a (non-working) formula. Are you trying to verify that an assembly is assigned parts from different groups? That means: If there are 7 groups listed, Assembly1 for example must have parts from each of these groups, right?
 
Upvote 0
Hello Aladin,

Is there an Array formula that would generate the red Check line ...???
 
Upvote 0
Hello Aladin,

Is there an Array formula that would generate the red Check line ...???

That would need non-native injection (like ACONCAT, a MCONCAT work-alike). However, I doubt that such constitutes a check in the sense of an audit.
 
Upvote 0
That would need non-native injection (like ACONCAT, a MCONCAT work-alike). However, I doubt that such constitutes a check in the sense of an audit.

Thanks Aladin ...

Think the OP is after building the conditional concatenate ... more as a reminder of items "to-be-checked" ...
 
Upvote 0
By the way ... Do you happen to know if Morefunc is available for 64bit systems ...???
 
Upvote 0
That's right. There are 4 groups and 3 parts for each. Each assembly must have at least 1 part out of 4 groups. Actually, there are almost 500 Assemblies in production lists.
I want to set up a visual error check row to see if assemblies are correctly set up in the list without missing a part from the Groups.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top