Comparing arrays....

**Bedlam** · 11-02-2011, 11:15 AM

I have two arrays that store information from two different workbooks.

Please Login or Register  to view this content.

This is how the information is stored, and it stores just fine, but what I need to do now is compare the two arrays and have a check that runs ThisMonthArray and checks each element of that array vs the entire array of LastMonthArray. I've tried do do this with for loops but I can not get it to compare correctly... Any help on this would be greatly appreciated.

**Kyle123** · 11-02-2011, 11:17 AM

What is the goal of this, what are you checking for and what do you want to happen should/n't be found?

**Bedlam** · 11-02-2011, 11:19 AM

I'm comparing accounts from two different workbooks and these arrays when compared should do one of three things either the account is still active, it has been deleted, or it is a new account, sorry I forgot to include this information. The instance of the account being deleted ( its in the last month but not in this month) The instance of it being new ( its in thismontharray and not last month's array) and if its still active its in both arrays.

**Kyle123** · 11-02-2011, 11:21 AM

Can you post a sample of your workbook after the import?

Also depending on your GetData code, this doesn't look particularly efficient

**Bedlam** · 11-02-2011, 11:52 AM

http://img839.imageshack.us/img839/5843/blehb.png is the output of my worksheet

Please Login or Register  to view this content.

**Kyle123** · 11-02-2011, 11:59 AM

Your macro really doesn't help since it's just calling another Macro, this is where the inefficiency will be.

I'm completely confused, there are only 2 columns. are you saying you need to check whether there are any account numbers in the left that are not in the right and vice versa?

Please post a workbook, not an image

**Bedlam** · 11-02-2011, 12:05 PM

I'm sorry for the confusion, I need to take the column to the left and check if the account number is in the column to the right. If it is then the account is still an active account. If it isn't then it is a new account. If the column to the right has a account number that the column to the left doesn't have then it is a deleted account. I hope that helps...

**Kyle123** · 11-02-2011, 12:10 PM

Not really, please post a workbook.

What do you want to happen when these have been identified?

**Bedlam** · 11-02-2011, 12:12 PM

When these have been identified I'm going to generate a report that shows which accounts have been deleted, added, or still active. I can't really post the workbook where the information is being pulled from... but i can probably post my entire macro code if you would like to see that...

**Kyle123** · 11-02-2011, 12:14 PM

Just mock up a sample workbook which shows your data after it is pulled in, how you would like it displaying and your importing code.

**Bedlam** · 11-02-2011, 12:18 PM

realistically now that i think about it... I just need a function that i can input the two arrays into and it check each element vs the other array. so something to the extent of

Please Login or Register  to view this content.

so when its used i could just switch the two arrays in the prompt
getArrayStuff(ThisMonthArray,LastMonthArray)
getArrayStuff(LastMonthArray,ThisMonthArray)
that way its checking the accounts vs each other
I will then be able to see if the account has either been added or deleted based on its existence in thismontharray and lastmontharray

**TMS** · 11-02-2011, 12:36 PM

Can we see a sample workbook? If you're just comparing accounts in two columns, you could do that with helper columns or conditional formatting or both. You'd use COUNTIF or VLOOKUP to do the checks.

Regards

**Bedlam** · 11-02-2011, 12:40 PM

So this is pseudo what I have in mind... it doesn't seem to wanna work but here is a go...

Please Login or Register  to view this content.

**Bedlam** · 11-02-2011, 12:41 PM

i've included a picture of my workbook... the image in the link above is the current workbook that contains the macro and pulls the information from the other two workbooks, i'm not sure what you want me to show you... sorry i'm not a new programmer but to vba i am...

**davegugg** · 11-02-2011, 12:42 PM

Usually you can just use a Vlookup formula for such:

=IF(ISERROR(VLOOKUP(Act#,LastMonthAct#s,1,0)),"New Account", "Old Account")

However, if you want to compare each element in two arrays in vba then:

Please Login or Register  to view this content.

Keep in mind this is VERY generic since you have not posted any sample workbook or problem.

**romperstomper** · 11-02-2011, 12:45 PM

Originally Posted by Bedlam

i've included a picture of my workbook...

FYI, a picture is of very little use to us, since it means that we have to recreate the actual workbook in order to test. As a general rule, you will get better help with an actual workbook as most of us are too busy to spend time recreating workbooks for every post we deal with.

**Bedlam** · 11-02-2011, 12:46 PM

and how exactly do I get vba to use an excel formula... I've only ever coded in other languages that don't go through excel... =IF(ISERROR(VLOOKUP(Act#,LastMonthAct#s,1,0)),"New Account", "Old Account") would be great to use, if i knew how to get vba to use it.

**Bedlam** · 11-02-2011, 12:47 PM

hmm let me see about making mockup workbooks

**Bedlam** · 11-02-2011, 12:58 PM

Ok here are two mockup examples there is more in the other workbooks that i'm using, but i'm only concerned with the first two rows. The comparison should only deal with the account number though. So when it parses the information from both workbooks I'm wanting to check the account numbers vs each other. It being in mockup1 and not in mockup2 would mean the account was deleted. An account being in mockup2 and not in mockup1 would mean its a new account. If they both contain the account number then its still an active account. Maybe that will help... sorry everyone The end all result would be a list of deleted accounts and a list of new accounts to be generated in a report.