OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Combining sets of strings using arrays?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Wed Oct 18, 2006 10:30 am    Post subject: Combining sets of strings using arrays? Reply with quote

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
View user's profile Send private message Visit poster's website
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Wed Oct 18, 2006 10:48 am    Post subject: Reply with quote

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
View user's profile Send private message
JohnV
Administrator
Administrator


Joined: 07 Mar 2003
Posts: 9183
Location: Lexinton, Kentucky, USA

PostPosted: Wed Oct 18, 2006 11:03 am    Post subject: Reply with quote

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
View user's profile Send private message
AndrewZ
Moderator
Moderator


Joined: 21 Jun 2004
Posts: 4140
Location: Colorado, USA

PostPosted: Wed Oct 18, 2006 12:45 pm    Post subject: Reply with quote

RickRandom, I like your solution because I don't know StarBasic very well Smile , 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
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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