Excel Tip: Copying and pasting a row automatically

Regular readers of my blog will know that I am not normally a big fan of Macros. I would argue that in the majority of situations that I see them used, they are unnecessary and often result from a lack of understanding of how Excel can address the problem using its standard functionality.

Naturally this is not always the case and one particular situation that cannot easily be addressed using standard functionality is automatically copying a line of data from one row to another.

Before we get into it, I think it is important to point out that there are many times that I speak to a client who thinks this is what they need, when what they really need is a link between the two lists, or one list with a category field that can be used to report two lists from the same data.

I came across an example of when this was genuinely need just last week. In automating an accountancy practice's job tracking, I had brought all of their data together into three distinct lists - Clients, Recurring Jobs and actual Jobs.

This allowed them to store information at the right level:

Clients - One row per client (e.g. XYZ Ltd), holding all information pertaining to that client.

Recurring Jobs - One row per each recurring job (e.g. XYZ Ltd Annual Accounts), holding the current information relating to that particular recurring job.

Actual Jobs - One row for each actual job, which could be a one-off job or an instance of a recurring job (e.g. XYZ Ltd Annual Accounts 2014). This holds all information for that specific job.

Each Client, Recurring Job and Actual Job then has its own unique reference that is used to link them and access information from the other lists (using lookups and SUMIFs, etc.)

However, on the Actual Jobs list, we need a new instance each time a recurring job comes around. We also want to copy the recurring information over, with the ability to edit it just for this instance. That means copying and pasting a number of columns from Recurring Jobs to Actual Jobs each time.

This can, of course, be done manually, however that can be cumbersome and prone to error - not least in that in this particular instance I wanted to use "paste as values" rather than a full paste, as this could override conditional formatting that I had used on the Actual Jobs sheet.

However we can create a relatively simple macro to do it for us.

To add a macro you will need the Developer Ribbon which you can enable by selecting File, Options, Customize Ribbon and ticking the Developer option on the right hand side.

From the Developer Ribbon, click Macros and enter the name (e.g. CreateJob) that you want to give this Macro and click Create.

Then paste the following code between the lines already there, being Sub CreateJob() (or whatever name you gave the Macro) and End Sub:

    If ActiveSheet.Name = "Recurring Job Data" Then
    Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "F")).Select
    Selection.Copy
    Sheets("ActualJobs").Select
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    Range("A10001").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:1").Select
    Selection.AutoFilter
Else
Exit Sub
End If

The yellow items will need to be changed for your individual requirements:

Recurring Job Data is the name of the worksheet that we want to copy from. This part of the code, checks whether we are on this worksheet when the code is run, and exits the code if we are not. That stops the code running when we are on the wrong worksheet.

The A and F are the columns (from and to) that we want to copy over.

ActualJobs is the worksheet that we want to copy to.

A10001 is a cell in the first column of the worksheet that we want to copy to, beyond the bottom of the range of the existing data.

The code works as follows:

