Microsoft Excel - Excel 2010 formulas & macros

Asked By Lisa G on 05-Dec-13 04:50 PM
Excel 2010 formulas & macros

I run a daily report on all 7 of our locations, each location has its own separate report. This means the number of data lines can vary widely, so any formula must be flexible enough to catch the data whether it is 5 lines or 150 lines (line # 1 is the header line). I am trying to reduce the number of formatting steps I need to do, before transferring the data into my final reports, by creating macros for multi-step formatting. Can anyone help me with any of the “bumps in the road” I am coming across?

The report format I have is 8 columns:
    A = blank 
    B = customer location (i.e. Q.56829-A)
    C = account #
    D= customer name
    E = order status
    F = service date (date of sale)
    G = service time (time of sale)
    H = hours:mins (since order placed) which comes across in this format: 1/2/1900 7:45:00 AM, but I have it pre-    formatted to display as 55:45 (hours:mins)
    I = payment method

Bump # 1:
I inserted the blank column A and I want it to display today’s date.  If columns B thru I, on the same line, are blank, I want the cell in column A to be blank, too (only fill in column A if there is data on that line).

    The steps I did, in order: 
      in cell A2: =IF(B2<>””,TODAY(),””)
      Format column A for date
      Copy A2
      Highlight Column A

But, I need to stop the paste at the first blank line. As a reminder, this is a daily report on 7 different locations, the number of lines will vary on each report, so setting it for a specific number of lines will not work. As a thought, do I need to change the sequence of events? For example, format the column for date before using the formula?

Bump # 2:
I sort the data by payment method and hours:mins (largest to smallest). I cut the lines for a specific payment type (purchase order) and place those lines further down the sheet (it needs to be worked with separately). I then need to go back to the non-purchase order lines and re-sort by hours:mins, so the longest times are listed on top (the “purchase order” lines are already sorted by time, from the first sort). I have the initial sorting in a macro, but when I try to program the second sort (separate macro), it re-sorts all of the data, not just the entries I have highlighted (I highlight first, before running the second macro). Is there a way I can program the second sort, in a macro, so it will sort only the lines I have highlighted?

Bump # 3:
In column H, I need to format in the number of days since the order was placed, not by hours:mins, if the payment method (column I) says “purchase order”. I am manually entering =DATEDIF(F2,A2,”D”) and format the cells for number, no decimals. Then I copy that down the lines. Logically, I should use =DATEDIF(IF(I:I,”purchase order”)(F2,A2,”D”)) but, it (or variations) do not work. Is there a formula that will automatically format column H if column I says “purchase order”, but will leave the hours:mins if not?

If all of these steps cannot be done in one macro, I can create as many as needed.

I apologize for this post being so long, I am just trying to be clear on the steps I need to do. Thank you for your help!
Harry Boughen replied to Lisa G on 05-Dec-13 10:46 PM
Hello Lisa,
Anything is possible.  I think you will have to use a macro to do what you want.
If you could give us a small sample of data and the output that would produce, it would help to clarify your description and any help could be better tested with 'real' data.
Harry Boughen replied to Lisa G on 06-Dec-13 12:26 AM
Hello again Lisa,
A few thoughts.
Bump#1 : In a macro you just have to get the number of rows that are filled and then copy the formula into the required range A2:Ax.  The following lines of code will set a range to the area used excluding the header
Set rngUsed = Range("B1").CurrentRegion
Set rngUsed = rngUsed.Offset(1, 0).Resize(rngUsed.Rows.Count - 1, rngUsed.Columns.Count)
You probably only need to set a variable to equal rngUsed.Rows.Count - 1 and then use that as the offset from A2 for the copy paste.
Bump#2: You would have to specify the range that you want to sort inside the macro either by setting it to the Selection or by other means.  If you have inserted a row between the two data sets you could use the current region code above but would have to allow for whether there is a header row or not.
Bump#3: If you just use this formula in column H and format as [h]:mm :-
You can use conditional formatting based on the contents of the adjacent cell in column I.  If the cell contains "purchase order" format the cell as number with zero decimal places.  This means that more than a day and a half will appear as 2.  If you want this to be handled other ways you can do it by using a conditional formula based on the content of the adjacent cell.
I will try to put some of this together in a sample spreadsheet and post it depending on whether I hear from you before hand.
Harry Boughen replied to Lisa G on 07-Dec-13 05:56 AM
Hello Lisa

This file is my take on what you want.  The time since order column is conditionally formatted depending on the payment method.  It includes a macro that finds the live data area and inserts the date as a formula in columnA.  It then sorts the data based on payment method and time since order.  I don't think you require any further sorting if I understand your text correctly.

The extra resort column is merely to allow easy return to the original order for testing purposes.

Obviously it all might need some tweaking to suit your real data.


Lisa G replied to Harry Boughen on 18-Dec-13 01:59 PM

Thank you, so much, for your help. It looks as if you went through a lot of trouble for me. I feel bad for what I am about to say ... this report has now been retired, by my company, for something entirely different. So, all of the work you have done is now a moot point. Hopefully, the new reporting format will be a bit easier for me. If not, I may be back for more help.

Thank you, again!