# Formula to Copy

#### SIBYCHERIAN

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### jbeaucaire

##### Well-known Member
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.

#### jbeaucaire

##### Well-known Member
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.

#### Sandeep Warrier

##### Well-known Member
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``````

#### jbeaucaire

##### Well-known Member
Good stuff, sandeep. Very nice.

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

#### Sandeep Warrier

##### Well-known Member
Thanks jbeaucaire...

I know he asked for the formula. I was wondering how I would do it if I could do it in vba.

#### SIBYCHERIAN

##### New Member
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 .

#### jbeaucaire

##### Well-known Member
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.

#### jbeaucaire

##### Well-known Member
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.

#### SIBYCHERIAN

##### New Member
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...

Replies
1
Views
111
Replies
0
Views
83
Replies
13
Views
295
Replies
1
Views
152
Replies
1
Views
368

1,191,614
Messages
5,987,690
Members
440,104
Latest member
thigarette

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

### Which adblocker are you using?

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

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