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]

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?

So, both the order of the cities in I and the order of the ranks in J can vary. So, someone could change the ranks of the cities, they could change the cities themselves, and they could change what cities appear in column I in any order (Sacramento instead of Los Angeles, for example).

If I wanted the order to be as you indicated above, then the ranks will be as follows:

San Francisco - 1
Los Angeles - 2
Chicago - 3
New York - 4

But, they could be formatted like this:

Chicago - 3
San Francisco - 1
New York - 4
Los Angeles - 2

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

[TABLE="width: 500"]
<tbody>[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]
</tbody>[/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)

The above could work if it doesn't require column J to list the numbers in order of 1-4. But, it seems this is not the case?

In essence, if the data in I:J were as below:

Chicago - 3
San Francisco - 1
New York - 4
Los Angeles - 2

Would OrderedConcat(I$2,I$3,I$4,I$5) rank Chicago first, then San Francisco, or would it rank San Francisco first, then Los Angeles?

 

[Rick Rothstein]

The above could work if it doesn't require column J to list the numbers in order of 1-4. But, it seems this is not the case?

In essence, if the data in I:J were as below:

Chicago - 3
San Francisco - 1
New York - 4
Los Angeles - 2

Would OrderedConcat(I$2,I$3,I$4,I$5) rank Chicago first, then San Francisco, or would it rank San Francisco first, then Los Angeles?

The order you list the cities in inside the function call is the order the data will be concatenated in (Column J is not used by my code). You can type the quoted city names in directly like this..

=OrderedConcat("San Franscisco","Los Angeles","Chicago","New York")

or you can reference the city names through the cell addresses for the cities (make the row designation absolute so that it does not change when you copy the formula down)...

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

If you want me to order the cities by the numbering in Column J, I can write a macro to do that if you want instead, just let me know.

 

[Greg Truby]

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

Well, it's a quarter 'til five on Friday, so margarita time will be here soon.


I quite missed that your sequence was based on the cities, I just thought you were wanting to list the items bottom-to-top and right-to-left.


You're in extremely capable hands with Rick, so I'm gonna bow out.

 

[MrBartlett]

The order you list the cities in inside the function call is the order the data will be concatenated in (Column J is not used by my code). You can type the quoted city names in directly like this..

=OrderedConcat("San Franscisco","Los Angeles","Chicago","New York")

or you can reference the city names through the cell addresses for the cities (make the row designation absolute so that it does not change when you copy the formula down)...

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

If you want me to order the cities by the numbering in Column J, I can write a macro to do that if you want instead, just let me know.

Yes, putting the numbers in would be fine if I was the only one manipulating the data, but there are others using my worksheet. Thus, as you mentioned, can you please help me out with a macro to do that (based on the numbers in J)? Thanks in advance.

P.S. if I want to get to a level of VBA knowledge that enables me to come up with these kinds of solutions, how much studying do you think that may involve? So far, I've studied for about 20 hours these past couple of months.

Well, it's a quarter 'til five on Friday, so margarita time will be here soon.


I quite missed that your sequence was based on the cities, I just thought you were wanting to list the items bottom-to-top and right-to-left.


You're in extremely capable hands with Rick, so I'm gonna bow out.

How were those margaritas?

And no problem, thanks for your help!

 

[MrBartlett]

If you want me to order the cities by the numbering in Column J, I can write a macro to do that if you want instead, just let me know.

Just bumping this. Thanks Rick