Transfering data from sheet to another sheet using arrays

[YasserKhalil]

Hello everybody
I have two sheets : Data sheet and Results sheet
My attachment is just a sample..the original has large amounts of data
I could loop through cells in sheets("Data").range("b2:B" & Last row) to check for the date which is in Result sheet. range("C2") to extract the data related to that date

I need to use arrays as I know it is faster and realiable in dealing with large amounts of data

Thanks for help

[Trebor76]

Hi there,

Not sure why you can't just filter on the required dates within the Data tab, but this will do the job in the most efficient way nonetheless:

Please Login or Register  to view this content.

Regards,

Robert

[YasserKhalil]

Thanks a lot Mr Robert for this simple and fantastic solution ..I really like it
I'm learning new techniques and I know how to loop and how to use filter methods .. but I'm poor at arrays
I was specific in post 1 that I need to learn how to do my task using arrays

[jindon]

Please Login or Register  to view this content.

[YasserKhalil]

Mr. Jindon
I tested the code on my original wb and the results are >>>>> great & fantastic & wonderful
It's very speedy even faster than the filter method
My way tooks about 20 seconds .. Filter way took about 7 seconds .. your way tooks about 2 seconds
Thank you very much for this great help

Just one request : can you add comments on the lines of the code to try getting it
I liked the way Mr. Robert do when added comments on each line to explain how it works

[jindon]

Step debug for yourself with LocalWindow open first.

If you still need help, ask the lines that you don't understand....

[YasserKhalil]

Thanks Mr. Jindon for guiding me
As for this loop

Please Login or Register  to view this content.

I guess that loop to store the results that matches the criteria ..can I use another variable such as b for example to store the results in it
to be like that I mean

Please Login or Register  to view this content.

and in this line

Please Login or Register  to view this content.

i tried to change but got an error
I appreciate your help

[jindon]

Then

Please Login or Register  to view this content.

[YasserKhalil]

Great Mr. Jindon
i got it now
In sheets("Data") in column A there are sometimes formulas

Please Login or Register  to view this content.

with no data related

After chacking local window I found that there are empty elements of the array b stored in the array but they are empty ..
how can I get rid of these empty elements?

I'm sorry for all these questions but I'm learning from you

[jindon]

Does it matter?

I don't care when I use Array, looping inside array is almost nothing.
I'd rather exit loop if the data column is "", like

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Jindon
I have told you that I'm learning and I'm curious about details. That's all
And from you now I learned new point which is that looping inside array is almost nothing (as I was intending to ask you about that point and you already replied me without asking you even)

Last point to ask about : I have 15 columns in sheets("Data") and the required results are 14 columns ..
In local window I noticed that array stored the 15 column but returns 14 column as needed .. but how the code let me get my results without any prompt in the code related to that point
Hope it is clear

[jindon]

That magic comes from

Please Login or Register  to view this content.

The sheet only has 14 columns so

Please Login or Register  to view this content.

is actually

Please Login or Register  to view this content.

You see?

[YasserKhalil]

Yes now it is very very clear
Thank you Mr Jindon for all these valuable information you provided me
you are really great and distict person

Can I ask again or you are annoyed from me?
About Array b there are empty elements of the array even after adding this line

Please Login or Register  to view this content.

I'm talking about the results in local window ..Is it possible to ReDim array b according to the actual size !!?

[Doc.AElstein]

Hi YasserKhalil,
. Here is another Array version similar to the last code I did for you.
http://www.excelforum.com/excel-prog...ml#post4088104
. It may be a bit slower than necessary as some additional steps are made to make it more clear to follow.
. Also the Transpose is known to be slower than a simple routine to transpose Array.
( http://www.excelforum.com/showthread...t=#post4092919 )
. Make note of jindon’s advice for putting watches on variable, especially the Arrays and watch how they are filled in when stepping through with F8. I find that easier when I have two screens,
http://www.mrexcel.com/forum/lounge-...aneously.html?

. Alan



Code:

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very very much Mr. Alan
You are fantsatic .. I like your way of explaining all the details. That's very impressive and very useful
Thanks alot for the time spent explaining the lines of the codes ...
I learned a lot from your great explanation

[Doc.AElstein]

Thank you very very much Mr. Alan
...

.


Hi YasserKhalil,
. Thanks for the feedback
Alan Elston

. BTW. ( By the way ) – I am looking also for my project in speeding things up a bit, possibly using Arrays. Amongst other things, I am considering a simple routine in place of the .Transpose Function. So I have as a by-product of this work another code version that you may wish to consider.. The basic form of the code is identical to that for my code from post #14. It is therefore in its more usually given, simplified form, and without most of the 'green comments
. However the .Transpose part is replaced with a simple routine ( Placed in a Function for clarity of code layout. )
.
. Here the codes. ( You copy both codes to the same Module. But you only run the first Sub. This is because the Fuction is “called” when needed ( twice in this case from the first Sub ) ) . For the Fuction I have include my usual explaining 'green comments.

Codes

.

Please Login or Register  to view this content.

………………

. BTW. If you are testing all the above codes you have been given, along with yours and are comparing the speeds in large circuits it would be a great contribution to this Thread to Post your results. Post also a copy of your code attempt.. This would make this Thread a very useful one for future reference.

[jindon]

Please Login or Register  to view this content.

Pull rows that col.B match with criteria. No loop.

[Doc.AElstein]

Please Login or Register  to view this content.

Pull rows that col.B match with criteria. No loop.

. Wow! - And so fast - I will be busy most of tomorrow finding out how that works! (it does work ( Obvously ) - just ran it )
.
. I have a lot to learn
.
Alan