Move data to appropriate line, delete old

[jomili]

In the attached workbook I have two tabs, "As It Appears" and "As I'd Like It". The difference between the two has to do with the "Enc/Pre-Enc" column; the pivot that this comes from doesn't put the Enc on the same line with the expense because the Expense is associated with a contract in the source data, while the Enc is not. They really both should be.

What I want to do is take the Enc amount, put it on the same line as the expense for that Dept/Program Code/ReqID combination, then delete the line that originally contained the Enc amount, then recalculate the Grand Totals. I'm stuck trying to figure out the logic of how to do all of that via VBA. Any help on this is greatly appreciated.

Thanks,
John

[realniceguy5000]

Hi John,

Maybe something like this?

Please Login or Register  to view this content.

Let us know?

.

[jomili]

Holy smokes, you did it, and quickly to boot! I was having troubles trying to reconcile all the "Ifs", but your macro appears to have them all covered. I'll try this out with a few different examples, and if it works correctly for all (which I anticipate it will) I'll close this thread. In the meantime, please accept my thanks for a job well done.

God bless you,
John

[jomili]

Well, RealNiceGuy came up with a great specific solution, but I need it to be a bit more generic. I'll be running this on hundreds of pivots per year, and the pivots have varying amounts of columns. The first 5 columns will stay the same (different info, but the same headings), and the "Enc/Pre-Enc" and "Grand Total" columns will always be the last two. But a line like

Please Login or Register  to view this content.

won't work because my pivot may only have 10 columns, or may have up to 19.

Any help is greatly appreciated.

Thanks,
John

[jomili]

Okay, I've got a partial solution. I created variables to capture the last two columns, and plugged the variables in in place of the column numbers. The problem now is, if there's no "Req ID", then blank equals blank and lines are being deleted that shouldn't be. I'd appreciate any insight into this.

Please Login or Register  to view this content.

[watersev]

hi, jomili, I would suggest to make the following modifications to realniceguy5000 code:

1. Turn off screenupdating
2. Add variable holding number of columns used

I hope the code author will forgive my intrusion here. The code would look like this at the end:

Please Login or Register  to view this content.

[jomili]

Watersev,

Thanks for your code. I think I like your version better than mine, but it suffers from the same issue mine has. See the attached example. The lines in pink get deleted, and they really shouldn't be. Would you have ideas on how to get around that? I'm not sure why those two lines are being deleted; I THOUGHT I knew, but I was wrong.

[jomili]

Hmmm,

I may have fixed it myself. I added another pair of IFs, so if column1 matches, column2 matches, and column3 match it does it's stuff. I've tested it on 5 different pivots so far, and so far it's working well. I'd appreciate it if anyone can point to a potential problem with it.

Please Login or Register  to view this content.

[snb]

Who am I ?
Avoid writing operations to a sheet.

Please Login or Register  to view this content.

[jomili]

SNB,

I tried your macro, and found two issues with it on my first test. See the attached example.

1) The Grand Total column isn't being updated
2) The formatting of the cells doesn't translate over (not a big deal; I can restore it, but ideally I'd want this all to happen within one sheet, so the cell formatting would remain).

Since I'm very confused on the whole Resizing and UBound stuff, I don't understand your code enough to change it, but I'm concerned with the "15" in your code. If this is referring to the column numbers, we might have more than 15 depending on time of year (see my post of 5/20 10:34am).

I don't know what to make of your "Who Am I?" question, but I value your insight and expertise. But I'm completly lost with your "Avoid writing operations to a sheet" statement. I can see how in my macro (posted 5/20, 4:38pm) I DO write the new values on the same sheet, but don't see why that's a problem.

Thanks for your help and your insight.

John