September 30, 2004

Duplicate Invoices

"Duplicate invoices - This seems to be problem for a lot of people and it affects me at times too so I thought it worth a post or two.

For ease of understanding, I'll use Conditional Formatting to highlight duplicates where they occur. (Data Validation can be used to prevent duplicates from being entered in the first place)

Sequential Numbers for All Customers
This is the easiest scenario. Here is a formula that will work shown in the Conditional Formatting dialog box.

=COUNTIF($C$2:$C$21,C2)>1



Sequential Numbers for Each Customer
A little more difficult. I get around this by joining (concatenating) the company names and invoice numbers (=B2&C2) in an adjoining column.

=COUNTIF($D$2:$D$21,D2)>1



You can add a number of conditions and just keep concatenating them to make sure you have just one invoice for each "unique" concatenated set of conditions.

Sound simple? Well, there are times when this won't work so I'll show an alternative way next time.  

Posted by andrewe at 01:06

September 27, 2004

Multiple Lookup 2

Sometime ago I posted about using SUMPRODUCT or SUM and IF as a multiple lookup. Here's a shot of SUMPRODUCT being used to find a person's age with the criteria of first, middle (initials) and last names.



These functions work well but not if the lookup values are text.



An alternative is to use INDEX and MATCH in an array (push Ctrl, Shift and Enter simultaneously when you enter the formula instead of just Enter and matching braces will appear, don't try to add them yourself)



It also works fine in the case that the lookup values are numbers, (such as age) also.



Just make sure if there is at least one unique criteria to compensate in the case that 2 or more people have similar names.  
Posted by andrewe at 22:18

September 24, 2004

Highlight with AutoShapes

Let's say you have something you want to draw attention to. Maybe it's something that's been changed or perhaps it's something you need to check.

Instead of using a regular comment, I often use an AutoShape placed over where I want to highlight and then make it semitransparent by right-clicking and selecting Format Autoshapes, then Colors and Lines. (There is better transparency control offered in later Excel versions - you can set the rate at a percentage with either manual input or using the scroll shown in the below picture)



Then I select the Properties tag and uncheck Print object.



You now have a "colorful" reminder which won't show up when you print but still enables you to see what's underneath.  
Posted by andrewe at 23:59

September 20, 2004

Errors and Zeros Revisited

I've posted about hiding errors and zeroes before, but here's a few more ways that you can use.

Hide Errors by Format
Select your range of cells, then right click to access the Format Cells dialog box.

Select the Number tag, then enter this Custom format.

[Black]General

Then change your font colour to the same colour as your cell background.

Hide Errors by Formula
The problem with using formats as above is that the font will appear if the cell background colour changes.

One way to overcome this is to add ISERROR to the formula you are using like this.

=IF(ISERROR(your formula),"",your formula)

Hide Zeros by Format
Select your range of cells, then right click to access the Format Cells dialog box.

Select the Number tag, then enter this Custom format.

General;General;

Don't forget the second semicolon.

Hide Zeros by Formula
As shown above with ISERROR, you can also hide zeros by adapting your formula like this.

=IF((your formula)=0,"",your formula)
  
Posted by andrewe at 23:35

September 16, 2004

Rankled by Rank

RANK is another one of those functions that is duped by duplicates.

Taking off from where I started last time, I have some numbers in cells B2:B21. In the adjoining column, I have the same numbers without the duplicates using my previous "duplicate ignoring" formula.

See Duped by Duplicates.

In the next column, I use this formula to return the rank in D2 and drag down.

=IF(C2="","",RANK(C2,$C$2:$C$21))

Here's how it looks.



Well, that's great, but what I really want is all of the original numbers to be ranked, so in the next column I add this formula.

=INDEX($D$2:$D$21,MATCH(B2,$C$2:$C$21,0))

Which gives me this. All numbers are ranked correctly.



Working with Criteria
Just working with the numbers may be fine but usually you will want them to relate to something like a name so you know had last month's highest sales figures or how many points were scored by your favorite sports team.

So let's modify my LARGE formula to refer to some names chosen at random (and laziness) in cells A2:A21. The name I want to match is "abc" in cell A2.

=IF(ISERROR(LARGE(IF(A2:A21=A2,C2:C21,""),ROW(INDIRECT("1:20")))),"",LARGE(IF(A2:A21=A2,C2:C21,""),ROW(INDIRECT("1:20"))))

It's an array formula so don't forget to push Ctrl, Alt and Enter at the same time when you enter it.



While I'm at it, I also enter the RANK function as per the below picture. (The LARGE formula has been entered in Column E only to make it look a bit neater)



Okay, that's it for a few days. I want to cruise the forums for a while and learn some new tricks. I gotta study too ;-)

Comments? Check here for details.  
Posted by andrewe at 01:12

September 13, 2004

Duped by Duplicates

I've noticed that certain functions don't return what I call "true ranking" when there are duplicates in a range.

Here's is an example. In cells B2:B21 I have 20 numbers and I want to sort them from the largest values first. To do this I have used an array formula which is =LARGE( B2:B21,ROW(INDIRECT("1:20"))). Note the braces at either end of the formula in the picture. You don't enter them yourself, they appear automatically when you push Ctrl, Shift and Enter which is how array formulas are entered (as opposed to just using Enter). Select your range first, then enter the formula as mentioned above.



So far so good. The problem is that any duplicates will cause inaccurate results. For example, I've now entered 200 into cell B7, which causes 200 to appear twice in Column C. Not good, the first and second largest values should definitely not be the same.



How to get around this? One way would be to use the wonderful Morefunc.xll add-in by Laurent Longre which contains 41 useful functions. For example, I have used the UNIQUEVALUES function here in an array to help solve the duplicates problem.



Well, this solved the problem very nicely, but unfortunately the same add-in must be installed in every computer where the file is used, so if this is the case, you might find yourself wondering if there is an alternative solution.

Here's a way that I thought up (probably not the best but it seems to work) First I'll enter a "duplicate ignoring" formula in cell C2 and drag down.

=IF(OR(B2="",COUNTIF(INDIRECT(ADDRESS(ROW($B$2),COLUMN($B$2))&":"&ADDRESS(ROW(),COLUMN()-1)),B2)>1),"",B2)

Here's how it looks.



Now I enter the same LARGE array formula as before like this.



Hmm... I don't like that #NUM! error at the bottom so I'll rewrite it like this.

=IF(ISERROR(LARGE(C2:C21,ROW(INDIRECT("1:20")))),"",LARGE(C2:C21,ROW(INDIRECT("1:20"))))

And here is the picture.



Well, that seems to do the job. Works for me!

By the way, you can read more about Mr. Longre and other Excel wizards at my friend Colo's site, Colo's Excel Junk Room, on the Cell Masters page.

G'night!  
Posted by andrewe at 23:49

September 09, 2004

AutoShapes in a Hurry

Here's a couple of quick tips to make working with AutoShapes easier.

Quick Tip 1
Drag any of the sub-menus from the main AutoShapes toolbar to keep it permanently displayed as a floating toolbar or drag it to dock against one side of your Worksheet.



You can also double click an AutoShape to insert them continuously. Click the shape again when you are finished.

Quick Tip 2
Once you have inserted your AutoShapes, you can save time by selecting all of them if you want to make the same changes to each.

Push the Select Objects button and drag around the shapes to include all of them.



Any changes you make to one will be made to the others also.

  
Posted by andrewe at 23:14

September 06, 2004

Formulas, Formats and Shortcuts

Today I finished a new section of my site called Formulas, Formats and Shortcuts.

The tips shown are the same as what are available on my Download Tips and the same rules apply.

1. Not all of them are mine. I try to give credit wherever possible.

2. I apologize for some of my "sillier" formulas, but sometimes writing them helps me learn. I appreciate any suggestions or corrections.  
Posted by andrewe at 00:36

September 03, 2004

Delete Blank Rows 2

By coincidence, I came across another way to delete blank rows today when I was using an AutoFilter at work. It deletes blank cells with formulas too.

I was using a formula to ignore duplicate entries in Column A (future tip) which in showed up as unique entries in Column B. In the case of duplicates in Column A, corresponding rows in Column B were left "blank", but they still contained my Ignore Duplicates formula.



Then I had the idea of using an AutoFilter. How it works is like this. Use the AutoFilter at the top and select Nonblanks.



Then copy the range and paste in another column.



Bingo, the entries without the blanks are now pasted as values. All that remains is to use the AutoFilter to select All once more.

PS. I could also use an Advanced Filter to show Unique records only, but I prefer this way because after I select All in the AutoFilter, all cells on the worksheet are visible again, including my selection minus the blank rows.  
Posted by andrewe at 01:52

September 01, 2004

Delete Blank Rows

Here's something I saw on a forum the other day. It's a quick non-VBA way to delete blank rows fast.

Ready? Push F5 (Go To dialog box shortcut), then Tab twice followed by Enter. Then push "K" and Enter once more. The blanks rows in your range should be selected.



Next push the Right Click key, (usually somewhere on the lower right of your keyboard), then "D", "R" and Enter.



One thing I noticed was that it although it works fine with cells that have Conditional Formatting or Data Validation, it doesn't work with formulas. Although the cells may look blank, by definition they are not.

Anyway, I thought it was a neat trick if you can use it, with practice you can do it quite fast.  
Posted by andrewe at 21:19