Need help with Array and For Loop Please

[Xceller]

Thanks for looking into this. Any comments and help would be much appreciated.

3 sheets contain data to be compared and out reports to the 4th sheet. This is the layout of the 3 data sheets:

----Title1-----Title2-----Acct-----Description
----ABC-------NKG------101------Euip1-----
----NBC-------NOG------102------Euip2-----

I would like to compare the Acct# in column 3 for all data sheets and find the matched ones and un-matched ones
and build 2 strings and output them in Sheet("RPT"), column1 and column2

Below is my code *error out on this line " If intArr(i, 3) = enrArr(j, 3) And enrArr(j, 3) = indArr(k, 3) Then "

Please Login or Register  to view this content.

[xladept]

Maybe:

Please Login or Register  to view this content.

[millz]

You are looping through enrArr 3 times. enrArr has the largest index, so when applied to a smaller array intArr and indArr, error occurs.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[xladept]

@millz

That's not the way it works - each for loop passes all of its variables - so the k loop runs entirely each time the j loop increments which, in turn, runs entirely each time the i loop increments.

[millz]

@millz

That's not the way it works - each for loop passes all of its variables - so the k loop runs entirely each time the j loop increments which, in turn, runs entirely each time the i loop increments.

He has intArr dim'ed with 155 rows of data and indArr with 176 rows, both has lesser rows compared to enrArr. Using UBound of enrArr on either of them would definitely throw an error, hence the error occurring on the If statement.

Please Login or Register  to view this content.

[AB33]

"I would like to compare the Acct# in column 3 for all data sheets"

I agree with Millz
The 3 loops apply to enrArr and the OP intention was to loop through 3 arrays, not to nest the enrArr array 2 times.

[Xceller]

Hi everyone - thanks for your comments and help. I tried millz's suggestion and I got the Mated items printed, but got a Subscript Out Of Range on this line: ArrUnMatched(d, 1) = enrArr(j, 3) for the UnMatch ones. Any thoughts?

[millz]

At second look, that's because the matched and unmatched arrays are dim'ed with size of enrArr, i.e. 180. Your total of 3 For loops will make the code loop for 155 * 180 * 176 times, so the moment you have more than 180 mismatches, the error occurs.

Try changing to this to redim the array each time there is a new found.

Please Login or Register  to view this content.

[Xceller]

@millz - Still got a Subscript Out Of Range on this Line: ReDim Preserve ArrUnMatched(1 To d, 1 To 1)

Please Login or Register  to view this content.

[millz]

Try removing the parenthesis

Please Login or Register  to view this content.

[Xceller]

Now it errors out on this line: ReDim Preserve ArrMatched(1 To c, 1 To 1)

[xladept]

Try my suggestion of Post#2 - why don't you??

[Xceller]

I tried removing the parenthesis and rediming it after the c counter, but it still error out on : ReDim Preserve ArrMatched(1 To c, 1 To 1) with "Type Mismatch".
Here is the code:

Please Login or Register  to view this content.

[millz]

Sorry I just realise it's because ReDim Preserve can only be used on the last dimension.

Try this

Please Login or Register  to view this content.

Edit: You might want to try on a smaller set of data first. I think you will be getting too many mismatches.

[Xceller]

I changed the data to just a few rows and narrowed down to just one column for each sheet, but still getting "Type mismatch"

Please Login or Register  to view this content.

[Xceller]

I was also thinking just combining the 3 ranges from the 3 sheets in one big range with 3 dimensions, something like Dim allArr(1 to 156, 1 to 181, 1 to 177), but I don't know how to assign the ranges to the dimensions accordingly.

Please Login or Register  to view this content.

[millz]

Sorry it's kind of weird when I was testing it a little. It seems we need a ReDim to "kick start" it. Also made a mistake previously on the UBound. Try this.

Please Login or Register  to view this content.

[millz]

