Copy data with condition

Hi bluerose.12
See if this code does what you require.

Please Login or Register  to view this content.

The code is in the attached workbook. Let me know of issues.
John

Hi bluerose.12
I like this code better. See if it works for you.

Please Login or Register  to view this content.

thanks! the code works..can i ask why you have come up with that code? im novice with macro and a little knowlegde in vb. what will i do if i need to connect 2 diffrent workbook and not the sheet in the same workbook? thanks for helping me!
one more thing..how does offset works? thanks!

Attached file is the 2 workbook, book 1 is the arrange file, book3 is the input data.. i need to input the data in book 3 to book 1 following the arrangement of book1.

thanks!

Id province and Id municipality will be the Code a and Code b respectively.
thanks again

Hi bluerose.12
I'll look at this tomorrow (it's getting late here). I'll answer your questions best I can; be aware, I'm far from expert myself.
John

Hi bluerose12
I've fully commented the code for you so you can follow what it's doing with each step. Step through the code in Debug mode and you can follow the actions. If you have specific lines you don't follow, let me know.

Offset means just what it says. Say Cell A1 is the active Cell and you wished to make Cell D3 equal to Cell A1. Your code would be something like this

Please Login or Register  to view this content.

where the 2 says move 2 rows down and the 3 says move 3 columns to the right.
Hope this makes sense.
John

Please Login or Register  to view this content.

Hi bluerose12
I'll look at this question this afternoon.

what will i do if i need to connect 2 diffrent workbook and not the sheet in the same workbook?

John

Hi bluerose12
Regarding this question

Please Login or Register  to view this content.

Please see the attached files. All the code resides in Book3 and this is the code that initiates the update to Book1.

Please Login or Register  to view this content.

This code asks the user to select a file they wish to update (Target File). This procedure assumes both files are in the same folder. If this is not workable, let me know.
The Target file should not be open when you fire the macro; the procedure will open the Target file. Play with it and let me know of issues.
John

Hi bluerose12
Get angry? Nah, we work on this stuff 'till we get it right (it might be ugly but it'll work). What line throws the error?
John

Hello!

I tried to copy your code, it works but it just copy the data from sheet1 to sheet2 at ease.... it didnt check if the code are the same..plus i made this in one workbook,different worksheet..wat will i do if i want different workbook..
I attached my samplework..thanks for the help!tke care

Hi bluerose.12
You need to add additional qualifiers. See attached file.
Regarding this

wat will i do if i want different workbook..

Isn't this what we dealt with yesterday? My post at 4:10 pm yesterday addresses the issue of two different files. Let me know if you have issues.
John

Hi JesseBurton
I assume you are using the "Two" workbook version and not the "One" workbook, "Two" worksheet version. In either event, what is the actual sheet name of your source worksheet? Subscript out of range would indicate that there is no worksheet with the name of "1".
Go to VBA and view the Project Explorer window to see your actual worksheet names.
If you can't figure it out, send me sample copies of your files (including your code). I'll look at it for you.
John

Hi JesseBurton
I'll take a look at this later this evening. How about briefly describing what you're trying to do so I don't have guess>
John

What I'm doing is this. The students are attending study sessions and they are swiping in and out with their student IDs. I am taking the ID number and timestamps from the swipes and calculating the number of hours they spent at the study session that day. Then I am taking that data, and comparing the list of students who attended that day to the master list of all students in the program and adding a column to the master list for that day's hours and then matching the ID numbers and putting in that day's hours for each student who attended in the new column on the master list.

Did that make sense?

So these are the steps I've already gotten the macro to do:

1. extract the ID numbers and Time stamps from the raw swiper data
2. delete any duplicate swipes withing a few minutes of each other
3. match up the swipes in and out for the same students
4. calculate the total time
5. round the total to the nearest 1/4 hour
6. save the treated data as a new excel spreadsheet
7. open the master list.


Now I need to figure out how to compare the treated data with the master list (both will have student IDs in column A) and input the value for the rounded total time that day into a new column in the next unused column on the master sheet, and then put the correct date in Row one for that days time which can be gotten from the Time stamps.

I can do some of this, like insert the row, but it's the comparing between the two workbooks that's really giving me a hard time.

