PDA

View Full Version : Solved: Comparing-Matching-Copying-Deleting Cells in 2 WKBooks



SherryO
02-07-2006, 02:07 PM
I have attached two worksheets. These are from two different sources. The destination file is used to upload financials into a DB. I cannot change the structure of this file, only the numbers. The second file, Source, is a Pivot Table created from MSProject output (Raw Sheet) which I can play with. What I now need to do is find all of the entries in the Source Pivot Table that matches existing data in Destination and overwrite it or zero it out.

For example. There is a Task 000 in Destination that does not exist in Source, therefore I need to clear all of the cells in mint green only for the future, but leave the data that occured in the past. For Task 207 which exists in both Source and Destination, I need to match the data by Task, Date and Class and write it to the appropriate cell in Destination. (Notes: WBSTask and Task are really the same. There are actually formualas in the grey cells, but I'm not currently conserned with them, so I left them out.)

I really don't even know where to get started outside of the fact that I have to walk the cells, but I would like to do it in the most efficient way possible as some of these files can get really big. We are currently doing it manually for about a bzillion items amd it's getting old. If one of y'all gurus could get me started, I would forever be in your debt!
Thanks!!!
SherryO

XLGibbs
02-07-2006, 05:42 PM
Well, One problem will be multiple occurrences of things like C1, C2, and C3 within one task. Some of them need to be overwritten, others don't....however, I imagine these are just sample headers....

How will the "past" and "future" be determined...by the actual date at the time the code is executed? or a time stamp within the file/pivot table somewhere...?

Overall this is pretty straightforward. Is the sole purpose of the pivot table to aggregate the data? If the pivot is not necessary, may be able to eliminate it based on the "Raw" data where the aggregations can be done behind the scenes. Would simplify some matters....

Answer the questions and i will see what I can do, if no one else beats me to it...

SherryO
02-08-2006, 06:30 AM
Thanks for such a quick answer!
The headers in both sheets are for real. In the PT there will never be duplicates, but in the Destination C1, C2 and C3 are in each task twice, once for hours and once for Dollars. I'm only concerned with hours. The Dollars are figured with a formula. Again, that's the sheet I can't change and I inherited it from someone who is no longer with the company. The past and future will be determined by the LastActual on the DB, so a variable for now would work, I think:> The Pivot Table is there just to aggregate the data. If it's easier to handle in Raw form, I'm all for it. Since no one has beat you to it, I greatly anticipate your reply. Thank you soooo much.
Sherry

XLGibbs
02-08-2006, 06:33 AM
OKay, will see what I can whip up tonight after work. Makes sense to me.

XLGibbs
02-08-2006, 06:28 PM
Sherry, running into some issues in just thinking of the logic. Where exactly in the two sample files would I be able to find the cut off date for ending Past and beginning future.

As far as matching the data to a task in the destination, that is not so rough, but identifying which column of interest is to receive the data (being past or future issue) is eluding me at the moment...can you shed more light on it?

SherryO
02-09-2006, 06:48 AM
This one got me too. I'm going to have to right the Last Month of Actuals to a particular cell. Let's pick A2. If I sent you everything that I'm working with, you would croak. I got this lovely patchwork of bandaids from someone who "is no longer with the company." There is another piece, a commercial product called Budget Wizard which queries the Merlin Oracle DB for the currently loaded budget and the past actuals, then the Destination piece populates the Periodic tab. What I'm trying to do is take the Source file, which is output from MSProject (thankfully coded brilliantly by someone else) and marry it to the Periodic tab and then let BudgetWizard upload the new info. The problem is, the BudgetWizard code is locked so the only way to determine the Last Month of Actuals is to count the cells that are a particular color. If it were up to me, I'd scrap it and go directly to the DB, but I am a mere worker bee.:> The end product will be a button in MSProject that asks the Project Manager if they want to upload to Merlin, then it will launch all of this stuff and email the Finance group that there has been an upload, or they need help or whatever. I cannot tell you how valuable this site has been. I would never have been able to do this without y'all!! Thanks for seeing this through!!
Sherry

XLGibbs
02-09-2006, 11:09 AM
OKay, we can have it so you can input the cutoff date for actual/future.

One last thing are all dates going to be the 1st of month/year, or will there be other dates involved....makes a difference in how I intent to solve this particular dilemma....

SherryO
02-09-2006, 11:31 AM
Yes, all of the dates should be the first of the month. Thanks again. I can't wait to see what you have!!:>

XLGibbs
02-09-2006, 04:33 PM
Niether can I, will follow up once I have working model for you. Max time frame, maybe 2 days. Sorry about the delays, but hey, you get what you pay for :)

