VBA for Concatenating Cells in a Specified order found in a list

[MrBartlett]

Hello all,

I have this code so far, for concatenating values in a range:

Function Concatenatecells(ConcatArea As Range) As String
  For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & ","): Next
  Concatenatecells = Left(nn, Len(nn) - 1)
End Function

Assuming I have this data:

How can I get it to concatenate "apples, pears" as "pears, apples", for example?

Thanks!

 

[MrBartlett]

Bump. Please help! Thanks!

 

[Logit]

.
Here is a different approach. Hopefully you can use it.

Paste this formula in C2 and drag down the column as far as required.

=IF(D2<>"",D2,"")&IF(E2<>"",", "&E2,"")&IF(F2<>"",", "&F2,"")&IF(G2<>"",", "&G2,"")

 

[Greg Truby]

How can I get it to concatenate "apples, pears" as "pears, apples", for example?

Function RevConcatCells(ConcatArea As Range) As String

    Const c_strSep As String = ", "

    Let v = ConcatArea
    
    For i = UBound(v, 1) To LBound(v, 1) Step -1
        For j = UBound(v, 2) To LBound(v, 2) Step -1
            Let rcc = rcc & v(i, j) & IIf(Len(v(i, j)) <> 0, c_strSep, "")
        Next j
    Next i
    
    If Len(rcc) > 0 Then
        Let RevConcatCells = Left$(rcc, Len(rcc) - Len(c_strSep))
    Else
        Let RevConcatCells = vbNullString
    End If
    
End Function

 

[Logit]

.
I misread your question. Disregard my previous post.

However, thank you for the question as I learned something new today ! Cheers !

 

[MrBartlett]

Function RevConcatCells(ConcatArea As Range) As String

    Const c_strSep As String = ", "

    Let v = ConcatArea
    
    For i = UBound(v, 1) To LBound(v, 1) Step -1
        For j = UBound(v, 2) To LBound(v, 2) Step -1
            Let rcc = rcc & v(i, j) & IIf(Len(v(i, j)) <> 0, c_strSep, "")
        Next j
    Next i
    
    If Len(rcc) > 0 Then
        Let RevConcatCells = Left$(rcc, Len(rcc) - Len(c_strSep))
    Else
        Let RevConcatCells = vbNullString
    End If
    
End Function

Thanks! I'm encountering an issue though, and I think it's going in reverse order in pairs of two or something.

Paste this in C1:


[TABLE="width: 805"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Concat[/TD]
[TD]chicago[/TD]
[TD]new york[/TD]
[TD]los angeles[/TD]
[TD]san francisco[/TD]
[TD][/TD]
[TD]order[/TD]
[TD]#[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]guavas, pears, melons, apples[/TD]
[TD]apples[/TD]
[TD]melons[/TD]
[TD]pears[/TD]
[TD]guavas[/TD]
[TD][/TD]
[TD]los angeles[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]grapes, bananas, oranges[/TD]
[TD]oranges[/TD]
[TD][/TD]
[TD]bananas[/TD]
[TD]grapes[/TD]
[TD][/TD]
[TD]san francisco[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]chicago[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]new york[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

.
I misread your question. Disregard my previous post.

However, thank you for the question as I learned something new today ! Cheers !

Haha, glad to help inspire. Thanks for your time!

 

[Greg Truby]

I think it's going in reverse order in pairs of two or something.

Either you've had a bit too much rum punch, sir, or else I ain't had quite enough... To me the results look exactly like the results you were requesting.

 

[MrBartlett]

Either you've had a bit too much rum punch, sir, or else I ain't had quite enough... To me the results look exactly like the results you were requesting.

I'm thinking you need to add more rum to yours :D

So it seems like it's concatenating backwards...column G, F, E, D is the order it seems.

guavas, pears, melons, apples should be pears, guavas, apples, melons

AND grapes, bananas, oranges should be grapes, bananas, oranges

These orders are of course determined by the rank-order corresponding with each city, in the rightmost column.

 

[Rick Rothstein]

These orders are of course determined by the rank-order corresponding with each city, in the rightmost column.

Will the listed cities always be in the same order with the numbers in Column J changing or with the numeric list always be 1, ,2, 3, etc. and the city names mixed to the correct order? In other words, if you wanted the order to be San Francisco, Los Angeles, Chicago, New York, what would Columns I and J look like?

 

[Rick Rothstein]

Will the listed cities always be in the same order with the numbers in Column J changing or with the numeric list always be 1, ,2, 3, etc. and the city names mixed to the correct order? In other words, if you wanted the order to be San Francisco, Los Angeles, Chicago, New York, what would Columns I and J look like?

Actually, if you don't mind listing the arguments by cities, you could use this UDF...

[table="width: 500"]
[tr]
	[td]Function OrderedConcat(ParamArray Cities()) As String
  Dim X As Long, Col As Long, ColNums As String
  For X = LBound(Cities) To UBound(Cities)
    Col = Rows(1).Find(Cities(X), , , xlWhole, , , False, , False).Column
    If Len(Cells(Application.Caller.Row, Col).Value) Then ColNums = ColNums & " " & Col
  Next
  OrderedConcat = Join(Application.Index(Cells, Application.Caller.Row, Split(Application.Trim(ColNums))), ", ")
End Function[/td]
[/tr]
[/table]

So, for your posted example, you would put this formula in cell C2 and then copy it down...

=OrderedConcat(I$2,I$3,I$4,I$5)