Concatenate without duplicates in descending order according to the sum

[Petros Georgilas]

concatenate sort sum descending.xlsx
Hello,
please see attached file
In range c3:c10 we have: USA, Canada, China, USA, France, Greece, Turkey, Canada

In range h5:h12 (the point is that the two ranges may not be next to each other and/or at the same horizontal level. They will have the same number of cells though) we have 6, 3, 10, 8, 0, , 0, 2

I am looking for a way, preferably a User Defined Formula where I would have to define the two ranges, to concatenate the countries in descending order based on the sums of their respective value. In addition, it should exclude countries where the sum of their values is 0.
So in our example the formula should return "USA, China, Canada"
Some comments
1) The values may not be integers
2) The values may not be positive numbers
3) China has the largest single value (10) but it comes second to USA because the sum for USA is 6+8=14 which is larger than 10

If it is any help, you may get some ideas from the thread under the name "Concatenate if other column value is non-blank or non-zero BUT IN DESCENDING ORDER"

Thanks a lot!

[Kaper]

No attachment, so I'll not waste my time to prepare sample sheet for you. (see http://www.excelforum.com/forum-rule...rum-rules.html - section "Want to get your question answered quickly?")

Easy way to get it done properly would be to copy both ranges, give them headers, and then do a pivot table - sorting and filtering result would be a snap.

Yes, it can be done also with formulas, but do not expect especially short version here.
Also doing it without helper cells/column not necesserily would be that easy.

[Petros Georgilas]

You are right Kaper. Sorry!
I uploaded the attachment.
This is just an example. In the actual file, I will perform this action multiple times and probably with values that will change from time to time. So copying the ranges and creating pivot tables every time does not really work. The ideal would be a User Defined Function (i.e. a function created using VBA). I was thinking sth like the solution in the thread under the name "Concatenate if other column value is non-blank or non-zero BUT IN DESCENDING ORDER".
I can create a new sheet where each country will be shown just once and calculate the values using "sumif" and then use the UDF in the thread I mentioned. However, new countries may be added in the future and more importantly the excel is for a friend not very familiar with excel, so I am looking for something more elegant.
Thanks

[Kaper]

Hi Petros,
You can use the function proposed there (in prevoius thread), but add a stage of collating entries first.
For instance, based on xladept approcach, but adding provisions for non-integer and for negative values:

Function CCHiLoX(R As Range, S As Range) As String
Dim P As String, P2 As Double, Q, T, i As Long, earlier As Long
Q = R
T = S
If Not IsNumeric(R(1, 1)) Then Q(1, 1) = 0
With Application.WorksheetFunction
  For i = 2 To S.Rows.Count
    If IsNumeric(R(i, 1)) Then
      If .CountIf(S.Resize(i - 1, 1), S(i, 1)) > 0 Then
        earlier = .Match(S(i, 1), S.Resize(i - 1, 1), 0)
        Q(earlier, 1) = Q(earlier, 1) + Q(i, 1)
        Q(i, 1) = 0
      End If
    Else
      Q(i, 1) = 0
    End If
  Next i
End With
BubbleQ:
For i = LBound(Q) To UBound(Q) - 1
  If Q(i, 1) < Q(i + 1, 1) Then
    P2 = Q(i, 1)
    Q(i, 1) = Q(i + 1, 1)
    Q(i + 1, 1) = P2
    P = T(i, 1)
    T(i, 1) = T(i + 1, 1)
    T(i + 1, 1) = P
    GoTo BubbleQ
  End If
Next i
For i = LBound(Q) To UBound(Q)
  If Q(i, 1) <> 0 Then
    CCHiLoX = CCHiLoX & "," & T(i, 1)
  End If
Next i
CCHiLoX = Right(CCHiLoX, Len(CCHiLoX) - 1)
End Function

PS. Looks much longer, but it's not - Is written here without collapsing several instructions into 1 row with : separator.

[Petros Georgilas]

Thanks a lot Kaper!
It seems to be just what i needed!
I will study in due time and try to understand