best way to add worksheets together

[Phillips]

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

 

[Paul Robinson]

Hi Phil
Your question is far too vague.
regards
Paul

 

[Phillips]

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

 

[Anders S]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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

 

[Phillips]

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


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

 

[Anders S]

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