Shouldn't be too bad once i can sit on it for about an hour and zip out the required code....

XLGibbs
02-12-2006, 09:37 AM
Sherry, unfortunate circumstances have prevented much of my ability to progress with this so far...I am still on it though :) hopefully tomorrow night I will have something solid for you to check out.

SherryO
02-13-2006, 09:49 AM
I'll be waiting eagerly for you... No problems with a delay. I appreciate you working on it!!! I have plenty to keep me busy until I hear from you. Sherry

XLGibbs
02-13-2006, 07:33 PM
Sherry, here is my first pass. Not the prettiest, but it appears to do exactly what you ask.

1. Finds the cutoff date based on a date you will enter, the default is the 1st day of the current month (all month dates are day 1 per you)
2. Finds the column of the cutoff date and clears the data in the green rows (per your requirement) all the way forward in time)
3.Goes through the Raw data on the source worksheet based on the CLASS in column C. Based on the dates in the same row, it finds which column to paste the data, and the row is determined based on the class in column A. This was only made easier by the consistent groupings of 27 rows each segment.
4.As of now, it will highlight the affected cells (ones that match the raw data) yellow so you can see which ones change.

The zip folder attached contains the source files you sent, and a copy of the sample destination file for backup.

Open the two files and execute the WillyNilly macro in the "forPostSoruce" document module 1.

The is really no error handling, but it works with the sample files you sent. Provided the actual structure of your "live" files is exact to the samples, this seems to do the trick. If this works, I may be able to clean up the process some ...

Let me know.

EDIT: All previous values included in dates past the cutoff date entered are CLEARED. If this needs to affect only a class that does not appear in the Raw data sheet in your source, let me know as that part will need to be rewritten slightly (easy fix)

Also, the pivot table is not necessary with this code as it cycles through the raw data.





Sub WillyNilly()
Dim wsS As Worksheet, wsD As Worksheet 'Destination
Dim rngLook As Range, rngDest As Range 'range for data to go
Dim cutDate As Date, rngDates As Range, rngDateMatch As Range
Dim c As Range, destoffset As Long
Set wsD = Workbooks("forPostDestination.xls").Sheets("Periodic_Data")
Set wsS = Workbooks("forPostSoruce.xls").Sheets("Raw")
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

'Get the date of cutoff and store it
cutDate = InputBox("Enter the cutoff date", "Date for cutoff", Format(Now(), "mm/1/yyyy"))

'find the column of the cutoff date
Set rngDates = wsD.Range("F6:CL6")
Set rngDateMatch = rngDates.Find(cutDate)
If rngDateMatch Is Nothing Then
MsgBox "Date not found"
Exit Sub
End If

'clear from the date column to column 90. Segments of 26 rows beginning with row 7
' end at row 319 block, rows 1,2(+1),3(+2),10(+9),12(+11),14(+13),16(+15) cleared

