Formula to Copy

SIBYCHERIAN

New Member
Joined
Mar 31, 2006
Messages
30
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
ABCD
1PC5Product CodeSampleoptions
26361263612-00372063612-0037, 63612-0038, 63612-0041
36361263612-00380
46361263612-00410
5U6002U6002-00120
6U6002U6002-00130
7U6002U6002-001620U6002-0012, U6002-0013, U6002-0016, U6002-0017
8U6002U6002-00170
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:
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.
 
Upvote 0
An additional note...this is NOT an array formula/function, so it will not have any adverse effects on your sheet performance.

Second, the reason my results are longer than yours is that it lists ALL the results for the PC5 match including the row that the formula is on. If you want to EXCLUDE the value in the current row from the "options", that will require an array formula and a stringconcatenation function that can take TWO criteria.

I have such a function in my arsenal, but don't want to cloud the water if you're OK with this as a result. Let me know.
 
Upvote 0
You could also try this...

Code:
Sub test()
    Dim rng As Range
    Dim full_range As Range
    Dim stOptions As String
    Dim stPC5 As String
    
    stOptions = ""
    Set full_range = Range("A2", Range("A65536").End(xlDown))
    
    Range("D2").Select
    
    While ActiveCell.Offset(0, -3).Value <> ""
    
        If ActiveCell.Offset(0, -1).Value = 20 Then
                
            stPC5 = ActiveCell.Offset(0, -3).Value
            
            For Each rng In full_range.SpecialCells(xlCellTypeConstants).Cells
            
                If rng.Value = stPC5 Then
                
                    stOptions = stOptions & CStr(rng.Offset(0, 1).Value) & ","
                    
                End If
            
            Next rng
            
            stOptions = Left(stOptions, Len(stOptions) - 1)
            ActiveCell.Value = stOptions
            
        End If
        
        ActiveCell.Offset(1, 0).Select
    
    Wend
End Sub
 
Upvote 0
Good stuff, sandeep. Very nice.

The OP did indicate he wanted a formula, so I presume that means self-running solution.
 
Upvote 0
Thanks jbeaucaire...

I know he asked for the formula. I was wondering how I would do it if I could do it in vba.
 
Upvote 0
Hi jbeaucaire,

THanks for the Formula...I have one more issue...Actuall its the same function ..Plz check the sheet below

the columns no will be Seq Number , Pc5, product Code, Color, Sample Options, Options Sequence number and Options Colors...
Seq Numb PC5 Product Code Color Sample options Options Seq Numb Options Colors
1 63612 63612-0037 red 20 63612-0037, 63612-0038, 63612-0041
2 63612 63612-0038 Blue 0
3 63612 63612-0041 Green 0
4 U6002 U6002-0012 Yellow 0
5 U6002 U6002-0013 Red 0
6 U6002 U6002-0016 Black 20 U6002-0012, U6002-0013, U6002-0016, U6002-0017
7 U6002 U6002-0017 White 0


Could you suggest a formula( as in the case of sample options) Under the column Options sequence number I need the corresponding Seq numbers , for example for Sample options 63612-0037, 63612-0038, 63612-0041 the corresponding seq numbers will be 1, 2, 3 .In the next Column , under options colors I want the corrrsponding Colors whcih is for the example above the cols need to be red,Blue, Green...

