• 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.

This should be simple, but can't seem to get it to work...

Morz18

New Member
Hi there, hoping someone can help me out with this...

I will be pasting data in the input tab (see attached file) daily with new and historical entries. I would like to create a macro to copy all the data from row A2:AG## (note the number of entries will be increasing daily) and paste special values into the "DailyReport" worksheet - first blank row (in the current file it would be cell A6, DailyReport, but it will change as more data is entered day over day). The macro will need to remove all duplicate rows as the data contains historical records

Note - column AH on the "DailyReport" contains manually inputted data and will need to align with the respective row after running the macro.

Hope someone can help me out, I read the following link, but wasn't able to customize to my needs.

https://chandoo.org/forum/threads/vb-code-to-copy-data-from-one-sheet-to-another-sheet.42963/

Thanks!
Morz18
 

Attachments

  • TestBook.xlsx
    13.7 KB · Views: 7
Morz18
... many things are possible
and of course, I would do this other way.
I would copy only unique row ... less cleaning.
>> Press Input-sheets [ Do It ]-button.
 

Attachments

  • TestBook.xlsb
    23.6 KB · Views: 8
Morz18
... many things are possible
and of course, I would do this other way.
I would copy only unique row ... less cleaning.
>> Press Input-sheets [ Do It ]-button.
Thanks vletm, that works great for small data sets, but I got the hamster wheel of death when processing 4,000+ lines of data. Regardless, I appreciate you having a look at this!
 
Morz18
Maybe Your hamster knows
... if You'll run 100meters or 10,000meters ... longer distance would take a longer time too ... or how?
Your sample data have only values ... if there would be eg (some) formulas, then there could control calculation ...
... and ... if instead of copy would do move
... there could be 3,999 duplicates in Input-sheet.
As I wrote: I would copy only unique row ... less cleaning.
Did You try to solve Your hamster's case or do make some kind of speed record?
'Quicker'?
a) avoid duplicates (why to make extra work?)
b) now, there are 33 columns to verify ... if less columns then it would be 'quicker'.
c) Without Your manually inputted data AH-column in DailyReport-sheet, You could use Tables - Remove Duplicates-option.
 
Last edited:
Hi !​
The macro will need to remove all duplicate rows as the data contains historical records
As you can yet test manually a pretty fast way : copy all the data to the destination worksheet​
then use the easy Excel feature 'Remove Duplicates' …​
Once it works, you can reload your workbook, activate the Macro Recorder and redo the same in order you get your own code base !​
Or for further help at least explain which columns you use to control for duplicates …​
Another way is to add an ID column in source data, as each ID must be unique,​
it's easier and faster to check duplicates ! (Just with an easy Excel basics : an advanced filter !)
If you go this way and need further help, a new attachment will be necessary.​
According to your thread title : it can't be simple using Excel like a database software as it was not designed for​
and more difficult when a worksheet has a poor data design …​
 
Morz18
Maybe Your hamster knows
... if You'll run 100meters or 10,000meters ... longer distance would take a longer time too ... or how?
Your sample data have only values ... if there would be eg (some) formulas, then there could control calculation ...
... and ... if instead of copy would do move
... there could be 3,999 duplicates in Input-sheet.
As I wrote: I would copy only unique row ... less cleaning.
Did You try to solve Your hamster's case or do make some kind of speed record?
'Quicker'?
a) avoid duplicates (why to make extra work?)
b) now, there are 33 columns to verify ... if less columns then it would be 'quicker'.
c) Without Your manually inputted data AH-column in DailyReport-sheet, You could use Tables - Remove Duplicates-option.
Thanks vletm for replying, I am rethinking my workbook design...you have given me some things to think about. Thanks!
 
Hi !​

As you can yet test manually a pretty fast way : copy all the data to the destination worksheet​
then use the easy Excel feature 'Remove Duplicates' …​
Once it works, you can reload your workbook, activate the Macro Recorder and redo the same in order you get your own code base !​
Or for further help at least explain which columns you use to control for duplicates …​
Another way is to add an ID column in source data, as each ID must be unique,​
it's easier and faster to check duplicates ! (Just with an easy Excel basics : an advanced filter !)
If you go this way and need further help, a new attachment will be necessary.​
According to your thread title : it can't be simple using Excel like a database software as it was not designed for​
and more difficult when a worksheet has a poor data design …​
Thanks Marc for replying, I am rethinking my workbook design...you have given me some things to think about. Appreciate the help!
 
Back
Top