With wsD
For d = 7 To 319 Step 26
.Range(.Cells(d, rngDateMatch.Column), .Cells(d, 90)).ClearContents
.Range(.Cells(d + 1, rngDateMatch.Column), .Cells(d + 1, 90)).ClearContents
.Range(.Cells(d + 2, rngDateMatch.Column), .Cells(d + 2, 90)).ClearContents
.Range(.Cells(d + 9, rngDateMatch.Column), .Cells(d + 9, 90)).ClearContents
.Range(.Cells(d + 11, rngDateMatch.Column), .Cells(d + 11, 90)).ClearContents
.Range(.Cells(d + 13, rngDateMatch.Column), .Cells(d + 13, 90)).ClearContents
.Range(.Cells(d + 15, rngDateMatch.Column), .Cells(d + 15, 90)).ClearContents
Next d

End With


Set rngLook = wsS.Range("C2:C" & wsS.Range("C65536").End(xlUp).Row)

'go down the list of classes
For Each c In rngLook
If c.Offset(, 2) >= cutDate Then 'if the date is = or past cutoff then
Set rngDateMatch = rngDates.Find(c.Offset(, 2).Text, LookAt:=xlWhole)
If Not rngDateMatch Is Nothing Then
Set rngDest = wsD.Range("A:A").Find(c)
destoffset = 0
'determine which row to paste the data
Select Case Left(UCase(c.Offset(, 1).Text), 2)
Case "C1"
destoffset = 0
Case "C2"
destoffset = 1
Case "C3"
destoffset = 2
Case "MA" 'materials
destoffset = 9
Case "SU" 'subcontract
destoffset = 11
Case "TR" 'travel and living
destoffset = 13
Case "OT" 'other
destoffset = 15
End Select

Set rngDest = wsD.Cells(rngDest.Row + destoffset, rngDateMatch.Column)
rngDest.Value = rngDest.Value + c.Offset(, 4) 'add the prior value to the current one
rngDest.Interior.ColorIndex = 6 'color the cell yellow for testing, remove when testing is done

End If

End If

Next c


Set wsS = Nothing: Set wsD = Nothing: Set rngDates = Nothing: Set rngDateMatch = Nothing
Set rngLook = Nothing: Set rngDest = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub

SherryO
02-14-2006, 07:41 AM
I tried the code, but I can't get it to work. It tells me that it can't find the cutDate. Date not found from the msgbox. Also, I should have mentioned that the number of segments will vary, so going from 7 to 319 won't work for me. How can I make that dynamic? This is great code! Thanks for sticking with me. Sherry

XLGibbs
02-14-2006, 09:41 AM
what date did you put in? The very file I sent back to you is the file I tested with and it worked fine.

As far as the cut date, I did run into some minor issues with finding the date in the custom format on the destination sheet, but thought I resolved that.

7 to 319 can be more dynamic, but you will have to more specific as to the "how" dynamic it needs to be.

If the range of dates on the file you are testing with is not in the same row and columns (on destination sheet) then the code needs to be changed.

There are some things you can try with cut date.....

first, try stepping through the code using F8 to see what cut date is, or add this line to the code underneath the input box and use the immediate window for viewing the date..

Print.Debug cutdate

The cutdate entered and subsequently searched for will appear in the immediate window.

I am flattered you think it is great code, particularly since it apparently doesn't work for you. :)

SherryO
02-14-2006, 10:15 AM
I can't see any file that you attached, so I copied and pasted the code into the files I sent to you. I got it to work once, then it gives me the cutDate problem, so I know it works, hence the Great Code comment:>, but I can't replicate it.
As for the how dynamic, it could be almost anything. There could be 100 Tasks (26 rows each) added, or 5 Tasks, then the Totals will be added, so I supposed that it could stop when it sees "total" in column A?
Also, I should have told you that in the real file that I will be using for all of this, there are fomulas in the Desination Date row (F6:CL6). They have been formatted to be the first day of each month. xllookup values? I can copy, paste special values to get rid of it, but I would prefer to look at just the values if that wouldn't be too inconvienent. I'm sorry I didn't tell you sooner. I would send you the real Destination file, but it's too big, and I probably would be violating my NDA.
I tried the Print .Debug; cutDate and it told me that it wasn't a valid object. I tried to hard code a date in it and got the same results, I used 9/1/2005, which was actually the right date and I got the same date not found.
I have used cell A2 in Periodic_data to be where the real date will be written. That is acually a formula, too. I hope that's not messing you up, I can change that if need be. Also, because some of these budgets will be for forecast only, it will put "No Actuals" in cell A2 if it's forecast only, therefore it can go from the first column, F, instead of finding a date.
I hope I'm not being too much of a pain or asking too much from you. I'm not trying to flatter you, but hey, if that works:> No, seriously, I appreciate the help like you wanna read about.
Thanks!

