List Distinct Values in One Cell

[animas]

I need to put list of distict values of a range inside a single cell using commas(,). How to do this using vba?

 

[animas]

in ur above example if you keep formula unchanged n hide rows of value 10 n 11 and then run any other macro you will see the error.

 

[Ganjin]

wow, you were right in saying that any macro would cause the problem ...

hmm, the problem seems to lie with the StringConcat function ...

okay, found a smaller function that does the same thing (minus, I hope, the error)

try this formula instead:
=CustomGT(AConcat(INDIRECT(A2):OFFSET(INDIRECT(A2),COUNTA(INDIRECT(A2):OFFSET(INDIRECT(A2),1000,0))-1,0),"/ "),"/ ")

AConcat Function:

Function ACONCAT(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
   Dim y As Variant

   If TypeOf a Is Range Then
       For Each y In a.Cells
           ACONCAT = ACONCAT & y.Value & sep
       Next y
   ElseIf IsArray(a) Then
       For Each y In a
           ACONCAT = ACONCAT & y & sep
       Next y
   Else
       ACONCAT = ACONCAT & a & sep
   End If
   ACONCAT = Left(ACONCAT, Len(ACONCAT) - Len(sep))
End Function

 

[animas]

=CustomGT(ACONCAT("/ ",A1:A1000),"/ ") worked nicely with hidden rows and macro clicks. Issues resolved.

Thanks for your help. I can use above 2 functions in several cases.

 

[animas]

How can I apply conditional concatenation here? I will provide (concat delimiter, concat range, criteria range1, criteria1) as argument.

Example:
=ConcatIf("/ ", A1:A100, B1:B100, "Yes")

How can I create a function like this which works with CustomGT function?

 

[Ganjin]

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Yes</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A/ B/ A/ </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 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>C</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 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>B</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 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>C</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 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>A</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Yes</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Formula, Cell C1:
=ACONCAT(IF(B1:B1000="YES",A1:A1000&"/ ",""))
Ctrl+Shift+Enter

 

[animas]

Thanks again.
=CustomGT(ACONCAT(IF(B1:B1000="YES",A1:A1000&"/ ",""),"/ ")
with Ctrl+Shift+Enter is working great.

To make it future proof i was trying multiple conditions like
=CustomGT(ACONCAT(IF(AND(B1:B1000="YES",C1:C1000>=20),A1:A1000&"/ ",""),"/ ")
which is not working for me. Any alternative?

 

[Ganjin]

=CustomGT(ACONCAT(IF((B1:B1000="YES")*(C1:C1000>=20),A1:A1000&"/ ","")),"/ ")
Ctrl+Shift+Enter

 

[animas]

I added three conditions and it works fine.
Can you pls explain briefly why AND(..,..) didn't work and how ()*() is working here?

 

[Ganjin]

hmm, AND() returns either true or false, which is useless when dealing with arrays.

on the other hand, multiplying two arrays together, like:
(B1:B4="YES")*(C1:C4>=20)
would lead to something like:
{True;False;True;False}*{True;False;True;False}
and simplifies to something like this:
{1;0;1;0}

which in a formula like this:

=CustomGT(ACONCAT(IF((B1:B4="YES")*(C1:C4>=20),A1:A4&"/ ","")),"/ ")
​

becomes:
=CustomGT(ACONCAT(IF({1;0;1;0},A1:A4&"/ ","")),"/ ")
then becomes (where A1:A4 equals 1,2,2,1 respectively):
=CustomGT(ACONCAT(IF({1;0;1;0},{"1/";"2/";"2/";"1/"},"")),"/ ")
note the "0" in the {1;0;1;0} indicates a FALSE and the "1" indicates a TRUE, therefore we get:
=CustomGT(ACONCAT({"1/";"";"2/";""}),"/ ")
then the other functions take hold and you get
1/ 2

hmm, hope that made some sense?
if not, highly recommend looking into the SumProduct function (wonderful function) which should (while learning how great it is) teach you quite a bit more about this kind of thing

 

[Ganjin]

opps, sorry for the double post