• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy found rows from another sheet based on criteria

S

solti

New Member
Hello,

I have 2 Sheets:
In the "accounts" sheet in col B I have a unique account number or numbers
In the "data" sheet I have 7 columns (with account numer in col C) . It can be up to 5k rows in this sheet.

For each account from "accounts" sheet I have to look up a matching number of accounts in "data" sheet and copy found rows under a given account row from "accounts" sheet. It can be ca. 100-120 unique accounts in "accounts" sheet (The number of sheets may change but not much from time to time and will be inserted manually).

Then, for each account in "accounts" sheet I have to insert subtotals and grouping.
I have done that with "for... next" implemented many times. For sure to many and takes ages to process.

I'll gratefull if anybody could suggested me a solution with the use of arrays (if this is the best solution),

I enclose a file with a simple macro.

Many thanks,

s.
 

Attachments

Hello Solti..

I think you have written great code which really works..for this we do not really recommend using arrays..

Thanks
Monty
 
It works with just a few rows like in attached file.
Try adding ca 2k rows. It lasts much too long. That's why I thought about using arrays
S.
 
Hi !

Try to first use an advanced filter to copy data,
after you can sort data on accounts
(if needed, depends if you use advanced filter at once
or account by account [no need to sort in last case])
then you can use subtotal from data menu …
​
 
I'm concerned about finding the code to dynamically adjust the accounts in advanced sort according to the number of accounts in "accounts" sheet...
 
You must log in or register to reply here.
Back
Top