Just saw the post on the second page. On second thought, I don't know if this is the best way to achieve what you are trying to.

I just re-read your first post and have questions. You mentioned you want to compare the acct# across 3 worksheets, so you are trying to get the acct# that exists in all 3 worksheets in a column? Then what about those that do not exist in all 3? In the code it seems you are only taking the acct# from ENR worksheet if it doesn't exist in the other 2, so ENR is the "main" list?

[Xceller]

I still want both lists ( matched and unmatched ). Originally, I thought ENR has the longest list among the other two, so if I use the upper bound of ENR, it would cover my basis ( maybe I am wrong ).

[millz]

Correct me if my understanding is wrong:

>Whatever in INT list exists in ENR list

>Whatever in IND list exists in ENR list

You want to:

>List out whatever exists in ALL 3 lists

>List out whatever else that is in ENR list, but not in INT or IND list. (meaning if it exists in ENR and INT ONLY, it will be in the unmatched list)

----3 Scenarios for unmatched list:
----In ENR list----NOT in INT list----NOT in IND list
----In ENR list----in INT list---------NOT in IND list
----In ENR list----NOT in INT list----in IND list


Will need to rework the macro if the above is correct.

[Xceller]

Please see the attached file.

> Items of the three lists may or may not exit in the other lists ( Meaning it could be in one but not the other two )
> Compare the 3 lists and find all the matched ones and un-matched ones.
> output the matched list and unmatched list in the RPT tab.

[Xceller]

You are right about needing to rework the macro. I retested the For Loops and the IF then Else structure, it's not working as I intended for it to work.

[millz]

The question is, how do you determine an 'unmatch' ? Like in my previous post, list out the possible ways to determine it.

[Xceller]

-----Sheet1----------------------------Sheet2-------------------------------Sheet3------------------------------RPT----------------------
-----ColumnC---------------------------ColumnC------------------------------ColumnC------------------Matched--------UnMatched---------
------101--------------------------------101-----------------------------------101-----------------------101-------------102--------------
------102--------------------------------102-----------------------------------103----------------------------------------103-------------
------104--------------------------------105-----------------------------------106----------------------------------------104-------------
---------------------------------------------------------------------------------------------------------------------------105-------------
---------------------------------------------------------------------------------------------------------------------------106-------------

Matched = 3 of a kind
UnMatched = everything that is not 3 of a kind

Hopefully I explained myself better this time.

[millz]

Try this:

Please Login or Register  to view this content.

[Xceller]

millz - Thanks for being patient with me. I ran your code. It's getting close, but found some errors in the Matched List and UnMatched List. For example, Account 100 is not on the Matched list, 170,180,190 should not be on list but they show up on the list..

[millz]

Ahhh I guess that's because it was finding "partial" match. Odd thing is, "100" is showing as a match when I tried running.

amended code:

Please Login or Register  to view this content.

[Xceller]

millz - Thank you so much for being patient and working this through with me. Your code works perfectly. I was trying to work around it by combining the 3 ranges into one range and using the Countif function, if count is 3 then put it in the Matched list, else put it in the UnMatched list. I noticed something that I wanted to run it by you, is it true that ranges from different sheets can't be combined by using Join or Union function? Is there any way to combine ranges from different sheets?

Please Login or Register  to view this content.

[Xceller]

Hi millz - Is there any way to join ranges from different sheets? I attempted the below but errors out on this line: Set rngALL = Application.Union(rng1, rng2, rng3)

Please Login or Register  to view this content.

[millz]

To be honest, I haven't tried anything with combining ranges so far. Maybe because I didn't have the need to do it.

I don't get what you mean by "you noticed something that you wanted to run it by me" ?

[millz]

Hi millz - Is there any way to join ranges from different sheets?

Sorry I am unable to help with this point as I have never used/tried it before. Maybe you can start a new thread regarding this question.

[Xceller]

millz - Thanks again. You have been a great help!