Search Not Just Numbers

Tuesday 24 February 2015

Excel Tip: Number occurrences in a list

There are a number of situations, where it is useful to number each occurrence in a list.

We could place a flag in column B next to each occurrence using an IF statement, e.g.

=IF(A2="North","Yes","")

Copying this down would place a Yes in column B next to each occurrence of North in column A, but it would not distinguish between each occurrence - they would all say Yes.

Fortunately there is a simple tip that you can use to place a number in column B, so that the first occurrence would show 1, the second 2, etc.

The principle is the same, we just need to replace "Yes" with something a little cleverer.

What we actually want now is to add 1 each time to the highest number already showing.

We can use the MAX function to find this highest number.

If we are again entering the formula in cell B2:

=MAX($B$1:$B1)

will return the highest number in the rows above (i.e. in cell B1).

Notice, though, the dollar signs. I have fixed both row and column on the start of the range, but only fixed the column on the end of the range. So copying this down, the end of the range would move, always going down as far as the cell above.

We can then use this formula, and add one, in place of "Yes" in our original:

=IF(A2="North",MAX($B$1:$B1)+1,"")

Whenever North appears in column A, this will find the maximum value in column B above this cell, and add 1 to it.

So, the first time North appears, there will be nothing above the row in column B, so 0+1=1. This will therefore return a 1 in column B.

Next time North appears, the maximum value in column B above this cell, will be 1 (returned against the first occurrence), so 1+1=2, etc.

We have all of our occurrences numbered in sequence now!

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".

Tuesday 17 February 2015

Excel Tip: Identify new entries in a list

I received the following email from a reader and I thought it was a common problem that might make a good blog post.

Gill Carnell of www.secretsofsoftware.co.uk sent me the following email regarding her work with the First Monday Business Network in North Yorkshire:

"I have been running a Free business network event for just over a year now.

I have built up a spreadsheet of approx. 150 unique names/emails of those who have attended on one or more occasions. This main list increases by half a dozen or so each month.

I paste the main list to create an invitation list to promote an upcoming meeting via eventbrite.

After a few weeks I export the event registrations to a smaller spreadsheet. It contains mainly names from my main list plus new entries who have registered for the first time.

My mission is to then send a second invitation to those on the main list who have not registered (not on small spreadsheet).

Currently I've used various combinations of VLOOKP and MATCH functions to find entries on both lists. But then I have to filter or sort and then copy resulting values to create the new mailing list.

Question is whether there is an Excel Function or you can recommend a technique that allows two spreadsheets to be compared and if duplicates occur remove both entries, so I am not inviting people who have already registered? Sort of List One subtract List Two"

I think the simplest approach would be as follows:

Add two new blank sheets to the spreadsheet that contains the Main List, so that you can paste the Registrations list into one of them each time, and use the second for the mail merge.

Add a new column to the Main List to count entries on the second list.

Say that the Main List is on a tab called MainList and  the email addresses are held in column D, whereas the Registrations are held on a tab called Registrations, and that these email addresses are in column F.

The formula in this new column (for row 2) could then be:

=COUNTIF(Registrations!F:F,MainList!D2)

This can then be copied down to ensure that it covers the whole list.

