Hi everyone, today I received an interesting question by email which I wanted to share since it was not the first time I had received this type of question. In this specific case, it required me to use several of the functions that I have been writing about on this blog so I thought it would make an interesting case. First, let me explain what the user asked for. You will not have the exact same problem but I think that trying to understand it can help you a great deal. Basically, this user has a list of documents that you can see in column A. Then, there is a reference for that document and a date in columns B and C. What this reader would like is:

-For each document number, get the reference number for the most recent modification

The tricky part here is that most documents appear several times (becaused they were modified on several dates)

Alternatives

Problems like this are so interesting because there so many different ways to get them done. One way would have been to do a macro with a loop as I did a few weeks ago. I will however do it without a macro for this time, it should be as simple.

My first step is to simply sort the entries by date, this is the basis of everything that I will do, so here is a simple sort:

Then, I simply want to see for each line, is there a more recent entry for that specific number. In other words, is there a row, above the current one, that has this number. How would I do it? This concern does not exist for the first row, so cell D2, which will determine if such an entry exists will be simple:

“Y” so this means that “Y” it is the most recent.

For the next line, I would check by doing a vlookup. Vlookups are actually a great way of simply validating if a specific data exists. Here is how I will do it. First, here is my initial formula for D3:

=VLOOKUP(A3,$A$1:A2,2,FALSE)

I currently get an error since it doesn’t.

But really, what I need, is simply to know if it exists. It is difficult to work with errors. So I use if “if is error” fundction” and will replace this with:

=IF(ISERROR(VLOOKUP(A3,$B$1:A2,2,FALSE)),””,”Y”)

Basically, if there is an error (not found), I will enter “Y”. However if there is no error, it means that there is a more recent entry so I will leave it blank. I can then use this formula for the entire set, you can see the result here:

Then, you can simply add a simple “if” such as:

=IF(D2=”Y”,A2,””)
=IF(D2=”Y”,B2,””)

The end result is:

You can download the spreadsheet here.

When experimenting with personal finance and making money, it can be very frustrating to fail. Nobody wants to fail. Nobody wants to wait around forever. This is likely why get rich quick schemes are really popular these days (and ever since the beginning of the Internet). We all want to find a quick solution that will solve all our problems. The worse off that your situation is, the more likely that you are to want to find a way out. This is why these schemes target those that are looking to make money quick.

Do you really think that if there was a way to make money fast that someone would share it with you for $45? Would you share all of your secrets with complete strangers? Would you let everyone in on your secrets to riches? I highly doubt it. You might share your tips with your good friends, but you wouldn’t be quick to advertise on street corners and online.

Simply, there are many ways to get rich quick out there. A few ways that you can get rich quick include:

• Marry a rich person.
• Win the lottery.
• Make a celebrity sex tape.
• Sue a big company.
• Start off a small company and wait for them to go public.
• Hit it big at the casino.

Who would want to work if you can just get rich quick through one of these ideas? Well, the answer is 99,9% of us. How many of your friends have gotten rich quick? Do you know anyone that’s made money without working for it? I certainly don’t.

Does slow and steady win the race? I truly believe that it does.

When I finished with community college, I was out of school for 8 months while I waited for my credits to transfer to my new university. I took this time to find ways to get rich quick, as any 20-something would do. I searched for months on how I could make money quickly. I just couldn’t find any answers. It made no sense to me.

Why couldn’t I find a legit way to get rich quick? Because no such thing exists. I eventual found the personal finance blog, Get Rich Slowly. This was around 2008 and the blog was gaining tons of steam. I became interested in the posts and the idea of saving up money over time. I eventually adopted some of the core tenets from this blog. I realized that it made sense to save money slowly instead of trying to get rich quick in one shot.

I do hope that you set big and audacious goals. I also hope that you realize that slow and steady wins the race. You’re not going to start the next Facebook over night. The good news is that you can save up and build a nice savings account. You can plan to have what you want in life. You don’t have to fall behind and get sucked into scams that serve no purpose other than taking your money from you.

