Are you looking for a way to automate this process, i.e. using a macro? If so Excel 2008 only supports Applescript code, would you consider using Excel 2004 or Excel on Windows?
Let me know when you're ready.
Do you mean you also have Excel 2003 on another PC running Windows OS?
Ok, do you have the file with the Data List (or sample data list) that I can use to base the macro on?
Sure. I will write the macro and send it to you once I get the data file. Here are the instructions for uploading you file.
1. go to http://fileqube.com 2. click ‘Browse' then select your file3. click ‘agree and upload' button (when it finishes uploading a new page will be displayed)4. click on the link next to 'Download link' and copy that link, then come back to this page and paste the download link in your reply.
Got it, thanks.
Will start working on it shortly and will send it out as soon as I am done, If I find that I need more information I will let you know.
Thanks for the accept.
b. If no match exists, add row to New List i. Delete row from DL
- To which row it should be added, and which data should the added row have?
- Which row should be deleted from Data List?
Ok, here is the initial version for testing.
I tried to follow your outline literally but in the absence of real data in the New List it was not possible to fully understand the concept and hence to test it properly. Had to create some arbitrary data in the New List so that the macro can run.
To run the macro press Ctrl + Q.
Please make sure that your Excel options is set to allow macros to run, otherwise nothing will happen when you try to run the macro.
Go ahead and test it out and let me know the changes or adjustments that you need.
I have to leave now for a few hours. Will be back this evening to pick up any message you may have left and make the adjustments.
I am not sure I understand where the duplicates should not be (between the New List and the Data List, or between the items within the New List, or between the items within the Data List,
To the best of my understanding the macro that I sent you does what you have outlined, step by step. I may have misinterpreted a step or two or you may have been unclear on some of the steps, that is why I would like to tell me which step in your instruction was not followed properly so that I can adjust that part of the macro,
In other words rows 3 through 24 should not be in the New List?
Still something is missing!
1. Insert first row from Data List to first row on New List a. Delete first row from DL
*** So by now New List has only one row of data
2. Check "T" column of next row on Data List against "T" column in previous 59 row's on New List
*** But at this point New List has only one row of data, so how does 'provisos 59 rows' apply
I am sorry we are jumping around from one issue to another. I asked a question and I got a confusing answer which does not specifically answer my question. The issue may be clear to you but it is not to me yet.
I can fix the macro to do exactly what you have outlined without going into the concept of the process, if you can clarify the points that I asked you about. If you do not think doing this will provide the solution let me know so that I can ask the moderator to refund you the amount that you have authorized to pay when you clicked the accept.
Yes I think that would help. I will then modify the macro to follow your outline exactly, then you can test it and tell me if there are changes needed. But please review your outline in view of these communications to make sure it includes all the points that you have mentioned as I will only be using the outline.
If you can provide the information tonight I will work on it in the morning and hope to send it out to you by noon time pacific.
Re your question about talking on the phone, I am afraid communication by phone or personal email is not allowed on this site.
Ok here we go
This covers steps 1, 2 and 3
Once we get this part finalized then we will go to step 4.
I have added serial numbers in columns BF and colored the relevant columns for ease of tracking and testing. We can of course remove them later.
This statement "1- Insert first row from Data List (Row 2) to first row on New List (Row 61)" is understood to mean add to first available row on New List (Row 1) and not "first row" on the sheet which is row 1.
This statement " 2. Check "T" column of next row on Data List against "T" column in previous 59 row's on New List"" is understood to mean that the checking will always be done against the original 59 rows which do not include the ones that have been recently added.
Let me know how it goes.
Here you go. Take 3!
I hope this one is ok.
Glad to hear that. Thank you very much for the bonus and feedback.
The difficult part was to fully understud the requirement.
Yes I will work on the remaining part now and will get back to you as soon as I am done.
lol. Yes everything that has to do with excel. In fact this question should have been posted in the programming section, that is where I am mostly at.
I will need an accept to answer this one! lol.
Please check if this one does the looping the way you want it. There was a slight discrepancy in item 4 between the two versions of the outline (i.e. repeat step 2, or repeat steps 1, 2, & 3). So i am not sure if I chose the intended interpretation.
You're very welcome. Hope to see you back if you have other questions in the future.
Sure, just write "For John D" in the subject or the body of the question.
Thank you for your confidence and good luck with your new business.