This will count, how many times that email address appears on Registrations. We are only interested in the zeros (i.e. those that don't appear on the Registrations List).

Switch on Autofilter for the main list (if it is not already switched on), and filter the Count column to only show zeros, then copy the list to the Mail Merge tab, which can be already set as the source for your mail merge.

Next time you do this, just delete the contents of the two spare tabs and paste the new registrations in. Your count column and Autofilter will already be set up and you mail merge will already be set up to point at the new list when you paste it into the mail merge tab.

I hope that helps Gill, and anyone else with a similar challenge.




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".

Tuesday 10 February 2015

Excel Tip: Find the last row of data

Do you ever have a need to find the last row in a set of data - or the first gap?

I find that I use this tip quite regularly. It might be to help define a range to copy, or set a print area, or to identify where to enter the next row of data, or it might simply be that the last item in a list is highly relevant to the spreadsheet I'm developing.

Whatever the purpose, it is very handy to have a formula that will give me this information.

Say we want to know the first blank row in column A. We can use an array formula to return the row numbers of all the blank rows.

I will not go too much into array formulae here as that would be a significant blog post in itself, but there are three important things to know:

  1. An array is a set of arguments or results, arranged into rows and columns, operations can be applied to the whole array
  2. The result of an array formula will usually be an array itself and therefore will need another function to specify how it is to be shown as a single result (without this, the first result of the array will be displayed). Typical functions might be SUM or AVERAGE.
  3. To enter an array formula, you need to press Ctrl+Shift+Enter, rather than just Enter
Our array formula to return the numbers of all of the blank cells in column A would be:

=IF(A:A="",ROW(A:A))

when we press Ctrl+Shift+Enter it will show as:

{=IF(A:A="",ROW(A:A))}

Note that entering these curly brackets manually will not work, they must be generated by using Ctrl+Shift+Enter.

This formula will return the array containing all of the row numbers that are blank, separated by FALSE, where they are not. So, if the first 6 rows of A were:

A1 23
A2 65
A3
A4 47
A5 12
A6

Then the formula would return the array {FALSE,FALSE,3,FALSE,FALSE,6}

However, all we will see in the cell is the first result, i.e. FALSE. What we need is a function to return the result we do want to see, which is the row number of the first blank row. This will also, of course, be the smallest (or minimum) number in the array. Excel provides the MIN function for just this purpose, so:

{=MIN(IF(A:A="",ROW(A:A)))}

will return 3, being the first blank row. Don't forget to use Ctrl+Shift+Enter to generate the curly brackets.

We can use the same logic to find the last non-blank row, which is often even more useful.
This time we want the highest (maximum) row number, where the contents are not blank, so our formula would be:

{=MAX(IF(A:A<>"",ROW(A:A)))}

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".

Tuesday 3 February 2015

Excel Tip: A short introduction to named ranges

Named ranges can generate intense debate among Excel experts as everyone has their own views about how often they should be used - from those who argue that you should never directly address a cell in a formula (and should always give the cell (or range of cells) a range name, to those who only use them if they have to.

I would put myself somewhere in between the two extremes. There is certainly no denying that they can be a useful tool.

I don't expect to resolve this debate here - I merely hope to introduce you to named ranges and how to use them.

Put simply, named ranges allow you to name a cell (or range of cells) so that this name can be used in a formula instead of the actual cell reference(s).

This provides the following advantages:

  • It can be easier to see what's going on in a formula if clear names are used;
  • If a range needs to be extended or moved, the range can be edited once, rather than in every formula that refers to the range;
  • Whereas inserting rows and columns, etc. will update both formulae referring directly to the moved cells and ranges, direct references from macros will not be updated. If the macro refers to a range name then this will not be a problem - as the range will have updated;
  • Dynamic ranges - the definition of a range does not have to be a fixed reference and can be a formula. It can therefore be dependent on other variables in the spreadsheet .
There are two main ways to define a range.

The simplest way (not possible for dynamic ranges), is to select the range with the mouse and enter the chosen name in the Name Box at the top left of the screen, just below the ribbon. This will give the selected cells that range name, and you can re-select those cells at any time by selecting the name from the dropdown in the Name Box.

Note that a Range Name cannot include certain characters (most notably, spaces), but you will be told if you have tried to allocate an invalid name.

The second way is to use Define Name on the Formulas Ribbon. In the dialog box that you see when you click Define Name, you can enter your chosen range name in the box at the top, and the selected range in the box at the bottom, which will default to the cell(s) you had selected before clicking Define Name. You will not normally need to use the other two fields in the dialog box, but if you wish you can choose to confine the scope of the range to one particular worksheet, rather than the whole workbook and/or add some comments.

Note that a Range Name cannot include certain characters (most notably, spaces), but you will be told if you have tried to allocate an invalid name.

The big advantage of the second method is that you can enter a formula in the "Refers to" box, rather than just a direct reference. This can give you a dynamic range, that changes with the contents of the spreadsheet. You may, for example, use functions such as OFFSET or INDEX to determine the dynamic range.

Finally, you can use the Name Manager (next to Define Name on the Formulas Ribbon) to edit existing ranges all in one place.

To use a range name in a formula just enter the name. So let's say we have named the range A1:A4 as sales. If we wished to total these cells we could type either:

=SUM(A1:A4)

or

=SUM(sales)

And say we entered the VAT Rate in cell B1 and named it vatrate. To return the gross sales including VAT, instead of typing:

=SUM(A1:A4)*(1+B1)

we could type:

=SUM(sales)*(1+vatrate)

but now we have all of the advantages mentioned above!




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".