Formula to Copy

[SIBYCHERIAN]

Hi I need a formula for the following ...there are 4 columns PC5,Product Code, Sample and I need a formula in the options column...

PC5 Product Code SAMPLE Options
63612 63612-0037 20
63612 63612-0038 0
63612 63612-0041 0
U6002 U6002-0012 0
U6002 U6002-0013 0
U6002 U6002-0016 20
U6002 U6002-0017 0

In the table above Under the options column , Wherever there is 20 under the samples column, In the options Column,I want all the other Product codes with the same PC5 ....meaning...I need the below output.

PC5 Product Code SAMPLE Options
63612 63612-0037 20 63612-0038,63612-0041
63612 63612-0038 0 0
63612 63612-0041 0 0
U6002 U6002-0012 0 0
U6002 U6002-0013 0 0
U6002 U6002-0016 20 U6002-0012,U6002-0013,U6002-0017
U6002 U6002-0017 0 0


Is there a formula for doing the same.

 

[SIBYCHERIAN]

Hi ,

Im trying to do the below mentioned function ,but when I copy the formula on the cell after saving the macro I get the result #NAME?...can you plz help.

Maybe an alternate non-array function will help. Mike Rickson from here on MrExcel posted a new string concatenation UDF the other day that is more compact and doesn't require array confirmation. Here's the code:

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
' used as =exactly like COUNTIF() with two additional parameters of delimiter and "no duplicates" as TRUE/FALSE if
' concatenated values might duplicate  ex. =ConcatIf($A$1:$A$10,A1,", ",True)

Dim i As Long, j As Long  'code base by Mike Rickson MrExcel MVP

With compareRange.Parent
    Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
End With

If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

Then use these non-array formulas instead. The benefit of this UDF is that it lays out just like a normal COUNTIF() formula, except it has the added delimiter parameter at the end.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Seq Numb	PC5	Product Code	Color	Sample	Options	Options Seq Numb	Options Colors
2	1	63612	63612-0037	red	20	63612-0037, 63612-0038, 63612-0041	1, 2, 3	red, Blue, Green
3	2	63612	63612-0038	Blue	0	*	*	*
4	3	63612	63612-0041	Green	0	*	*	*
5	4	U6002	U6002-0012	Yellow	0	*	*	*
6	5	U6002	U6002-0013	Red	0	*	*	*
7	6	U6002	U6002-0016	Black	20	U6002-0012, U6002-0013, U6002-0016, U6002-0017	4, 5, 6, 7	Yellow, Red, Black, White
8	7	U6002	U6002-0017	White	0	*	*	*
Sheet1

 

[jbeaucaire]

The #NAME error means your sheet can't see the CONCATIF() function. The code should be placed into a regular code module, not a sheet module nor the ThisWorkbook module.

Press ALT-F11 to open the VBEditor
Click INSERT > MODULE and a window will appear
Paste in all of the code above

 

[SIBYCHERIAN]

Hi jbeaucaire,

thank you so much ...this works.