Ahh, the restive feeling of a man who just left a business retreat.  The team-building exercises, the delicious food, the “re-charged batteries of my soul.”

Ok, so it wasn’t exactly like that.  I did have a business retreat this weekend–as i explained in my prior post on the importance of business systems–but my company is just me.  It really made that team-building exercise where you fall back and learn to trust (when the other person catches you) quite difficult.

And instead of catered food, I had Boston Market.  And instead of relaxing the whole weekend, I pretty much worked 16 hour days trying to improve my business.  But I still feel oddly satisfied.  I can’t say my whole business has been “turned around,” but I do think there have been some improvements.  Let’s review some of the work I accomplished this weekend (along with, perhaps, an overview of my descent into madness (j/k)):

Drafting a Written Business Plan

When I left the professional group where I was working, I had a great plan: for starting my own business.  The startup phase is something I’ve thought about for years.  I’ve always wanted to have my own business.  People were even amazed at how quickly my business “came together.”  You know how sometimes a person will have their whole wedding planned out even before they’re even been proposed to.  Yeah, that was me.

The furniture was delivered as the business cards were hot off the press.  The website was up and it looked like my business had been around for decades. I had everything, everything I tell you!   Everything…..but clients.  Clients and any idea of how to actually run the business after the startup phase was over.

The next six months were a whirlwind of trying to learn the technology, customer service, accounting, and other practices that would keep my doors open.  It was, well….exhausting.  Totally exhausting.    I never took the time to write out an actual business plan.  Or to brainstorm the specific systems I should have in place within my business  to make it run smoothly.

This weekend I wrote out my business plan.  I brainstormed.  I ate Boston Market cornbread and I envisioned where my business is and where I want it to be.

In full disclosure, I must admit that I also did some soft and cuddly things like designing a “Firm Mission Statement.”  Then I called my wife who was out of town this weekend and she said she liked it (but probably cracked up as soon as she hung up the telephone).  But I also did some of the difficult strategic thinking my business has so long needed.  I decided upon what types of clients I would take versus which ones I would refer out.  I created the profile of my “ideal client.”  I mapped out short-term and long-term goals, and systems I would have to improve to make my business into something bigger than me.

Drafting a Written Marketing Plan

I also focused on how I can consistently generate new clients.  How does my website fit into the overall structure of my business’s marketing plan?  How can I ensure more referrals?  What systems should I put in place to create a larger sense of customer satisfaction?

Handbook

Then I started really going crazy.  I worked my Keurig machine so hard that it asked for a transfer. I put M83’s “Midnight Children” on permanent repeat and I dug deep into the inner recesses of my own entrepreneurial fetishism.  I sat down at my computer and in a caffein addled state of delirium had my Jerry Maguire moment. (“even the cover, looked like the Catcher in the Rye.”)  What did I do?  I wrote out a fifteen page company handbook.

It described everything about how my business should be run.  It even listed my vacation schedule for my employees.  And yes, I know I don’t have any employees.  But dammit!  I want to have employees!  That’s part of my business plan.  Yeah….it was kind of sad, in a way.  (in every way).

I outlined the telephone procedures for my business.  I created intake forms for the areas of my business.  I chastised non-existent employees for being rude to customers.  I almost cried with joy when I learned how to “hyperlink” within Word documents.  I redesigned my entire conflict list system.  I made a sheet outlining my pricing policies for different types of matters.  I set written standards and/or redesigned my systems of ordering supplies, backing up data, billing and invoicing, storing new contact information, filing, opening and closing business matters, and calendaring meetings and other documentation.

It was one wild ride, baby…one wild ride indeed.

Informational Pamphlets

Then I designed pamphlets providing some information about my business and the areas of my business.

I even spent some time reading The Essential Drucker, because I am nothing if not addicted to reading.  Then I woke up this morning and bought some more Boston Market.   And I’m not even sure I like their food.  But dammit that rotisserie chicken tasted different this morning.  It tasted like…..victory.