I can conjugate two or more columns but is there a way to do this in alphanumeric order of the data sets.
ie: Row 1 Column 1 "Twain, Mark" Column 2 " Williams, Sue" Column 3 " Brown, Ron"
Row 2 Column 1 "Williams, Sue" Column 2 " Brown, Ron" Column 3 " Twain, Mark"
How do I get to " Brown, Ron, Twain, Mark, Williams, Sue" in column 4 in both rows
Excel 2007 conjugate of column data
-
-
-
This can be done using a UDF.
The UDF is
Code
Display MoreFunction SortAndJoin(r As Range) As String Dim x, y, z, i As Long, ii As Long x = r ReDim y(LBound(x, 2) To UBound(x, 2)) For i = LBound(y) To UBound(y) y(i) = Split(x(1, i), ", ")(0) Next z = y ArraySort y, LBound(y), UBound(y) For i = LBound(y) To UBound(y) ii = Application.Match(y(i), z, 0) If SortAndJoin = vbNullString Then SortAndJoin = x(1, ii) Else SortAndJoin = SortAndJoin & ", " & x(1, ii) End If Next End Function Public Sub ArraySort(x As Variant, inLow As Long, inHi As Long) Dim y, z, i As Long, ii As Long i = inLow: ii = inHi y = x((inLow + inHi) \ 2) While (i <= ii) While (x(i) < y And i < inHi) i = i + 1 Wend While (y < x(ii) And ii > inLow) ii = ii - 1 Wend If (i <= ii) Then z = x(i): x(i) = x(ii): x(ii) = z i = i + 1: ii = ii - 1 End If Wend If (inLow < ii) Then ArraySort x, inLow, ii If (i < inHi) Then ArraySort x, i, inHi End Sub
If your data is in columns A to C starting in row 1 then put this in D1 and copy down
=SortAndJoin(A1:C1)
Note that both the Function and the Public Sub is required.
-
works like a charm
thank you -
You're welcome
-
A small problem in the solution. I get a #value! error is one or more of the three columns is blank.
[tr]
Also when two persons have the same last name it repeats the first of the couple
ie [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[/tr]
[TD="width: 134"]Krisch, Greg[/TD]
[TD="width: 176"]Krisch, Valerie[/TD]
[TD="width: 176"]Porter, Linda[/TD]
[TD="width: 594"]Krisch, Greg, Krisch, Greg, Porter, Linda[/TD]
[/TABLE]
Sorry for the trouble -
-
Ronbro2018 not yet test Maybe like This
=ifError(SortQ(a1:c1),"")Code
Display MoreFunction SortQ(r as range) as String dim a,e,sp,i&: a = r.value with CreateObject("System.Collections.ArrayList") for each e in a if len(e) and instr(e,",") then sp = split(e,",") For i = 0 to ubound(sp) .add sp(i) next i end if next e .Sort SortQ = join(.Toarray,",") .Clear end with end Function
-
I get nothing when I run this ??? ie: blank cell
The original solution works great till the last names of persons in the same row match - then when it concatenates them it repeats the first person with the same last name twice
ie: Column 1 - Heart, Jim Column 2 - Black, Rick Column 3 Hart, Jill ends up to be Black, Rick, Heart, Jim, Heart,Jim ???? -
Or try use sortedlist
Code
Display Morefunction Sortx(r as range) dim ary,sp,i&,j& With CreateObject("System.Collections.SortedList") If r.count > 1 then ary = join(application.transpose(application.transpose(r.value)),",") sp = split(ary,",") For i = 0 to Ubound(sp) .Add sp(i), .count Next for j = 0 to .count-1 sortx = iif(sortx="",.getkey(j),sortx & "," & .getkey(j)) next end if End With End Function
-
Added this function to module
I then entered =sortx(B2:D2) into cell e2 but get #VALUE! error each of cells B2 to d2 contain names ie: LastName,FirstName -
Or maybe in excel 2007 not support arraylist
try again but still not to test sorri use handphone
Code
Display MoreFunction sortX(r as range) as string dim x,y,i&,t$,ii&,sp if r.count >= 1 then With application x =join(.transpose(.transpose(r.value)),",") end with sp = split(x,",") for i = 0 to Ubound(sp) For ii = i + 1 to ubound(sp) If sp(i) > sp(ii) then t = sp(i) sp(i) = sp(ii) sp(ii) = t end if next ii Next i sortX = join(sp,",") End if end Function
-
-
Now we are getting somewhere
[tr]
it is sorting first names in order then last names in order
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[/tr]
[TD="width: 138"]Williams, Susan[/TD]
[TD="width: 161"]Good, Karen[/TD]
[TD="width: 357"]Lane, Robin[/TD]
[TD="width: 443"] [/TD]
[/TABLE]
result was "Karen, Robin, Susan,Good,Lane,Williams"Even If I got Last names in order then First names in order i could live with it ie: "Good,Lane,Williams,Karen, Robin, Susan"
but best would be " Good,Karen, Lane, Robin, Williams, Susan"
thanks for your help
much appriciated -
because theare space I HAS TES 3 FUNCTION WORK FOR ME
1.using bubble SortCode
Display MoreFunction Sortx(r As Range) As String Dim x, y, i&, t$, ii&, sp If r.Count >= 1 Then With Application x = Join(.Transpose(.Transpose(r.Value)), ",") End With sp = Split(x, ",") For i = 0 To UBound(sp) For ii = i + 1 To UBound(sp) If Trim(sp(i)) > Trim(sp(ii)) Then t = Trim(sp(i)) sp(i) = Trim(sp(ii)) sp(ii) = t End If Next ii Next i Sortx = Join(sp, ",") End If End Function
USING sortedlist
Code
Display MoreFunction SortP(r As Range) Dim ary, sp, i&, j&, v With CreateObject("System.Collections.SortedList") If r.Count > 1 Then ary = Join(Application.Transpose(Application.Transpose(r.Value)), ",") sp = Split(ary, ",") For Each v In sp .Add Trim(v), .Count Next v For j = 0 To .Count - 1 If SortP = "" Then SortP = .getkey(j) Else SortP = SortP & "," & .getkey(j) Next End If End With End Function
using arraylist
Code
Display MoreFunction SortQ(r As Range) As String Dim a, x1, e, sp, i&: a = r.Value With CreateObject("System.Collections.ArrayList") With Application x1 = Join(.Transpose(.Transpose(r.Value)), ",") End With sp = Split(x1, ",") For Each e In sp .Add Trim(e) Next e .Sort SortQ = Join(.Toarray, ",") .Clear End With End Function
-
tes this file
-
ok looks good
thanks
Ron
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!