looping through multiple ranges and concatenating based on condition

Question

I have four 1 column ranges with the same row count: Country, Name, Category, Amount. The Country, Name and Category ranges have string values, the Amount range has doubles. the problem I'm facing is this: As long as the Amount is above or below certain value, I need to get a string of all countries under each category and each name.

Sample table:

Country Name Category Amount
croatia Jon Blue 14
norway Jon Blue 23
poland Rob Green 10
egypt Eva Green 8
canada Eva Pink 32
brazil Rob Pink 25
switzerland Rob Pink 35
russia Jon Pink 27
sweden Rob Black 32
togo Rob Black 13
benin Esther Violet 24
morroco Jon Yellow 36
romania Eva Yellow 35
usa Eva Yellow 38
japan Rob Yellow 34

For Amount > 20, the correct string result for each category would be:

Blue: Jon: norway(23)
Pink: Eva: canada(32), Rob: brazil(25), switzerland(35)
Yellow: Jon: morroco(36), Eva: romania(35), usa(38), Rob: japan(34)
etc.

Any idea how to approach this problem? Don't have a code written as I couldn't figure out where to even start. Was thinking around multidimensional arrays but that's beyond my coding abilities...any help much appreciated

Why not just filter the data, even if it means first copying them to a single block/table? — Andy G
well the filter would get me the list of values in a table which I would still need to somehow concatenate to get to the correct string. I guess the question is if there is a way to do some type of loop "for each category, for each name, concatenate countries where value is above or below the value... — whada

Tony M Tony M · Accepted Answer · 2017-07-24T22:25:40

The code below will get you close to what you want. Here's the output:

Blue: Jon: norway(23), 

Pink: Eva: canada(32), Pink: Rob: brazil(25), Pink: Rob: switzerland(35), Pink: Jon: russia(27), 
Black: Rob: sweden(32), 
Violet: Esther: benin(24), 
Yellow: Jon: morroco(36), Yellow: Eva: romania(35), Yellow: Eva: usa(38), Yellow: Rob: japan(34),

The only thing you need to do is suppress things like the blank second line, the duplicate color output and the ", " at the end of each line. But I didn't want to take away all your fun! If you have trouble with that, post again.

Option Explicit
Sub test()
Dim r As Range, colorR As Range, resultR As Range
Dim amountR As Range, countryR As Range, nameR As Range
Dim color As String, name As String, country As String, amount As String
Set resultR = Range("A19")
Set r = Range("C2")
Set colorR = r
While r <> ""
  While r = colorR
    Set amountR = r.Offset(0, 1)
    Set nameR = r.Offset(0, -1)
    Set countryR = r.Offset(0, -2)
    If amountR > 20 Then
      If color = r & ": " Or color = "" Then color = "" Else color = r & ": "
      If name = nameR & ": " Then name = "" Else name = nameR & ": "
      country = countryR & "("
      amount = amountR & "), "
      resultR = resultR & color & name & country & amount
    End If
    Set r = r.Offset(1, 0)
  Wend
  If resultR <> "" Then
    resultR = Left(resultR, Len(resultR) - 2)
    Set resultR = resultR.Offset(1, 0)
  End If
  Set colorR = r
Wend
End Sub

looping through multiple ranges and concatenating based on condition

1 Answers