Macro to 1) Insert Rows when not a value changes, 2) Writes 4 formulas at the end of group

[rjw524]

Hi All,

I have an Excel spreadsheet that gets budget and forecasting data dumped into Column B from our database.

The spreadsheet is already set up to take the data dumped into whatever cell, in Column B and parse it into the corresponding row in Columns A and C because of a hyphen that is always in the Column B data.

Basically

Column B will the code for the budget item, followed by the hyphen and the amount charged to that budget item.

So for example if:

B1= 1001DEP-5150
(then A1= 1001DEP and C1= 5150

B2= 2001FIN-1200
(A2= 2001FIN and C1= 1200)

B3= 1001DEP-9800
(A3= 1001DEP, C3= 9800)

B4= 3009ACC-650
(A4= 3009ACC, C4= 650)

B5= 2001FIN-300
(A5= 2001FIN, C5= 300)

(and blah, blah, so on, etc, until the last row of data which, of course, is a random number from day to day.)

B24000= 10250-3009ACC
(A24000= 3009ACC, C24000= 10250 )


Now, as this short example demonstrates, this data dump could contain tens of thousands of rows.

Right now, I have a pretty crudely written macro that does the following:

MY MACRO:

1) First, sort Column A so all the Budget Codes are grouped together.

1001DEP
1001DEP
2001FIN
2001FIN
3009ACC
3009ACC

2) Go down Column A and when the value in a particular cell is DIFFERENT than the row above it, insert a blank row.

(for instance: before step 2
1001DEP
1001DEP
2001FIN
2001FIN

(After step 2
1001DEP
1001DEP

2001FIN
2001FIN


4) In Columns E, F, G, H, find and then go to the last row of the group (now with a blank row following it). And, to keep it simple, let's just say my macro inputs a formula to find the average, mean, max, & MIN calculation in each Column respectively.

(assuming that the now sorted and separated Budget Code grouping of "3009ACC" starts in A100 and comes up say 47 in this particular data dump*)

*IMPORTANT: THE NUMBER OF TIMES A BUDGET CODE SHOWS UP IN THE DATA DUMP IS NOT FIXED. IT'S A RANDOM NUMBER THAT CAN CHANGE FROM ONE DAY'S REPORT TO THE NEXT.

Column A
100) 3009ACC
101) 3009ACC
102) 3009ACC
103) 3009ACC
...
146) 3009ACC

Go to E47 and enter "=AVERAGE(A1:A47)"
Go to F47 and enter "=MEDIAN(A1:47)"
Go to G47, enter "=MAX(A1:A47)
Go to H47 and enter "=MIN(A1:A47)"


STEP 5: Repeat Step 4 until Macro has reached the end of the data.

MAIN PROBLEM: My macro TAKES FOREVER...literally 4 to 8 HOURS depending on the amount of rows in Column B and the number of different Budget Codes.

(I know there's a faster way to accomplish this automatically. It's just beyond my skills currently. I would imagine perhaps an Autofilter macro could work, but I'm not certain.)

In any case, I know it was a bit long, but I wanted to make sure I explained it thoroughly.

But if anyone out there knows how to handle this is in a Macro. that took MINUTES instead of HOURS, I would greatly appreciate it!

Thanks again,

R.J.

[mehmetcik]

I think i can help.

There are several approaches

One way to speed things up is to insert the formulae one row at a time and then copy paste values to get rid of them.


Ok Try putting your data in A and C as you describe

And then run my macro.

Please Login or Register  to view this content.

[rjw524]

Hi mehmetcik,

This is simply amazing. Thank you for taking the time to help!

I do have to ask another question of you however.

I actually have to use my formulas and I keep getting a run time error and can't seem to debug it.


This is what I would actually type into the last row of each "grouped" Budget Code in Columns E,F,G,H manually:

E29 =COUNTIF(A2:A29,"*.*")/COUNTA(A2:A29)

F29 =COUNTIF(A2:A29,"*%*")/COUNTA(A2:A29)

G29 =COUNTIF(A2:A29,"*&*")/COUNTA(A2:A29)

H29 =IF(MAX(E29:G29)=E29,"YES",IF(MAX(E29:G29)=F29,"NO",IF(MAX(E29:G29)=G29,"REVIEW")))

(I didn't come close to getting an answer trying to manipulate your code, lol.)

How would I have to alter the formulas above to work with the macro you provided?

(As you know, the "A2:A29" range I used in the formulas is just a static example. The ranges could be anything really. All depends on the data dump.)

Thanks, again!

R.J.

[mehmetcik]

Hi

I have added your formulae as requested.

They don't work for me, probably because I have the wrong Data.

Insert your data in columns A and B and run my macro.

Please let me know if it any faster.

Please Login or Register  to view this content.

[rjw524]

Hi Mehmet,

I wanted to say thanks for your help with this! I actually learned quite a bit from looking through your macro and fitting it to match my data.

This worked great!

Thanks again.

R.J.