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
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