# Aggregare string variables

#### cuubaa

##### New Member
Hi,
Here is my problem: I have 6 string variables, all of them with values from a finite (and short) list, so that very often there will be more than 2 variables with same value.
I need something like this:
Code:
``````sub agg(A as string, B as string, C as string, D as string, E as string, F as string) as string
agg("VV", "V9", "E1", "VV", "V9") = "VV - A, D; V9 - B, F; E1 - C"``````
I guess it should be simple but I just cannot get it right.
Thanks!

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### DonkeyOte

##### MrExcel MVP
Not sure why you would want to do this in the first place but does this do what you want ?

Code:
``````Function Agg(a As String, b As String, c As String, d As String, e As String)
Dim xtest As Variant: xtest = Array(a, b, c, d, e)
Dim ytest As Variant: ytest = Array("", "", "", "", "")
Dim ztest As Variant: ztest = Array("A", "B", "C", "D", "E")
Dim y As Integer
Dim ans As String
For i = 0 To UBound(xtest)
y = WorksheetFunction.Match(xtest(i), xtest, 0)
If ytest(y) = "" Then
ytest(y) = xtest(i) & " - " & ztest(i) & " "
Else
ytest(y) = ytest(y) & "," & ztest(i) & " "
End If
Next i
For i = 0 To UBound(ytest)
If ytest(i) <> "" Then ans = ans & ytest(i) & ";"
Next i
Agg = Left(ans, InStrRev(ans, " ") - 1)
End Function``````

ie

A1 = "VV", B1 = "V9", C1 = "E1", D1 = "VV", E1 = "V9"

F1 = Agg(A1,B1,C1,D1,E1) -> returns VV - A ,D ;V9 - B ,E ;E1 - C

#### cuubaa

##### New Member
Yes, thank you lasw10 that is what I need.
I have a report that needs to be filled out automatically and I cannot rearrange nothing so the only way to fill out few cells (which are quite small) is to put them in one string, make it font size 7 and text wrap Obviously this data is not of high importance, otherwise I would not agree for such way of presenting it, but it is there to meet traceability requirements found in different quality standards, so it will be only read if something goes really wrong

Replies
0
Views
142
Replies
4
Views
194
Replies
7
Views
169
Replies
4
Views
73
Replies
6
Views
115

1,190,677
Messages
5,982,211
Members
439,768
Latest member
loukrs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back