| View previous topic :: View next topic |
| Author |
Message |
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
 Posted: Wed Oct 18, 2006 10:30 am Post subject: Combining sets of strings using arrays? |
 |
|
Is there a way to combine two sets of strings---maybe using arrays?
For example, set A is red, orange, yellow, and set B is box, ball, brick. Is there a way to get set A*B with the 9 combinations? |
|
| Back to top |
|
 |
RickRandom
Super User
Joined: 27 Jan 2006
Posts: 1082
Location: UK
|
| Posted: Wed Oct 18, 2006 10:48 am Post subject: |
|
|
Not really sure what you want but...
Put set A in cells A2, A3, A4.
Put set B in Cells B1, C1, D1.
In B2, put:
=$A2&" "&B$1
and copy or fill to cells B2 to D4.
This will give red box, etc, all 9 combinations. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8983
Location: Lexinton, Kentucky, USA
|
| Posted: Wed Oct 18, 2006 11:03 am Post subject: |
|
|
Likewise not sure what you want but this macro displays all 9 combinations using two arrays. | Code: | Sub Main
A = Array("Red", "Orange", "Yellow")
B = Array("box", "ball", "brick")
For outer = 0 to uBound(A)
For inner = 0 to uBound(B)
s = s & A(outer) & " " & B(inner) & Chr(13)
Next inner
Next outer
Msgbox s
End Sub |
|
|
| Back to top |
|
|
AndrewZ
Moderator
Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA
|
| Posted: Wed Oct 18, 2006 12:45 pm Post subject: |
|
|
RickRandom, I like your solution because I don't know StarBasic very well  , but I wanted the results to be in one column.
JohnV, I tried to make your code as a function that I can use within the spreadsheet, but I am having trouble. When I use a string, there are no line breaks. When I use return an array, I was only getting the last element in the array, but then I changed the code and am getting nothing. (I tried with and without the CTRL+SHIFT+ENTER.)
| Code: |
'Expects each input to be a single column of cells (with any length of rows)
Function CombineArrays(a,b)
Dim Outer As Integer
Dim Inner As Integer
Dim S As String
Dim I As Integer
i = 0
Dim size
'size = (uBound(A, 1) - lBound(A, 1)) * (uBound(B, 1) - lBound(B, 1))
Size = uBound(A, 1) * uBound(B, 1)
Dim Result(Size) As String
For outer = lBound(A,1) to uBound(A, 1)
For inner = lBound(B,1) to uBound(B, 1)
s = s & A(outer, 1) & " " & B(inner, 1) & Chr(13)
Result(i) = a(outer, 1) & " " & B(inner, 1)
i = i + 1
Next inner
Next outer
'ShowArray(result())
ConcatenateArrays= Result()
'ConcatenateArrays= s
End function
|
The following function is very similar, but this one will return the full array properly.
| Code: | Function ReturnArray()
Dim result(1 to 3) as String
Result(1) = "One"
Result(2) = "Two"
Result(3) = "Three"
ReturnArray=Result()
End Function |
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
