Excel: VLOOKUP Function Explained

One of the functions I use most often in excel is the "Vlookup" function.

This function has four parameter parts and is written as =VLOOKUP(para1, para2, para3, para4)

para1: the field you wish to use as the lookup value. Should be an entered value or a single cell

para2: the spreadsheet are that may/may not contain the value for which you are searching.  This is most often a series of 2 or more columns. Only the first column of the designated area will be searched for a match

para3: the index of the value you wish to return. Most often a single column.

para4: a true or false boolean.  If you want to only return an exact match enter FALSE otherwise enter TRUE or leave it blank to return anything that is similar.

As an example, I am using a list of customers with addresses and phone number. This data is contained in a customer listing spreadsheet.

In a new spreadsheet I have a shorter list of customer that I wish to return only the phone number for any customer that exists in the customer listing.

In cell B2 I have entered this formula =VLOOKUP(A2, 'CustomerListing'!B:G, 6, FALSE).

This formula is translated as follows:

Lookup the value in cell A2 from spreadsheet named CustomerListing. Look only in column B of CustomerListing for the value in cell A2 of the current spreadsheet. If a value is found return the value for the found row in column G. Why G? Because the index set in the 3rd parameter is 6. This tells the formula to move 6 columns from the first column to for the return value. B+6 = G.  Lastly the formula says to only return exact matches to cell A2 because False has been entered as the last parameter.  The formula will stop evaluating after finding the first match.

If no match is found for the specified value, N/A will be returned.

To finish the lookups for the remaining customers in the new list the formula can be copied down for the other cells in column B.  (NOTE: a quick way to do this copy is to double-click the small square in the lower right corner of the cell you wish to copy the formula for.  This will copy the formula from this cell to all empty cell going down until no value is found to the left)

I use the vlookup formula not only to move data across spreadsheets but most often to determine if a record exists in a spreadsheet.

Happy Exceling!!!

SQL Server: Incompatible version error message

 This week we pulled a database from a client site because they reported errors in a WPF application we created. When I copied the .mdf database into my environment and tried to run the C# code against it in visual studio. The following message was produced.

"The database 'myDatabase' cannot be opened because it is version 904. This server supports version 869 and earlier.  A downgrade path is not supported."

 This project is using the LocalDb as the datasource and apparently the client was using a newer version than what I am using in my development site.

You should always be cautious when upgrading a LocalDb database as they cannot be downgraded. And as usual always make backups of all databases before proceeding.

Warning! Doing the following steps will upgrade all existing localdb database and you will need to reattach them to the localdb. This can be done using SQL Server Management Studio or with command line.

Below are the steps I used to upgrade the LocalDb instance in my development environment.

Step 1: Install Newest version of MS SQL Server Express

Download and install the newest version of MS SQL Server Express

Use For the 2019 version to download click here. https://go.microsoft.com/fwlink/?LinkID=866658 

 

Choose the custom install option.

 Warning: As you move through the installation wizard at the step labeled "Features" you need to select the checkbox next to "localdb" BUT there is a bug in this installation package. If you click anywhere in this windo it will move you to the next step. So be careful to make sure you click directly on the checkbox.

 After you have successfully upgraded SQL Server Express restart the computer.

Step 1: Stop, Delete & Recreate LocalDb

When the computer restarts you will need to stop and delete the current version of localdb. And then create a new instance. This is the step will cause any existing database instance to detach from localdb.

Use command line for this step (cmd.exe).

 

To Stop LocalDb: "sqllocaldb stop MSSQLLocalDB"

To Delete LocalDb: "sqllocaldb delete MSSQLLocalDB"

To Create LocalDb: "sqllocaldb create MSSQLLocalDB"

The return message when a new instance is created should tell you the version of the instance created. The version for 2019 is 15.x.x.x.  If the correct version isn't created MS SQL Server Express was not upgraded to the correct version in Step 1. You will need to start back at that step. 

To Start the new LocalDb: "sqllocaldb start MSSQLLocalDB"