I have attached the file with all of my code in it. its a mess, I was teaching myself as I went. like I said I've been at it for about a month.

Hi JesseBurton
I have a couple of questions. I THINK I know what you're trying to accomplish (notice, I said think), not certain yet.

Question 1. You indicate

(I have the source sheet renamed to "Source" earlier in another macro to simplify things or else they would always be different)

I don't see this in any of the code you posted; I MAY not need to see it but it would be helpful to see the entire process. I'm assuming you're renaming "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx" to "Source"; is this correct?

Question 2. After you've manipulated the data with previous Marcos in "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx", are the entries in Column A unique or could there be duplicate entries in Column A (I suspect dupes but you sample data show none that I notice).

Question 3. I assume you want "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx" data to be pasted in the last open column in Sheet "Fall 09 - 10 Study Session Hours - Test.xlsx" (Column K) to the left of "Total Hours (column M) in your example.

I'll play with this based on my assumptions but your input ASAP would be very helpful.
John

Hi!

The code works as i tried it but it only copy the data in sheet1 to sheet2 as ease. i want it to be sorted just like your first example wherein it will verify first if the Id province and the municipality id in sheet2 is the same in Sheet1. if yes,the data will be copied in sheet1.if the row is blank.if not it will be copied in next the row,wherein they still have the same code.

did i explained it clear..i hope i do...thanks for the help!!!!
I really appreciated it! take care

Hi bluerose.12
Please attach your files (with code in the files) and explain what the code is not doing that you would like it to do. On my platform, the code appears to do what you wish it to do; so, I may not understand what you're trying to accomplish. Create a sample sheet of what your output would look like so I can see what you're looking for.
John

Hi bluerose.12
I re-read your most recent post. So, you want only

Please Login or Register  to view this content.

Based on this, I really do need to see what you wish to see as a final output. Just create a worksheet and create an example of what you're looking for as a result. If we can make it happen, we will.
John

Hi bluerose.12
This can be done

where in i dont have to ask if the workbook is open

What is the target file name?

This I'm not certain I understand; I'll need to think about it for a moment.

I need to count the rows that already have data per code meaning if there are 50 rows having the codes 1 and 2 respectively..i need to count how many rows have already fill out. and restrict the duplication of ID number and contact number..

John

Originally Posted by jaslake

Hi JesseBurton
I have a couple of questions. I THINK I know what you're trying to accomplish (notice, I said think), not certain yet.

Question 1. You indicate I don't see this in any of the code you posted; I MAY not need to see it but it would be helpful to see the entire process. I'm assuming you're renaming "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx" to "Source"; is this correct?

Question 2. After you've manipulated the data with previous Marcos in "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx", are the entries in Column A unique or could there be duplicate entries in Column A (I suspect dupes but you sample data show none that I notice).

Question 3. I assume you want "Card Swipe Time Log 2Ps Uploaded 12_07_2009 11 13 AM.xlsx" data to be pasted in the last open column in Sheet "Fall 09 - 10 Study Session Hours - Test.xlsx" (Column K) to the left of "Total Hours (column M) in your example.

I'll play with this based on my assumptions but your input ASAP would be very helpful.
John

Hi so to answer your questions.

