Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
I have several worksheets and I would like to add them together.
I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
Hi Phil
Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
All worksheets have the same structure, some have been split up for
different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
Phil,
Still not enough info to deliver the "best way" if there ever is one. How many sheets are there? Dozens, or just a limited number? Will you do this once only or on a regular basis? Assuming this is a once-only job on a limited number of sheets you can - copy/paste all sheets into new master sheet - do not link - sort on email and date - identify the duplicates with a simple formula like =IF(B3=B2,"X") (with email addresses in Col B) - delete the duplicates Post back with more details if you need a macro to automate the process. HTH Anders Silven "Phillips" skrev i meddelandet news:I5qvb.263842$HS4.2352893@attbi_s01... All worksheets have the same structure, some have been split up for different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
If I were doing this manually, I'd create a new worksheet.
Then for each workbook: copy the worksheet (just one?) to the new worksheet. But paste it in column B--not column A. Then put the date of that worksheet in Axxx:Ayyy. When you're done with this, you'll have all the data in one spot--with the associated dates in column A. Now get rid of all the headers except the first ones. (In fact, you didn't need to copy them from workbooks 2,3,&4.) Sort your data by the key value and date (descending order so that the most current date is on top). Insert a new column A. put this in A1 (assuming the key value is in column C). =countif($c$1:c1,c1) (and drag down) Now apply Data|Filter|Autofilter and show the values that are greater than 1 in column A. Delete those visible rows. This assumes that all the info is on each of the records in each of the files. (I was assuming that the original split into each workbook was to move whole rows--not fields within the row.) Phillips wrote: All worksheets have the same structure, some have been split up for different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil -- Dave Peterson |
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
Another way that doesn't use that last formula and autofilter.
Do everything the same way through the sort. Then select column B (where the key values are) and Data|Filter|Advanced filter (filter in place and check unique entries only). Then copy those visible cells to a new worksheet. (Because this'll probably copy lots of discontiguous areas, the values will be pasted--existing formulas will be lost.) Dave Peterson wrote: If I were doing this manually, I'd create a new worksheet. Then for each workbook: copy the worksheet (just one?) to the new worksheet. But paste it in column B--not column A. Then put the date of that worksheet in Axxx:Ayyy. When you're done with this, you'll have all the data in one spot--with the associated dates in column A. Now get rid of all the headers except the first ones. (In fact, you didn't need to copy them from workbooks 2,3,&4.) Sort your data by the key value and date (descending order so that the most current date is on top). Insert a new column A. put this in A1 (assuming the key value is in column C). =countif($c$1:c1,c1) (and drag down) Now apply Data|Filter|Autofilter and show the values that are greater than 1 in column A. Delete those visible rows. This assumes that all the info is on each of the records in each of the files. (I was assuming that the original split into each workbook was to move whole rows--not fields within the row.) Phillips wrote: All worksheets have the same structure, some have been split up for different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
There are currently about 15 different sheets.
I would like to be able to do this on a regular basis (once a month?) I would like to have the records from one sheet as always being the preferred, and then all others based on the date field (latest being the one retainded) I would like to have a macro. I was thinking about a loop that would step through each sheet, look at each record, and then if found in NewMaster, compare dates, if the date is later, then replace the data, if the date is older, go to the next. If the record is not found, then add it. Does this sound like a proper (read "good") way of doing this? How would I code this? Thanks Phil "Anders S" wrote in message ... Phil, Still not enough info to deliver the "best way" if there ever is one. How many sheets are there? Dozens, or just a limited number? Will you do this once only or on a regular basis? Assuming this is a once-only job on a limited number of sheets you can - copy/paste all sheets into new master sheet - do not link - sort on email and date - identify the duplicates with a simple formula like =IF(B3=B2,"X") (with email addresses in Col B) - delete the duplicates Post back with more details if you need a macro to automate the process. HTH Anders Silven "Phillips" skrev i meddelandet news:I5qvb.263842$HS4.2352893@attbi_s01... All worksheets have the same structure, some have been split up for different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
#8
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
best way to add worksheets together
Phil,
I think, but that's a matter of opinion, that it is easier to first collect all data in one place and then find the most recent entries. You can start by recording a macro and perform the steps outlined in my previous post (if you like the strategy). You don't have to include all 15 sheets, 2-3 sheets are enough at this point. The recorded macro, when working, will show the basic program flow, but it will need some fixing and optimizing. The repetitive selection of sheets and copy/paste of data will have to be replaced by a loop. All selection of ranges to copy from and paste to, will have to be automatically calculated. And so on. If you run into any specific problems, ask a question in a new thread so more people will see it. Otherwise feel free to continue in this thread if you like. Good luck, Anders Silven "Phillips" skrev i meddelandet news:ZIqwb.290440$HS4.2604053@attbi_s01... There are currently about 15 different sheets. I would like to be able to do this on a regular basis (once a month?) I would like to have the records from one sheet as always being the preferred, and then all others based on the date field (latest being the one retainded) I would like to have a macro. I was thinking about a loop that would step through each sheet, look at each record, and then if found in NewMaster, compare dates, if the date is later, then replace the data, if the date is older, go to the next. If the record is not found, then add it. Does this sound like a proper (read "good") way of doing this? How would I code this? Thanks Phil "Anders S" wrote in message ... Phil, Still not enough info to deliver the "best way" if there ever is one. How many sheets are there? Dozens, or just a limited number? Will you do this once only or on a regular basis? Assuming this is a once-only job on a limited number of sheets you can - copy/paste all sheets into new master sheet - do not link - sort on email and date - identify the duplicates with a simple formula like =IF(B3=B2,"X") (with email addresses in Col B) - delete the duplicates Post back with more details if you need a macro to automate the process. HTH Anders Silven "Phillips" skrev i meddelandet news:I5qvb.263842$HS4.2352893@attbi_s01... All worksheets have the same structure, some have been split up for different reasons, and I want to create a new "master" that has all of the records. Basically, I need to copy all of the records together, but by doing this, I would get many duplicate records. What I would like to do, is to have a nice clean empty worksheet, and then look at all of the other sheets in the workbook, look at each record, if the record is not found, add it. If it is found, then look at the last modified date, and which ever date is the latest, made sure that is the info that is in the master.I would be using email address as a unique key. The date is a column label lastmodified. HTH, Phil "Paul Robinson" wrote in message om... Hi Phil Your question is far too vague. regards Paul "Phillips" wrote in message news:<z3dvb.259485$Tr4.807966@attbi_s03... I have several worksheets and I would like to add them together. I do not want to copy or create duplicats, and I want the one with the latest date (LASTUPDATED is a date) How would I do this? Thanks Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to consolidate all worksheets data into 1 worksheets? | Excel Discussion (Misc queries) | |||
How use info in Excel shared worksheets to create new worksheets | Excel Worksheet Functions | |||
How do i assign the ActiveWorkbook.Worksheets to a worksheets object? | Excel Worksheet Functions | |||
Assigning Cells in worksheets to other data in other worksheets. | Excel Discussion (Misc queries) | |||
Need code to protect worksheets - amount of worksheets varies | Excel Programming |