The localdb has been upgraded.  You will need to reattach any database instances.

It is also important to note all log files will need to be deleted. If they aren't you may encounter additional errors when running connection strings.

The easiest way to re-attach database is using SQL Server Management Studio (SSMS) which can be downloaded for free.

For my purpose I use C# in my project to reattach the database when the program is ran.

connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True"

Hope this helps someone.

  

How to Send a Group Message on an iPhone

The iPhone allows for "Group Messaging" (allowing a single message to be sent to multiple contacts).

To enable this on the iPhone, go to Settings > Messages > Group Messaging and turn it on.

Then to send a group message:

1. Open Messages

2.  Tap the create new message icon at the upper right.

3.  Tap the add contact icon at the upper right (looks like a plus sign).

Continue step 3 until you have added all contacts to your group.

4.  Type your message and tap the send button.

The message you typed will be distributed to all the contacts you chose in step 3.

Witty "Out of Office" Email

Some of the best "Out of Office" email automatic responses:

I am currently out of the office at a job interview and will replay to you if I fail to get the position. Please be prepared fro my mood.

You are receiving this automatic notification because I am out of the office. If I was in, chances are you wouldn't have received anything at all.

Sorry to have missed you, but I'm at the doctor's having my brain and heart removed so I can be promoted to our management team.

I will be unable to delete all the emails you send me until I return from vacation. Please be patient, and your mail will be deleted in the order it was received.

Thank you for your email. Your credit card has been charged $5.99 for the first 10 words and $1.99 for each additional word in your message.

The email server is unable to verify your server connection. Your message has not been delivered. Please restart your computer and try sending again.  (The beauty of this is that you will be able to see how many people did this over and over)

Thank you for your message, which has been added to a queuing system.  You are currently in 352nd place, and can expect to receive a reply in approximately 19 weeks.

I've run away to join a different circus.

Keyboard Special Characters

Here are some special characters you can get using the number pad on your keyboard:

Hold down the ALT key and enter the number on the number keypad.

      ☺ = 1

    ☻ = 2

     ♥ = 3

     ♦ = 4

     ♣ = 5
     
     ♠ = 6

     • = 7

     ◘ = 8

     ○ = 9

     ◙ = 10

     ♂ = 11

     ♀ = 12

     ♪ = 13

     ♫ = 14

     ☼ = 15

     ► = 16

     ◄ = 17

      ↕ = 18

     ‼ = 19

     ¶ = 20

VB.Net - Close Excel and Kill Process

How to make sure all Microsoft Excel processes are stopped after completing and Excel project using VB.net.

Here is a code example:

Dim app As new Application

Dim wb As Workbook

Dim sheet As new Worksheet

"Do Excel project"

sheet = Nothing

wb.Close()

app.Quit()

wb = Nothing

app = Nothing

For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")

    

     If proc.MainWindowTitle.Trim() = "" Then

          proc.Kill()

     End If

Next

This code will stop all Excel processes running on the machine.

Book of Corporate Life

1. In the beginning was the Plan.

2. And then came the Assumptions.

3. And the Assumptions were without form.

4. And the Plan was without Substance.

5. And darkness was upon the face of the Workers.

6. And the Workers spoke among themselves saying, "It
    is a crock of crap and it stinks."

7. And the Workers went unto their Supervisors and
   said, "It is a crock of dung and we cannot live with
   the smell."

8. And the Supervisors went unto their Managers saying,
   "It is a container of organic waste, and it is very
    strong, such that none may abide by it."

9. And the Managers went unto their Directors, saying,
  "It is a vessel of fertilizer, and none may abide its
   strength."

10. And the Directors spoke among themselves, saying to
     one another, "It contains that which aids plant growth,
     and it is very strong."

11. And the directors went to the Vice Presidents,
     saying unto them, "It promotes growth, and it is very
     powerful."

12. And the Vice Presidents went to the President,
     saying unto him, "It has very powerful effects."

13. And the President looked upon the Plan and saw that
      it was good.

14. And the Plan became Policy.

15. And that is how crap happens.