List Distinct Values in One Cell

A

animas

Active Member
Joined
Sep 28, 2009
Messages
396
I need to put list of distict values of a range inside a single cell using commas(,). How to do this using vba?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
lets say the range is A1:A1000

the formula in F1:
=CustomGT(STRINGConcat(", ",A1:A1000),", ")
CTRL+SHIFT+ENTER

first UDF
Code: 
[COLOR=black][FONT=Calibri]Function StringConcat(Sep As String, ParamArray Args()) As Variant[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' StringConcat[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' By Chip Pearson, chip@cpearson.com, www.cpearson.com[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'                  www.cpearson.com/Excel/stringconcatenation.aspx[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' This function concatenates all the elements in the Args array,[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' delimited by the Sep character, into a single string. This function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' can be used in an array formula. There is a VBA imposed limit that[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' a string in a passed in array (e.g.,  calling this function from[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' an array formula in a worksheet cell) must be less than 256 characters.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' See the comments at STRING TOO LONG HANDLING for details.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim S As String[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim N As Long[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim M As Long[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim R As Range[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim NumDims As Long[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim LB As Long[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim IsArrayAlloc As Boolean[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' If no parameters were passed in, return[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' vbNullString.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]If UBound(Args) - LBound(Args) + 1 = 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] StringConcat = vbNullString[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] Exit Function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri]For N = LBound(Args) To UBound(Args)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] ''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] ' Loop through the Args[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] ''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] If IsObject(Args(N)) = True Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     '''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' OBJECT[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' If we have an object, ensure it[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' it a Range. The Range object[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' is the only type of object we'll[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' work with. Anything else causes[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' a #VALUE error.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     If TypeOf Args(N) Is Excel.Range Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         '''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' If it is a Range, loop through the[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' cells and create append the elements[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' to the string S.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         '''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         For Each R In Args(N).Cells[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             If Len(R.Text) > 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 S = S & R.Text & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Next R[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     Else[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         '''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' Unsupported object type. Return[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' a #VALUE error.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         '''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         StringConcat = CVErr(xlErrValue)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Exit Function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri] ElseIf IsArray(Args(N)) = True Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     '''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' ARRAY[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' If Args(N) is an array, ensure it[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     ' is an allocated array.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     '''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     IsArrayAlloc = (Not IsError(LBound(Args(N))) And _[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         (LBound(Args(N)) <= UBound(Args(N))))[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     If IsArrayAlloc = True Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' The array is allocated. Determine[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' the number of dimensions of the[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' array.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         '''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         NumDims = 1[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         On Error Resume Next[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Err.Clear[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         NumDims = 1[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Do Until Err.Number <> 0[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             LB = LBound(Args(N), NumDims)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             If Err.Number = 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 NumDims = NumDims + 1[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Else[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 NumDims = NumDims - 1[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Loop[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         On Error GoTo 0[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         Err.Clear[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' The array must have either[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' one or two dimensions. Greater[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ' that two caues a #VALUE error.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         ''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         If NumDims > 2 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             StringConcat = CVErr(xlErrValue)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Exit Function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         If NumDims = 1 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             For M = LBound(Args(N)) To UBound(Args(N))[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 If Args(N)(M) <> vbNullString Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                     S = S & Args(N)(M) & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Next M[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri]         Else[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' STRING TOO LONG HANDLING[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' Here, the error handler must be set to either[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             '   On Error GoTo ContinueLoop[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             '   or[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             '   On Error GoTo ErrH[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' If you use ErrH, then any error, including[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' a string too long error, will cause the function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' to return #VALUE and quit. If you use ContinueLoop,[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' the problematic value is ignored and not included[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' in the result, and the result is the concatenation[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' of all non-error values in the input. This code is[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' used in the case that an input string is longer than[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ' 255 characters.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             ''''''''''''''''''''''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             On Error GoTo ContinueLoop[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             'On Error GoTo ErrH[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Err.Clear[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             For M = LBound(Args(N), 1) To UBound(Args(N), 1)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 If Args(N)(M, 1) <> vbNullString Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                     S = S & Args(N)(M, 1) & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Next M[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             Err.Clear[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             M = LBound(Args(N), 2)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             If Err.Number = 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 For M = LBound(Args(N), 2) To UBound(Args(N), 2)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                     If Args(N)(M, 2) <> vbNullString Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                         S = S & Args(N)(M, 2) & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                     End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]                 Next M[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             On Error GoTo ErrH:[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     Else[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         If Args(N) <> vbNullString Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]             S = S & Args(N) & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     Else[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     On Error Resume Next[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     If Args(N) <> vbNullString Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]         S = S & Args(N) & Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     On Error GoTo 0[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]ContinueLoop:[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Next N[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' Remove the trailing Sep[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]If Len(Sep) > 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] If Len(S) > 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]     S = Left(S, Len(S) - Len(Sep))[/FONT][/COLOR]
[COLOR=black][FONT=Calibri] End If[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End If[/FONT][/COLOR]
 
[COLOR=black][FONT=Calibri]StringConcat = S[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' Success. Get out.[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Exit Function[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]ErrH:[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]' Error. Return #VALUE[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]'''''''''''''''''''''''''''''[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]StringConcat = CVErr(xlErrValue)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End Function[/FONT][/COLOR]
second UDF:
Code: 
[COLOR=black][FONT=Calibri]Function CustomGT(txt As String, Optional delim As String = " ") As String[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]Dim e[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]With CreateObject("Scripting.Dictionary")[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]  .CompareMode = vbTextCompare[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]  For Each e In Split(txt, delim)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]       If Trim(e) <> "" And Not .exists(Trim(e)) Then .Add Trim(e), Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]  Next[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]  If .Count > 0 Then CustomGT = Join(.keys, delim)[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End With[/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End Function[/FONT][/COLOR]
 
Upvote 0
Only deleimeter ", " is working. No other delimeters are working.

=CustomGT(STRINGConcat("/ ",A1:A1000),", ") is not working. Any fix?
 
Upvote 0
This problem occurs when some rows of range A1:A1000 is hidden. Excel functions usually work with hidden rows. Is it fixable?Thanks.
 
Upvote 0
If not fixable then a function which will take a cell address(ie. B2) as argument will return range B2:B10 (B10 is last non blank cell, so b11 is empty) could solve my case.
 
Upvote 0
nah, hidden rows should not cause a problem ...
well, in any case:
<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=217 border=0><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 106pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=141 height=20>Cell Address:</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=76> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>B2</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Distinct Values:</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2/ 3/ 4/ 6/ 1/ 10/ 11</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>11</TD></TR></TBODY></TABLE>
Cell A2: Input Cell Address
Cell A4: Formula:
=CustomGT(STRINGConcat("/ ",INDIRECT(A2):OFFSET(INDIRECT(A2),COUNTA(INDIRECT(A2):OFFSET(INDIRECT(A2),1000,0))-1,0)),"/ ")
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
TextToColumns, VBA to uncheck "Treat consecutive delimiters as one"
Replies
5
Views
146
Joe4
Joe4
J
  • Question Question
Extract list of cells from one column in to one cell and separate by a comma
Replies
8
Views
157
Peter_SSs
Peter_SSs
D
  • Question Question
Generating a list of Dates using nested array inside SEQUENCE function?
Replies
2
Views
276
Cubist
Cubist
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
269
drom
drom
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
226
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

Share this page

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