XLGibbs
02-14-2006, 10:25 AM
Certainly, presenting sample files that don't match the "real" files poses issues, but minor ones.

Since the sample file had issues with the date....there is a workaround. You say the resulting date is formula based, so if we compare values....


Set rngDates = wsD.Range("F6:CL6")
Set rngDateMatch = rngDates.Find(datevalue(cutDate))
If rngDateMatch Is Nothing Then
MsgBox "Date not found"
Exit Sub
End If


also change the next date search below that to lookat:=xlValues instead of xlWhole.

as far as the number of 26 row sets....this should handle that....



Dim lastrow As Long

With wsD
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row - 26 'change this per below
'replace the 26 above with the number of rows
'from the bottom cell in the "total" section to the last Class header in column A
'then this would be more variable
For d = 7 To lastrow Step 26 '319 needs to be the last header row in column A to look at...


be sure to change the .Row - 26 number to be the right number. You want it to take the bottom row found in column A and subtract a number to get the right row of the last class header...





I didn't realize the files I uploaded didn't stick..probably because they were already attached in the thread ....my bad.

This is somewhat complicated, and now it seems much of the original structure as in the sample, is not what the reality is. It is certainly helpful when asking for code to be written to perhaps be as accurate as possible. My code does precisely what you asked for in the sample files provided in the post.

I don't mind helping make it work, but had this information been known, it would have already been handled...:thumb

XLGibbs
02-14-2006, 10:34 AM
As far as the real date being in cell A2....do you want this to be the cutoff date then? I do recall asking how this was to be determined...no mention of a cell containing it was made...that is an easy change..as opposed to the input box.

With wsD
cutdate = .cells(2,1).value
End With

SherryO
02-14-2006, 10:44 AM
I didn't mean to make you angry. The sample I first submitted was the best information I had at the time. I'm sorry it wasn't as accurate as it needed to be. I was not intending to send anyone on a wild goose chase. I really appreciate the time and effort you've put into this. I will try to code changes and let you know how things work out.
Thanks again. Sherry

XLGibbs
02-14-2006, 11:03 AM
Not angry at all! Don't misunderstand me.....I am NOT angry at all, just disappointed that the original code did not work as needed......

I have no problem help you out...

SherryO
02-14-2006, 12:31 PM
This is turning out to be a big thread!! I'm glad you're not angry, I didn't mean to disappoint you. I should have started with more of the real file, but it is so huge, so I tried to pare it down as much as possible, looks like a bit too much. I've attached the files that I am working with to get it to at least run again. I can't for the life of me figure out what was different the first time I ran it. I inserted the code in that I got from you. Could you test it and tell me what I'm doing wrong. Am I missing some reference? Thank you very much!

XLGibbs
02-14-2006, 01:06 PM
I will look at it when I get home, but since I tested on the files you first provided, why not test on those you first attached here?

That way you can see what the code is doing, and maybe catch the differences between the two files.

I can't download it at work, but will check it out tonight....

XLGibbs
02-14-2006, 02:13 PM
Sherry, I unzipped your files, opened them and executed the code in the source file. It worked fine, so I am not sure where exactly the problem occurs for you....wanna clarify a bit?

SherryO
02-15-2006, 09:51 AM
I needed to have Scripting Runtime and Office Runtime referenced. It works great now. Thanks for everything. Sherry

XLGibbs
02-15-2006, 01:56 PM
Glad to have helped you out. http://vbaexpress.com/forum/images/smilies/friendship.gif