Category Archives: Excel programming

Excel programming

A tasty Excel tidbit today

This is one of those things that I wish (Upon a star) that would just be automatic.

There are so many things in Excel that are automatic and a nuisance at the same time, I think we take for granted the automatic things in Excel that don’t annoy us.  How’s that for your riddle of the day!

I was recently filling out an expense form and I thought – Hey, I got a minute, let’s fancy this puppy up a bit (I say this to myself with a southern drawl accent…when I say these things to myself – it spices up my day a bit).

So on the expense report I added formulas to total the row and then total the column. But when you add the formula it places a 0 or $0 in the column – it’s the default (yuck).

From this… …to this.

There are a couple ways to deal with this:

  • change the formula to something like this: if(sum(e19:m19)=0,””,sum(e19.m19))     
    If you do this, however, you will could get an error if you were adding the column up

  • Use Conditional Formatting: 
    The catch here is that sometimes when you set the font to white in conditional formatting, when you print the text will show up. You won’t see it on the screen, but when creating a PDF and/or printing it will show there.

Select “Use a formula…”

Choose white for the text color

It will show you what you have defined but in this case it is to display white text, so it won’t show here.
  • cell format: Number -> Custom ->   $0.00;-0;;@,
    seems straight forward and to the point, but not unless you understand the format keys. But this is my preferred way to do this.

I know this is pretty rudimentary, but lately I’ve noticed that those are the things being overlooked these days.

 

Computer Programming, Excel programming

New Favorite thing in Excel – Frequency

I love it when I can learn a new thing in software program.

I have had to do this function many times before, however, I have never run across this option:  “Frequency”.

A B C D
1 Purchased   Colors
2 Green   Green
3 Yellow    
4 Green    
5 Red    

 

=frequency(B2:B10,D2) Results = 2

Counts how many times in B2 thru B10 that match what is in D2 – Awesome.

so, let’s add a little bit to this (I love things that do multiple things at one time (multi-tasking!) )

IF… you wanted to know how many unique items are in a column:

=SUM( — (FREQUENCY(B2:B5,B2:B5)>0))

—   =  Double-unary: A single unary operator (-) coerces true/false values into -1/0. By using the double unary operaor, we coerce the values again to 1/0.

This can be used in place of the countif statement for large sets of data.

Computer Programming, Excel programming

We can all stand to learn something at times

So I’ve been in the computer world for years, sometimes I think it’s too many years!

And I learned something the other day. I know it’s one of those things that we all say at one time “Dang, it would be nice if I could do this…” Well, I learned something in Excel … you can sort on more than just the value in the cell.

SortOn

 

I could have used this so many times, if only I would have known it was there. So there’s egg on my face on this one!!!!

Excel programming

Collect Area from multiple tabs in Excel Workbook

Every once in awhile I need to refresh my “geekiness” and do some programming.  This week I had a project to take an excel file with 200 tabs (each employee had a tab with their information on it) and combine it into 1 worksheet so that it could be imported into a database without retyping all the information.

So I “googled” to see if I could find something that would do it, I found a couple of close ones, many way off ones so I took the best of them all and made this one.

  1. In preparing to run this macro I went into the first employee’s sheet and made sure that all the information was in A1.I1  – I used the “=F45” to get the information from other places on the sheet to where I wanted it.
  2. I then copied that formula to all the tabs – tedious, but very effective.  So I know had all the information I wanted to pull over into my one worksheet in the same layout and place on each tab.
  3. Then comes the macro:
    1. Creates another tab named “Combined” – which is also Sheet1
    2. Go to each tab in the workbook (Sheets.Count)
    3. Highlights the area that I have the formulas collection the information on each tab (A1.I1)
    4. Copies it (Selection.Copy)
    5. Make Combined Sheet the active sheet (Sheets(1).Activate)
    6. Go to the bottom of Column A in the worksheet – skip up to the last row of data in column A (the best way to explain this is if you were in the worksheet in column A, GoTo (ctrl-G) A65536 (way down the column) press the End key and then the Up key and it will bring you to the bottom of the data you collected). Then paste the values of what you copied in #D above.
      Range(“A65536”).End(xlUp)(2).PasteSpecial xlPasteValues
Sub CollectIt()
 Dim J As Integer
 On Error Resume Next
 Sheets(1).Select
 Worksheets.Add
 Sheets(1).Name = "Combined"
 For J = 2 To Sheets.Count
   Sheets(J).Activate
   Range("A1.I1").Select
   Selection.Copy
   Sheets(1).Activate
   Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
 Next
End Sub