1. The source workBOOK will always be named using this convention "Card Swipe Time Log 2Ps Uploaded & Current Date (I deleted the time).xlsx" But the workSHEET within that book has been renamed to "Source" because otherwise it would always have been the MMDDYY of either the upload date or the data date (I don't really remember at this point)

2. There will be no duplicates in column A after having run through the data manipulation. That first process deletes any duplicates within a few minutes of each other and then matches the in and out swipes and copys the time stamps but removes the rest of the data.

3. Almost, I want a new column inserted in that last empty column (I have already figured out how to do this so its not a big deal I can just put that block of code in), so there are always two empty columns between the last column of data and the total column and then the Data in column D of the "Card Swipe Time Log 2Ps Uploaded MMDDYY.xlsx" inserted into that new column (in the correct rows based on student ID, aka 2P, number of course).

I hope this clears some things up... Again THANK YOU SOOOOOO MUCH!!! I'm sorry I couldn't respond earlier, after I left work I didn't get home until after midnight and went straight to bed and actually just got back into work, about half an hour late hahaha.

THANK YOU! THANK YOU! THANK YOU! (I don't want you to just do this for me though, I want to know what it is the code is doing so that in the future I won't need you to do it for me again! hahaha)

Hi JesseBurton
The attached workbooks contain code that places hours from Card Swipe Time Log 2Ps Uploaded 12_07_2009.xlsx (Source File) into Fall 09 - 10 Study Session Hours - Test.xlsm (Target File).
Both files must be open and, as you requested, the file names are hard coded in the procedure. This is going to cause you headaches in that the name of your source file will continually change. If you want this modified so you can select the file, let me know.
I changed the sheet name to "Source" in Card Swipe Time Log 2Ps Uploaded 12_07_2009.xlsx as you indicated you do this in another procedure.
Let me know if you have any questions or issues. The code is below.
John

Please Login or Register  to view this content.

Hi bluerose.12
I haven't been able to figure out what you mean by this

I need to count the rows that already have data per code meaning if there are 50 rows having the codes 1 and 2 respectively..i need to count how many rows have already fill out. and restrict the duplication of ID number and contact number.

I think I might have an idea of what you want but, instead of me guessing, please demonstrate for me what this count would look like with actual examples. Assuming I can figure out what you're after, where would this count show?
John

Hi bluerose.12
Regarding this

Please Login or Register  to view this content.

As you know, the procedure inserts a new Column A in the source workbook and places an "x" in that Column as the records are used so that it doesn't use them again. Then at the end of the procedure, that Column A is deleted.
If you can live with the new Column A staying in your source file and not being deleted, that will accomplish what your after. Alternately, the "X" can be placed in an unused field in some far right Column and remain there.
Either option would require some minor revision to the Offsets but nothing major.
If you want to use either of these approaches and can't figure out what changes need made to the code, I'll be glad to help. If you need my help, I'll need to see the code you're currently using and sample files (unless the sample data previous posted is still valid).
John

Hi Jessie
Glad I could be of service. If you have issues, let me know.
John

Hi Jesse
The file you posted has a digital signature and I can't open the code.
John

Tell me how the three workbooks tie together; what's the process you're trying to implement?
John

So you've got this .csv file out there somewhere on your network or hard drive. You then import the .csv file into the getsP2fromcardswipes workbook. The front end part of your procedure massages the imported .csv data into the format of the Card Swipe Time Log 2Ps Uploaded XX_XX_XXXX.xlsx file. You then save the massaged .csv file as Card Swipe Time Log 2Ps Uploaded XX_XX_XXXX.xlsx. Is this correct so far?
If the process is as I described, how about posting a sample of the .csv data so I can emulate your entire process as I step through the code.
When you say

it is pulling the date but not any of the data

are you saying the date is being inserted at the top of the new column but no hours are being matched to ID's? If that's the case, I suspect it's a formatting problem with the ID's in the source file vs ID's in the target file. 1234567 is not the same as "1234567". So, you may need to either do a conversion or reformat one on the ID columns. This is just a guess; with the actual .csv data, I could make a better determination.
John

I take it you like it? Glad it works for you.
John

Hi bluerose.12

I can detail the lines of code that need changed but I don't know which version you ended up using. Are you using the version where the worksheets exist in the same workbook (single file version) or the version where the worksheets exit in separate files (two file version)? Or are you using both?

Where do you want the "x" placed? In what column? Give me a column letter as I won't write the code that uses the last right column on the worksheet; if your file is large now, doing so will make it huge.

John

Hi bluerose.12
I'll look at this tomorrow. It's late here now.
John

Hi bluerose.12
I've revised the code in "test" procedure to place an "x" in column Z of the source file when a match is found in target file. The "x" remains there so that the record doesn't get used again. This is my understanding of what you requested; if it's not correct, please let me know.
The code changes are marked in Blue.
John

Please Login or Register  to view this content.

Hi John!

I also did that modification but when i input another data in book 3. after the first run.it didnt copy my next input because it has already "x". I want to put an "x" on the row in Book 3 that has been copied to book1 so that if i entered another input the previous data wont be copied again because it has an "x" in the column Z. "x" will only appear in column Z if the row has value.if the row is blank then there will be no "x" mark in the column Z. Did i explained it well? If theres something not clear just let me know..thanks for your help!

Hi bluerose.12
I honestly don't understand what you mean

when i input another data in book 3. after the first run.it didnt copy my next input because it has already "x". I want to put an "x" on the row in Book 3 that has been copied to book1 so that if i entered another input the previous data wont be copied again because it has an "x" in the column Z. "x" will only appear in column Z if the row has value.if the row is blank then there will be no "x" mark in the column Z

The only thing I can think of is that you're saying that once a record has been matched in EITHER file then it should not be matched again. Or are you saying that once a record has been matched in Book 1, it should not be matched again?
You have me confused! Send me an example of what you want to see. If I can do it I will.
John

Hi john..

sorry if i made you confused. i attached here the 2 workbook. in Book3, there are rows that will be copied in book1 once i run the macro..right? "X" will appear in column Z. after the first run.. i will close the book 1 for the saving.. then i will add more data in book3.. then run it again so that the data that i recently input will be copied in Book1 again..unfortunately the code that you've provided didnt copied my next input. it just dont copy the data that i already copied..once i update the book3 it cant be copied in Book1.
I want to copy the data that i will add in book3 to book 1 everytime i run the macro..and it will only copy the data that i recently update..the data that already copied will stay as ease..
how could i possibly do that? thanks for the help!

Hi bluerose.12
I think I finally understand your issue. The first sample Book3 you sent me didn't contain any empty records; your most recent Book 3 file DOES contain empty records. It makes a huge difference!
To overcome the issue, I have assumed Column G of Book 3 will always be populated when a new record is added. If this is not the case, pick another column that will ALWAYS be populated when adding a record.

Change this line of code

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

See if this answers your problem.
John

Hi bluerose.12
With regard to this

if i want to put the "X" in the target file so that if the data that i already copied wont be copied again

In what column do you want to place the "x"?

With regard to this

I realize that from time to time, i will receive an excel file(which will be the source data) that i need to copy in the target file.. the data that i run previously will contain the "X" value in column Z right?the next worksheet that im going to received didnt have the "x" value. annd everything will be copied again. now, i want to transfer it to target file and it will compare if the data that was written in the source file is already copied in the target file..if yes, it wont copy the data and go to the next row(source file)
the validation will be the name and contact number in target file. if found a data in source file that is equal in the target file..(meaning already copied) it will skip the row and proceed to the next value.. i hope i made it more clear to you..thanks! your code works well..its just that i found some errors if i run the next file..coz the next file will be different file though the same template..

I assume this is a new procedure and not part of the procedure we've already developed. Is this true?

Are you referring to Columns J and K when you say this?

validation will be the name and contact number in target file

Let me know the answers and I'll get back to you.
John

Hi bluerose.12
This line of code has been changed:

Please Login or Register  to view this content.

If "PCOS TECHNICIAN NAME" (Column K in Target File) is populated, that record will not be overwritten by Source File.

This line of code has been added:

Please Login or Register  to view this content.

[/COLOR]It places an "x" in Target Column Z for items that have been matched.

I need to think of the logic to do this:

i want to have the verifier in the target file..once i run the macro, the data from the target will verify if the data in the source file already exist in the target..if it exist it wont copy the data from the source file.

I'll get back to you on this.
John

Hi bluerose.12
This revised code compares each Target File record with the Source File record. If it exists in Source File but Source File has no "x in Column Z, it places an "x" in Column Z. The rest of the procedure does what it did before. The added lines are in Blue.
Let me know if this does as you requested.
John

Please Login or Register  to view this content.

Hi bluerose.12
I guess I don't understand exactly what you're trying to accomplish. The code I posted works on your sample data based on my understanding of your requirements. Tonight or in the morning, I'll post another message asking you specific questions regarding the process so I can get an understanding of your needs.
I think I told you before, I don't get mad about things like this. I do get a little frustrated because I'm not understanding your needs.
John

Hi bluerose.12
I have some questions about your requirements. I would like you to tell me about what you need. The following questions are to help me understand.

1. You will have more than one source file; will you have more than one target file?
2. The key of the target file are ONLY columns J and K (Province, Region, Municipality, etc. are not included in the key). Is this correct?
3. The key of the target file will appear ONLY once in each file (target and source). Is this correct?
4. When you process a new source file, you first want to see if the new records exist in the target file based on the target file key. Is this correct?
5. If the new source file record already exists in target file, put an "X" in Column Z of source file record. Is this correct?
6. If the new source file record does not exist in target file, put it in target file. Is this correct?

Please go through each of these questions and answer or explain as best you can. The attached code is based on my understanding as explained above.
The code first loops through the records in target file and looks for new source file records to see if the key is the same. If it's the same, it places
an "x" in the source file record. No change is made to target file on this loop.
The code then loops through source file and adds new records to target file.
Please try this new code and let me know if it works as you require. If it does, I'll rewrite it because it's terribly inefficient.
But, if it doesn't work as required then we'll need to start over.

John

Please Login or Register  to view this content.

Thanks for the Big HELP!!!!!!!!!

Hi bluerose.12
Have you tried the new code? Did it work as required?
John

Hi bluerose.12
Yes, the code needs rewritten to make it more efficient. I wanted to make certain it worked before I did that. Since it works, I'll start on the rewrite.
John

Hi bluerose.12
I've rewritten the code to look at only records that are populated with data. ALL OF THE CODE SHOULD BE PLACED IN TARGET FILE. The previous code was in source file; please notice the change.
I think you'll find that these procedures will run much quicker. Of course, as your target file fills up with data, the procedures will slow down. I don't have a fix for that.
Put this code in Target and delete all code from source. Let me know of issues. John

Please Login or Register  to view this content.

i attached here the sample similar to my real work book

Bluerose.12...no file is attached.
John

Hi bluerose.12
I'm looking at your "masterlist.xlsm" file which I assume is the "Target File". The key to this file appears to be Columns A, B, C, D, E and F. The test file "Book1.1Filter.xlsm" was the test "Target File". The key to this file is Columns A, B, C, D, E and H.

I'm also looking at your "TRAINEE.xlsx" file which I assume is the "Source File". The key to this file appears to be Columns A, B, C, D, E and H. The test file "Book5.0.1.Filter.xlsx" was the test "Source File". The key to this file is Columns A, B, C, D, E and F.

So, straighten me out here. Which is the "Source File" and which is the "Target File". Have the keys changed?
John

Hi bluerose.12
I've applied another filter to the Master file to reduce the number of records that need to be matched. On the Trainee file you provided, the entire procedure takes three minutes to complete on my system. It takes a full minute to just open the Trainee file.
I've added an additional button to Master file so you can run a timed update. Either button will run the procedure; one is timed and one is not.
Let me know how it works for you.
John

Hi bluerose.12
There MAY be a problem with my logic in the files I had attached so I've removed them for the moment. I'll get back to you.
John

Hi bluerose.12
I've revised the filters on the attached files and it appears to run more efficiently. On the files you provided, the procedure completes in about 50 seconds. I've added a "Timed Run" button so you can time the procedure with copies of your real files.
There is too much data for me to tell if it's doing exactly what you need so look over the results carefully.
Let me know of issues.
John

bluerose.12
I'm glad it works for you. I'll be happy to do this

Please Login or Register  to view this content.

I'll look at this tomorrow or Wednesday.
John

Hi bluerose.12
Below is the code with comments. If there is something that needs further explanation, let me know. In commenting the code, I found one filter reference that needed changed. The line is in blue.
If you have any questions, let me know.
John

Please Login or Register  to view this content.

Hi bluerose.12
Is

my original workbook

protected?
I'm going to modify the code somewhat and will post it later but make sure your source file is NOT protected as protection will cause this error

Run-time error '1004':
Method 'Add of object 'Sheets' failed.

John

Hi bluerose.12
Below is revised code that incorporated an unprotect feature in the event that was causing your issue with your original workbook. Please note, I did not set protection back on. If the workbook is to be protected, that will need to be added.
The code also contains several revisions as suggested by broro183. Rob has been kind enough to give me many useful suggestions in the past and I look forward to his continuing insight.
Hope this works for you; let me know of issues.
John

Please Login or Register  to view this content.