Hope u can follow what I am trying to say..I cannot paste an excel sheet ...So :(.
 
Upvote 0
As you can see from my post, it is possible to post up a visual sample of sheet excerpts. Go to this site, install the Excel Jeanie:
http://www.excel-jeanie-html.de/index.php?f=1

Now it's a menu option next to HELP on the Menu bar in Excel.

Mock up what you're talking about, make sure you've represented it accurately. Then highlight the cells that form the sample, and click on the EJH4e on the Menu bar, then click FORUM STANDARD.

That will copy the data into computer memory and you paste it into a forum message.
 
Upvote 0
OK, to do what you're suggesting requires a different UDF than JoinAll. It's from CPearson and it's called StringConcat and it's main benefit is that it can take multiple criteria and can return values from either side of the criterion.

Here's the sample layout from your data:

Excel Workbook
ABCDEFGH
1Seq NumbPC5Product CodeColorSampleOptionsOptions Seq NumbOptions Colors
216361263612-0037red2063612-0037, 63612-0038, 63612-00411, 2, 3red, Blue, Green
326361263612-0038Blue0
436361263612-0041Green0
54U6002U6002-0012Yellow0
65U6002U6002-0013Red0
76U6002U6002-0016Black20U6002-0012, U6002-0013, U6002-0016, U6002-00174, 5, 6, 7Yellow, Red, Black, White
87U6002U6002-0017White0
Sheet3


Here's the code from CPearson:
Code:
Function StringConcat(Sep As String, ParamArray Args()) As String
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat http://www.cpearson.com/excel/StringConcatenation.aspx  '
' This function concatenates all the elements in the Args array,       '
' delimited by the Sep character, into a single string. This function  '
' can be used in an array formula.                                     '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim S As String
Dim N As Long
Dim m As Long
Dim R As Range
Dim numDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean

'''''''''''''''''''''''''''''''''''''''''''
' If no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
    StringConcat = vbNullString
    Exit Function
End If


For N = LBound(Args) To UBound(Args)
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the Args
    ''''''''''''''''''''''''''''''''''''''''''''''''
    If IsObject(Args(N)) = True Then
        '''''''''''''''''''''''''''''''''''''
        ' OBJECT
        ' If we have an object, ensure it
        ' it a Range. The Range object
        ' is the only type of object we'll
        ' work with. Anything else causes
        ' a #VALUE error.
        ''''''''''''''''''''''''''''''''''''
        If TypeOf Args(N) Is Excel.Range Then
            '''''''''''''''''''''''''''''''''''''''''
            ' If it is a Range, loop through the
            ' cells and create append the elements
            ' to the string S.
            '''''''''''''''''''''''''''''''''''''''''
            For Each R In Args(N).Cells
                S = S & R.Text & Sep
            Next R
        Else
            '''''''''''''''''''''''''''''''''
            ' Unsupported object type. Return
            ' a #VALUE error.
            '''''''''''''''''''''''''''''''''
            StringConcat = CVErr(xlErrValue)
            Exit Function
        End If
    
    ElseIf IsArray(Args(N)) = True Then
        
        On Error Resume Next
        '''''''''''''''''''''''''''''''''''''
        ' ARRAY
        ' If Args(N) is an array, ensure it
        ' is an allocated array.
        '''''''''''''''''''''''''''''''''''''
        IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
            (LBound(Args(N)) <= UBound(Args(N))))
        On Error GoTo 0
        If IsArrayAlloc = True Then
            ''''''''''''''''''''''''''''''''''''
            ' The array is allocated. Determine
            ' the number of dimensions of the
            ' array.
            '''''''''''''''''''''''''''''''''''''
            numDims = 1
            On Error Resume Next
            Err.Clear
            numDims = 1
            Do Until Err.Number <> 0
                LB = LBound(Args(N), numDims)
                If Err.Number = 0 Then
                    numDims = numDims + 1
                Else
                    numDims = numDims - 1
                End If
            Loop
            ''''''''''''''''''''''''''''''''''
            ' The array must have either
            ' one or two dimensions. Greater
            ' that two caues a #VALUE error.
            ''''''''''''''''''''''''''''''''''
            If numDims > 2 Then
                StringConcat = CVErr(xlErrValue)
                Exit Function
            End If
            If numDims = 1 Then
                For m = LBound(Args(N)) To UBound(Args(N))
                    If Args(N)(m) <> vbNullString Then
                        S = S & Args(N)(m) & Sep
                    End If
                Next m
                
            Else
                For m = LBound(Args(N), 1) To UBound(Args(N), 1)
                    If Args(N)(m, 1) <> vbNullString Then
                        S = S & Args(N)(m, 1) & Sep
                    End If
                Next m
                For m = LBound(Args(N), 2) To UBound(Args(N), 2)
                    If Args(N)(m, 2) <> vbNullString Then
                        S = S & Args(N)(m, 2) & Sep
                    End If
                Next m

            End If
        Else
            S = S & Args(N) & Sep
        End If
    Else
        S = S & Args(N) & Sep
    End If
Next N

'''''''''''''''''''''''''''''''''''
' Remove the trailing Sep character
'''''''''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
    S = Left(S, Len(S) - Len(Sep))
End If

StringConcat = S

End Function

Press ALT-F11 to open the VBEditor
Click INSERT > MODULE and a window will appear
Paste in all of the code above (you can delete the other code for JoinAll if you added it previously)
ALT-F11 to close the editor and save your sheet.

Now, notice the formulas in the sample above are ARRAY formulas. You have to cut and paste them into your first cell (make whatever changes to ranges) and then press CTRL-SHIFT-ENTER to activate the array. You'll see braces { } appear around your formula. After you enter the array formulas into the first cell for each column, then copy those cells down as far as needed.
 
Upvote 0
Hi jbeaucaire ,

I have a problem...the formula doesnot seem to be running...Let me explain What I did...

I pasted the code as advice by you by using Alt+f11 and closed using the same command...then I pasted the Formulas (copied and pasted) and Pressed CTRL-SHIFT-ENTER ..the formula just stays as text in the cell..Nothing happens..Plz advice...
Note...I am not an advanced user of Excel..Pardon my ignorance...;)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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