Combining Arrays

C

cbrf23

Board Regular
Joined
Jun 20, 2011
Messages
241
Venturing into new territory: Arrays :eeek:

I'm looking for a way to combine two arrays without duplicates.

For example, how do I combine array1 and array2, without duplicating any values, to get array3? In the example below, both have the string "A100" but I only want that to appear in my combined array one time...

Code: 
'Color coded for visual aid only....
[COLOR=royalblue]Array1 = ("A20" ,"A40", "A60", "A80", [COLOR=magenta]"A100"[/COLOR], "A120", "A140", "A160")[/COLOR]
[COLOR=purple]Array2 = ("A50", [COLOR=magenta]"A100"[/COLOR], "A150")[/COLOR]
'How do I combine these to get the following?
Array3 = ([COLOR=royalblue]"A20" ,"A40", [COLOR=#800080]"A50",[/COLOR] "A60", "A80", [COLOR=magenta]"A100"[/COLOR], [COLOR=#800080]"A150"[/COLOR] "A120", "A140", "A160"[/COLOR])
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
cbrf23 said:
I'm looking for a way to combine two arrays without duplicates.
Click to expand...

Hi

You can use for ex. a dictionary object to filter duplicates.

Ex., execute and adapt this code:

Code: 
Sub Test()
Dim Array1 As Variant, Array2 As Variant, Array3 As Variant
Dim j As Long
 
Array1 = Array("A20", "A40", "A60", "A80", "A100", "A120", "A140", "A160")
Array2 = Array("A50", "A100", "A150")
 
With CreateObject("Scripting.Dictionary")
    .comparemode = vbTextCompare ' for non case sensitive comparison
    
    ' add elements from Array1
    For j = LBound(Array1) To UBound(Array1)
        .Add Array1(j), ""
    Next j
 
    ' add elements from Array2
    For j = LBound(Array2) To UBound(Array2)
        If Not .exists(Array2(j)) Then .Add Array2(j), ""
    Next j
    
    ' store the result in Array3
    Array3 = .keys
End With
 
' do something with the array
MsgBox Join(Array3, ", ")

End Sub
 
Upvote 0
Hi PGC

That works great. Is there anyway to organize the items from low to high before creating array3? I'm using the array to create column headings for a number of items that need inspected, and they need to be inspected in the right order.

Currently, Array3 looks like this
A20, A40, A60, A80, A100, A120, A140, A160, A50, A150
Is there anyway to sort it like this?
A20, A40, A50, A60, A80, A100, A150, A120, A140, A160
 
Upvote 0
Hi there,

About how many elements are we going to have total? (before filtering for uniques)

If the total elements are not excessive, you might give Collection a shot.

Mark
 
Upvote 0
I think I may be going about this all wrong, Like I said, I'm new to arrays. I've never really used them before, so maybe that is not the best approach.

Here is what I'm trying to do...The top part of the spreadsheet is how it looks now. The bottom part, is what I'm trying to do through code....



<TABLE dir=ltr border=1 cellSpacing=1 borderColor=#000000 cellPadding=2 width=943><TBODY><TR><TD height=16 width="7%">QTY



</TD><TD height=16 width="7%">200



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">ITEM



</TD><TD height=16 width="7%">FREQ



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">A



</TD><TD height=16 width="7%">1/20



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">B



</TD><TD height=16 width="7%">1/20



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">C



</TD><TD height=16 width="7%">1/50



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">D



</TD><TD height=16 width="7%">1/20



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD><TD height=16 width="7%">



</TD></TR><TR><TD height=164 width="43%" colSpan=6>I'm making 200 (QTY) of each item. Each item has a frequnecy at which it needs to be checked. (Ex. Items "A" ,"B", and "C" get checked 1/20, so I need to check those items at 20, 40, 60, 80, 100, etc all the way to 200 -- Item "B" gets checked 1/50, so I need to check "B" at 50,150, 200 .

I need to create enough columns to check all items, and label the columns appropriately so that each item is checked at the correct interval. I want to insert "-----" if the item doesnt need checked at that interval...​




​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD><TD height=164 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">QTY


​
</TD><TD height=16 width="7%">200


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">ITEM


​
</TD><TD height=16 width="7%">FREQ


​
</TD><TD height=16 width="7%">20


​
</TD><TD height=16 width="7%">40


​
</TD><TD height=16 width="7%">50


​
</TD><TD height=16 width="7%">60


​
</TD><TD height=16 width="7%">80


​
</TD><TD height=16 width="7%">100


​
</TD><TD height=16 width="7%">120


​
</TD><TD height=16 width="7%">140


​
</TD><TD height=16 width="7%">150


​
</TD><TD height=16 width="7%">160


​
</TD><TD height=16 width="7%">180


​
</TD><TD height=16 width="7%">200


​
</TD></TR><TR><TD height=16 width="7%">A


​
</TD><TD height=16 width="7%">1/20


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">B


​
</TD><TD height=16 width="7%">1/20


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">C


​
</TD><TD height=16 width="7%">1/50


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD></TR><TR><TD height=16 width="7%">D


​
</TD><TD height=16 width="7%">1/20


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">-----


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD><TD height=16 width="7%">


​
</TD></TR></TBODY></TABLE>​
 
Last edited:
Upvote 0
Any takers? I'm open to solutions.... I'm trying to read up on multi-dimensional arrays, thinking that may be the way to go. If anyone can help me with this, I would be very grateful.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Add local array to public array - loop redim preserve
Replies
4
Views
613
GeeWhiz7
G
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
B
  • Question Question
Calculating Annualized returns of 1 year, 3 years, 5 years, and 10 years from the data of 132 months/ 11 Years
Replies
0
Views
602
Brandon_333
B
B
  • Solved
Unsure if Calculation is Correct
Replies
3
Views
429
Bazza06
B
Z
  • Question Question
Attrition forecast for next 3 months?
Replies
0
Views
516
zinah
Z

Forum statistics

Threads
1,224,613
Messages
6,179,894
Members
452,948
Latest member
Dupuhini

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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