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.

 

[Sandeep Warrier]

Right-click the cell and select format. The format should be General. If it is not then delete the formula, right-click again and select format, select General & click ok. Then paste the formula..

 

[SIBYCHERIAN]

Hi sandeep...

It still doesnot work...

 

[Sandeep Warrier]

Hello,

Please try it once more. Be sure to delete the initial formula. Once you have deleted the formula, change format to General and before entering the formula again save your workbook.

Once you have changed the format & saved the workbook, try entering the formula again. It should work now.

 

[SIBYCHERIAN]

HI ...It still does not Work...

Plz help...

 

[Sandeep Warrier]

If you could post a sample of your spreadsheet so that we see what is going on we might be able to help. Because I tried the formula on my workbook and it worked perfectly.

 

[jbeaucaire]

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

 

[Krishnakumar]

Hi,

UDF:

Function KCONCAT(Var As Variant, _
            Optional Delim As String = ",", _
            Optional Exc As Boolean = True) As String
            'Exc - Removing False from output
Dim v
If IsArray(Var) Then
    For Each v In Var
        KCONCAT = KCONCAT & Delim & v
    Next
    If Len(KCONCAT) > 1 Then
        If Exc Then
            KCONCAT = Trim$(Replace(Replace(Mid$(Trim$(KCONCAT), 2), _
            Delim & "False", ""), "False" & Delim, ""))
        Else
            KCONCAT = Mid$(Trim$(KCONCAT), 2)
        End If
    End If
Else: KCONCAT = Var
End If
End Function

use:

F2:

=IF($E2=20,KCONCAT(IF($B$2:$B$8=B2,$C$2:$C$8),", "),"")

and copied down.

Confirmed with CTRL + SHIFT + ENTER

HTH

 

[SIBYCHERIAN]

Hi jbeaucaire ..

the code given by u in the end Worked great...thanks a Ton ...all u guys...Saved me a lot of time...superb Stuff...

thanks once again..

 

[SIBYCHERIAN]

Hi Jbeaucaire,

I have been using the below mentioned Code and formula...But now when paste the formula i donot get the desired result but I get and output which says "#Name?" ...I now use excel 2007 ...Could u plz help.

First of all, this is the result I get from your sample data and criteria, which doesn't match your sample result...so check that.

Excel Workbook
	A	B	C	D
1	PC5	Product Code	Sample	options
2	63612	63612-0037	20	63612-0037, 63612-0038, 63612-0041
3	63612	63612-0038	0	*
4	63612	63612-0041	0	*
5	U6002	U6002-0012	0	*
6	U6002	U6002-0013	0	*
7	U6002	U6002-0016	20	U6002-0012, U6002-0013, U6002-0016, U6002-0017
8	U6002	U6002-0017	0	*
Sheet1

The way I got this was to install a new function called JoinAll. This function will take a single criteria from one column and return a concatenated string of values from the second column. To add this function to your sheet:

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

Function JoinAll(ByVal BaseValue, ByRef rng As Range, ByVal delim As String)
Dim a, i As Long    'code base by Jindon MrExcel MVP
a = rng.Value
For i = 1 To UBound(a, 1)
    If a(i, 1) = BaseValue Then JoinAll = JoinAll & _
        IIf(JoinAll = "", "", delim) & a(i, 2)
Next
End Function

ALT-F11 to close the editor and save your sheet.

Now use the sample formula above shown how to identify the value to match, show the 2-column range, and then provide the delimiter...a comma in your formula.

Copy that formula down.

 

[jbeaucaire]

Did you install the UDF into the workbook in Excel 2007? You'll have to save that workbook as a *.xlsm.