• Check the active worksheet is "Recurring Job Data" and exit the Macro if not
• Select columns A to F of the current row (the row that that the currently selected cell is in)
• Copy these cells
• Switch to the "ActualJobs" worksheet
• Switch off Autofilter (as if the list is filtered this may cause the code to incorrectly determine the bottom of the existing list
• Select cell A10001
• Go up column A until it finds some data and selects the cell below this
• Paste as values
• Switch Autofilter back on (assuming that the headings are in row 1)

Note that the code requires all rows that have data to have row A populated.

Once we have entered the code, we can select File, Close and return to Microsoft Excel.

Finally, we want to attach this code to a shortcut key. we can do this by clicking Macros again selecting our Macro from the list and choosing Options. Here we can enter the key that when pressed with the Ctrl key will activate the Macro. I chose J for job.

Then all we need to do to copy a recurring job to the ActualJobs sheet is click any cell in the row on the recurring job sheet and press Ctrl J.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Replacing parts of text strings

I have previously posted about using the Find and Replace facility to replace parts of text strings or formulae. But what if you want to be able to do this kind of thing automatically?

You're going to need to do it with formulae. The good news is that Excel has a couple of functions to do this.

Say you need to replace a section of a nominal ledger code (maybe a section the denotes a department or cost-centre).

If this part of the code will always be in the same place then we can use the REPLACE function.

The syntax for the REPLACE function is:

=REPLACE(OldText,StartNumber,NumberOfCharacters,NewText)

where,

OldText is the text we want to amend
StartNumber is the position in OldText at which we want to start replacing
NumberOfCharacters is how many characters of OldText we want to replace
NewText is the text we want to replace them with

So, say we wish to replace characters 4 to 6 of the string in A1 with the letters SAL, then we can use:

=REPLACE(A1,4,3,"SAL")

We may, however, not be able to rely on the department being in the same place. We can use the SUBSTITUTE function to replace a particular string with another.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(Text,OldText,NewText,[Instance])

where,

Text is the text string that we want to amend
OldText is the text string (within Text) that we want to replace
NewText is the text string that we want to replace OldText with
Instance is an optional field that can be used to specify which instance (as a number) of OldText should be replaced. If this is omitted all instances of OldText are replaced with NewText

So, if in our earlier example we know the existing code has a department of ADM, then we can use:

=SUBSTITUTE(A1,"ADM","SAL")

This will replace every instance of ADM with SAL, so be careful.

We can use the Instance argument, if we know that it is always the first instance for example:

=SUBSTITUTE(A1,"ADM","SAL",1)


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Eliminating #DIV/0! and other errors - an update

A while back, I wrote a post explaining an approach to eliminating #DIV/0! and other errors in Excel.

Although the information in the original post is still valid, there is a more streamlined approach you can use in more recent versions of Excel (Excel 2007 onwards).

But before we get into that, a quick reminder that Mynda Treacy's Excel Dashboards course is closing shortly, so get yourself over there if you don't want to miss out!

Anyway, on with the post...

If you want to read the original post (out of curiosity or to stay compatible with Excel 2003 and earlier), you can do so here,

Where the original post used a combination of IF and ISERROR, the newer approach uses the IFERROR function.

The syntax for the IFERROR function is as follows:

=IFERROR(value,value if error)

The value argument can be any calculation that may result in an error message, and the value if error argument is what you want to replace the error with.

So, say you have a margin percentage calculation that divides Profit in cell B5 by Sales in B2. You might use the following formula formatted as a percentage:

=B5/B2

However if, in some instances, there might be zero sales, then this function will return the #DIV/0! error.

Instead of seeing #DIV/0! in this case, we can choose what should appear. We may prefer to show zero percent, or a dash, or nothing, or a message such as "No Sales". We can do any of these with the IFERROR function as follows:

=IFERROR(B5/B2,0)

=IFERROR(B5/B2,"-")

=IFERROR(B5/B2,"")

=IFERROR(B5/B2,"No Sales")




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Using an Override List to Tweak Imported Data

This week's post is not so much learning a new Excel function, as learning a concept that you can use to enable you to import data and correct it, without overwriting the imported data. This is important, so that you can refresh the imported data later!

Before we get into that though, just a quick reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday, so if you're interested you need to get over there now.

Excel is a great tool for reporting on data held in an external database (this could be anything, such as an accounting system, ERP/MRP software or maybe a CRM system). However, sometimes we need the ability to edit or override the imported data with some tweaks that we wish to see included in the final reports.

In most cases, I would encourage any changes to be made in the external database, then the data to be refreshed, however this is not always possible. This can particularly be the case with transactional data (such as orders, invoices, etc.). Once an order is complete, most systems won't allow you to edit it, so if you need the order to be reflected differently in the final reports, this will need to be done in the spreadsheet.

You could over-type the imported data to make the changes, but this would be overwritten next time you refreshed the link to the external data. Far better to hold the changes in the spreadsheet to be applied to the data as it comes in. This is where I use what I call an override list.

As long as the imported data has a unique reference that you can link the adjustments to, you can use this approach. Simply have a separate sheet in the spreadsheet where you enter the references of any records that you want amended alongside the amendments. You can look up these amendments  in calculated columns alongside the data.

Say that you are importing a list of sales orders but that they are not always allocated to the correct salesperson in the external database. You could have an override list with the following two columns:

• Sales Order No
• Salesperson

Then for any orders that you needed to correct, you could type that order number and the salesperson that you wish to be allocated to that order.

Then all you need is a calculated column alongside the imported data that uses the new value if the Sales Order in question is on the list or the original salesperson if not.

Assuming that the imported data is in a table where two of the headings are Sales Order No and Salesperson, and the override list as described above is in the first two columns of a sheet called Override, then the following formula will work in the calculated column (which we might call CalcSalesperson:

=IF(COUNTIF(Override!$A:$A,[@[Sales Order No]])=0,[@Salesperson],VLOOKUP([@[Sales Order No]],Override!$A:$B,2,FALSE))

This uses an IF statement to check whether this row's Sales Order No appears in the first column of the Override sheet (The COUNTIF counts the occurrences of the Sales Order No in that column). If this returns zero, then the Salesperson field is used, otherwise the amended Salesperson is looked up (using VLOOKUP) from the override sheet.

Any references to the data (in PivotTables or SUMIF formulae for example) can now refer to CalcSalesperson, rather than Salesperson.

The specific formula will vary depending on what you are trying to adjust, but the principle remains the same - and the changes will be applied every time that particular Sales Order No appears in the imported data.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".