Compare 2 Lists Visually and Highlight Matches

From Chandoo.org

Compare 2 Lists Visually and Highlight Matches:

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel. We have discussed various techniques for comparison earlier too,

• Compare 2 lists using conditional formatting


• Even faster way to compare lists


• Compare lists using row differences

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos.

And you have 2 burning questions,

1. Did we finish all the queries we had?

2. Should I go get some coffee?

Lets answer question number 2. Yes, you can get some coffee. Go, enjoy it now

Back already?!? Good. Now, lets answer the question 1.

Compare 2 Lists Visually using Conditional Formatting

[Note: this article is inspired by Reepal's comment.]

You would like to highlight the lists as shown below, so that you would know whether each product query is fulfilled or not.

Step 1: Create 2 more lists, with count of products

In order to compare our lists, we need some help. We will create 2 more lists like this:

How do we generate these lists?

Assuming our original data is in B6:B33 and D6:D33,

1. In a blank cell (lets say in F6), write =B6&COUNTIF(B$6:B6,B6)


2. This gives the count of first product up to that point, ie, Fired Forks1.


3. Now drag & fill the formula down until F33


4. Do the same in column H, but use the formula =D6&COUNTIF(D$6:D6,D6)


5. Fill this until H33

Step 2: Name these new lists

Now that we have created 2 more lists, lets give them names. Select the range F6:F33, go to Formula ribbon and click on “Define Name”. Name the range count1s

Do the same for range H6:H33 and name it count2s

Stpe 3: Apply Conditional Formatting to First List (Product Queries)

Now that we have done all the background work, lets visually compare the data. Select the first list (B6:B33) and go to Conditional Formatting > New Rule

We need to write a rule such that we would highlight all the items in list 1 whenever there is a match in list 2.

The rule is =COUNTIF(count2s,$F6)>0

It means, is the value in F6 present in 2nd list?

in other words, does the first product query has a corresponding product demo?

Set the formatting as you want. Click ok.

Step 4: Apply conditional formatting to Second List

Use the same logic, but this time the rule becomes =COUNTIF(count1s,$H6)

That is all, we have visually compared the two lists.

If you feel like, you can go back for one more cup of coffee.

Download Example Workbook

Click here to download the example workbook – Compare 2 lists visually and play with it. Examine the formulas in columns F & H. Also examine the conditional formatting rules to understand how this works.

How do you compare lists of data?

For me comparison is an everyday task. I rely in several techniques, some quick and dirty, others a bit more elaborate. For quick comparisons, I use either row differences or highlight duplicates rule. For elaborate comparisons, I use COUNTIF, VLOOKUP or other formula based techniques.

What about you? How do you compare lists of values? What techniques and tips you suggest. Please share using comments.

Want to learn Excel Formulas?

If you want to learn Excel formulas so that you can compare, analyze and present better, then please consider joining my Excel Formula Crash Course. This is an 8 hour online training program aimed to make you awesome in Excel formulas. We teach more than 40 every day formulas with loads of real-world examples, practice material & homework.

Click here to know more.

Excel Links – After a long time edition

From Chandoo.org

Excel Links – After a long time edition:

Long time readers of Chandoo.org know that we have a regular featured called as “Excel Links“, where I share some interesting Excel articles published around the web. This used to be a weekly feature, then became a monthly thing and later once a few months thing. As I had a hectic weekend, when I got up today, I did not know what to write. So here we go, one more edition of Excel Links. I am hoping to revive this feature and share links, at least once a month.

How to show filter criteria in Status Bar?

Mike shows us how to display the Auto Filter criteria in status bar when a list / table is filtered. Of course, he uses VBA to do this. Check out his code & learn something fun.

Convert Numbers to Words

Quite a few of you email and ask me, “How do I convert a number to words”. Well, here is your answer. Check out Debra’s tip to learn how to use UDFs to convert numbers to words. (And if you want to do the other way round, ie, words to numbers, you can check this out )

Embedding & Geo-coding Maps in Excel

Ever wondered how to use maps and geo-coding in Excel? Well, Tushar did and he provides an elegant solution for this problem.

Excel Rubik Cube Game

I love Rubik’s cube. Whenever I try to solve it, It puts me to sleep almost instantly. But some of you might stay awake and love to work on it. So you are going to love this. Rubik’s Cube in Excel.

Why Pie Charts are Burdensome

Pie charts are burdensome and we all know it. Sometimes, they just add too much chart junk that you would rather solve a Rubik’s cube than understand what the pie chart says. Here is one such poorly constructed pie chart (and a beautiful, simple alternative)

Doing SEO Competition Analysis using Excel

Here is one for webmasters. If you ever want to do some competitor analysis and want to automate it, you can use Excel (and VBA) to help you. A powerful macro to search Google for various keywords and analyze competition.

Do you want to share a link with us?

If you want to share a link with readers of Chandoo.org, leave a comment or drop me an email. I am always looking for new information, tips & ideas when it comes to anything related to Excel. You can email me at chandoo.d @ gmail.com.

I wish you a beautiful week ahead.

Split an Excel File in to Many using VBA [Videos]

From chandoo.org

Split an Excel File in to Many using VBA [Videos]:

Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.

Context:

Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.

Solution – Split Data in to Multiple Files using Advanced Filters & VBA

The process of splitting data can be broken down to 4 steps.

1. Identify the split criteria and list down all values in a small range. In our case, we list all the salespersons names in a named range lstSalesman.
   
   


2. Set up advanced filters so that we can filter the data by one salesman at a time.
   
   


3. Now, for each salesman, apply advanced filters and set it to copy the filtered values elsewhere.
   
   
   
   1. Copy the filtered values
   
   
   2. Add a new workbook and paste the copied values there.
   
   
   3. Save the new workbook with a unique name
   
   
   4. Repeat the above 3 steps for each salesman
   
   
   
   


4. That is all! You are done splitting.

Video Lesson on Splitting Data using Filters & VBA

Since splitting data in to multiple files requires a bit of macro code & advanced filter knowledge, I have created a short lesson explaining how this works. Watch it below.

[If you are not able to see the video, watch it on our Youtube Channel]

If you are new to VBA, take our crash course.

Download Split Data Example Workbook

Click here to download the split data example workbook.To use this,

1. Save the downloaded file to any folder.


2. Open the file and enable macros.


3. Examine everything and when ready, click on “Extract” button.


4. Check the folder where you saved the file and you will fine 4 new Excel workbooks named after the salespersons with the data extracted for them.

You can find the macro code in Module 1.

How do you Handle Splitting Situations?

In my work, I rarely had to split data. And whenever I had to split data, I usually copy paste the data after filtering what I want. But I can imagine many real life scenarios where you need to automate the splitting part.

How do you split data? What techniques and ideas you use to speed up the splitting process? Please share using comments.

More on Splitting & Consolidation

If you are in to splitting or combining things, we have a selection of tips & examples to help you. Check out these articles.

• Consolidating Data in Excel – a collection of techniques & tips


• Split Text on new line using VBA


• Combining Data using Excel’s Consolidate Feature


• Using 3D References to Consolidate Data

PS: Heck, we have even have an Excel tip to tell you how to split expenses among friends

PPS: You can use Pivot Table Report Filters if you want to split data in to multiple sheets.

VBA code to handle Access Imports and Query

From ExcelExperts.com

VBA code to handle Access Imports and Query:

Paste the following code in a general module

Public g_objConnection As ADODB.Connection
Public Const gc_strDBPath As String = "C:\Test.mdb"
Function blnConnectDatabase(strPath As String, strDBPass As String) As Boolean
'    If blnFileExists(strPath) = False Then
'        GoTo ErrH
'        Exit Function
'    End If
    Set g_objConnection = New ADODB.Connection
   On Error GoTo ErrH
   g_objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
       strPath & ";Jet OLEDB:Database Password=" & strDBPass & ";"
   On Error GoTo 0
   blnConnectDatabase = True
   GoTo ExitH
ErrH:
   blnConnectDatabase = False
   Set g_objConnection = Nothing
ExitH:
   Application.StatusBar = False
End Function
Function blnTableExistsInDB(strTableName As String) As Boolean
   Dim rst         As ADODB.Recordset
   Dim strTbl      As String
   strTbl = strTableName
   Call blnConnectDB
   Set rst = g_objConnection.OpenSchema(adSchemaTables)
   If Left(strTbl, 1) = "[" And Right(strTbl, 1) = "]" Then
       strTbl = Mid(strTbl, 2, Len(strTbl) - 2)
   End If
   rst.Filter = "TABLE_TYPE='TABLE' and TABLE_NAME='" & strTbl & "'"
   On Error Resume Next
   blnTableExistsInDB = (UCase(rst.Fields("TABLE_NAME").Value) = UCase(strTbl))
   On Error GoTo 0
   If Err.Number <> 0 Then blnTableExistsInDB = False
   Set rst = Nothing
End Function
Function ExecuteDBQuery(strQuery As String, Optional rngTarget As Range, Optional blnHeader As Boolean) As ADODB.Recordset
   Dim objRecordset                As ADODB.Recordset
   Dim intColIndex                 As Integer
   Dim lngRowOffset                As Long
   On Error GoTo ErrH
   Call blnConnectDB
   If Not rngTarget Is Nothing Then
       Set rngTarget = rngTarget.Cells(1, 1)
   End If
   Set objRecordset = New ADODB.Recordset
   With objRecordset
       .CursorLocation = adUseClient
       '.Open strQuery, g_objConnection, adOpenForwardOnly, adLockReadOnly ', adCmdText
        .Open strQuery, g_objConnection, adOpenDynamic, adLockOptimistic ', adCmdText

       If Not rngTarget Is Nothing Then
           If blnHeader = True Then
               For intColIndex = 0 To objRecordset.Fields.Count - 1 'field names
                    rngTarget.Cells(1, intColIndex + 1).NumberFormat = "@"
                   rngTarget.Cells(1, intColIndex + 1).Value = .Fields(intColIndex).Name
                   rngTarget.Cells(1, intColIndex + 1).Font.Bold = True
               Next intColIndex
               lngRowOffset = 1
           Else  'Without field names
                lngRowOffset = 0
           End If
           If Application.Version < 12 And .RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 65535 Then
               MsgBox "Records upto row number 65535 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           ElseIf Application.Version >= 12 And objRecordset.RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 1048576 Then
               MsgBox "Records upto row number 1048576 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           End If
           rngTarget.Cells(lngRowOffset + 1, 1).CopyFromRecordset objRecordset ' the recordset data
        End If
   End With
   Set ExecuteDBQuery = objRecordset
ErrH:
   Set objRecordset = Nothing
   If Err.Number <> 0 Then
       'MsgBox Err.Description, vbCritical, "Error"
        'MsgBox "Database Query Error"
    End If
End Function
Sub DropTable(ParamArray strTableName() As Variant)
   Dim x As Integer
   For x = LBound(strTableName) To UBound(strTableName)
       If blnTableExistsInDB(CStr(strTableName(x))) = True Then
           Call ExecuteDBQuery("Drop Table " & CStr(strTableName(x)))
       End If
   Next x
End Sub
Function blnConnectDB() As Boolean
   Dim blnCon As Boolean
   blnCon = True
   If g_objConnection Is Nothing Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   ElseIf Not g_objConnection.State = 1 Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   End If
   blnConnectDB = blnCon
End Function
Sub CompactDB()
   Dim lngRes As Long
   Call CloseDB
   lngRes = DatabaseCompact(gc_strDBPath)
   If lngRes = 0 Then
       'MsgBox "Succeeded in compacting database...", vbInformation
    Else
       'MsgBox Error(lngRes)
        Application.StatusBar = "Unable to clean database..."
   End If
End Sub
Function DatabaseCompact(strDBPath As String, Optional strDBPass As String = "") As Long
On Error GoTo ErrFailed
   'Delete the existing temp database
    If Len(Dir$(strDBPath & ".tmp")) Then
       VBA.Kill strDBPath & ".tmp"
   End If
   With CreateObject("JRO.JetEngine")
       If strDBPass = "" Then 'DB without password
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True"
       Else             'Password protected db
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";Jet OLEDB:Database Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDB:Database Password=" & strDBPass
       End If
   End With
On Error GoTo 0
   VBA.Kill strDBPath 'Delete the existing database
    Name strDBPath & ".tmp" As strDBPath 'Rename the compacted database
ErrFailed:
   DatabaseCompact = Err.Number
End Function
Sub CloseDB()
   If Not g_objConnection Is Nothing Then
       If g_objConnection.State = 1 Then g_objConnection.Close
   End If
   Set g_objConnection = Nothing
End Sub

Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

From Chandoo.org

Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?:

Filtering is a simple yet powerful way to analyze data. When you apply filters to any list of values, Excel gives you some really useful pre-defined filters so that you can analyze the data in a variety of ways.

So, assuming you have data like this:

We all know how to filter data for Bananas.

We also know how to filter data where Sales > 70

But, what if you want to filter data such that Fruit is Banana OR Sales is more than 70?

Sounds tricky, Right?!?

Well, not so tricky. We can use Advanced Filters to do just this (and more).

Here is how we can filter values with Fruit=Banana OR Sales>70

1. Insert a few blank rows above your data

2. We will use this space to define the conditions for our Advanced Filters

As you can guess, to use Advanced Filters, you must write down the conditions for filtering in cells.

3. Now, set up cells like this.

4. In first row, write =”=Bananas” against Fruit column

Note: we use =”=Bananas” instead of =Bananas because whenever you write = Excel thinks you are writing a formula.

5. In second row, write >70 in the Sales column

If you write this in first row, then the filtering would happen for Fruit=Banana AND Sales>70

6. Now, select any cell with actual data and go to Data > Advanced Filter

7. Select cells as shown below.

8. Click OK, and your list is filtered

Pretty cool, eh?

Some Tips about Advanced Filters:

• Use Copy to Another Location Option to copy the filtered values elsewhere.


• Excel creates a named range criteria upon the first time you apply advanced filters. As you can guess, this range contains the filtering criteria. With some creativity, you can dynamically change this (or create it) and make advanced filters even more advanced


• Do not select blank criteria rows: Make sure you only select criteria rows with some data in them. Otherwise, Excel will not filter.


• Use with VBA: Advanced filters are pretty powerful & very fast. So, if you need to process a large list and create a sub-list that meets a criteria, you can do that thru Advanced Filters and even automate the process with a bit of VBA (more on this during next 2 weeks).


• Few more advanced filter tips on Contextures: Debra shares some really nice examples on advanced filters. Check them out.

Download Advanced Filter Example Workbook:

Click here to download Excel workbook with Advanced Filter Example. Play with it to understand how you can filter like a fine coffee maker.

Do you use Advanced Filters?

I have rarely used advanced filters before writing this example. A reader’s email prompted me to learn this technique. And now, I am very eager to play with this so that I can share few more awesome implementations with you.

What about you? Do you use Advanced Filters? What do you use them for? What are your favorite tips & ideas? Please share using comments.

More Tips on using Data Filters:

• How to Filter Even or Odd rows only


• Use Filter by selected cell’s value to save time


• Create an awesome dynamic chart with data filters


• How to check a table is filtered or not using formulas


• Make an impressive product catalog using data filters


• More tips on using Data Filters

Offset() function to Calculate IRR for Dynamic Range

From Chandoo.org

Offset() function to Calculate IRR for Dynamic Range:
Offset() function to Calculate IRR for Dynamic Range

When you start the project can you be sure, for how long will you operate it? A VC gives you funds to buy a commercial project. You are to operate the project for some time and then sell it off! Can you tell me today, when you will sell?

Real world is dynamic and business situations keep changing! Your excel is not that dynamic, when you use the IRR function and tell it to calculate the IRR, you show fixed cash flows! These cash flows are dynamic.

Not to worry! We have Offset function to our rescue!

What is the Offset() function

In my opinion it is one of the most versatile (And dangerous) functions to use. On the face of it, it is a simple function – As the name suggests, it just offsets your reference.

Offset( range, rows, columns, height, width )






So in the illustrated example, it starts from the C8 cell, moves 0 rows and 0 columns and then gives an array of size 5 x 5 to the sum function!

The difference – Now Offset is NOT returning a value. It is returning references to arrays!

So what can you do with this function?

Ah… you can do a lot! It can change diapers of your kids as well . Right now we will see, how it can introduce unparalleled flexibility in your models.

So the VC we were speaking about – gives you USD 1000. You propose to operate the commercial complex for 4 years and post that sell it off for USD 1200.

But then you are not certain if the economic & business conditions would be such that you need to operate it for 4 or 5 or even 6 years. You want a flexibility in the model to “Dynamically Update” itself.




How do we implement this?

The first step is to find the last cash flow in the assumption. I do that simply by counting the number of cash flows. It indicates the number of years that you run the business.



Once I have the number of cash flows with my, I dynamically return the size of the cash flow range using the offset function and input that to IRR function.



Since the offset returns reference to the array of cash flows, I can give that as an argument to the IRR function and it gets me the IRR of the project!

If a new cash flow is entered, the count function would calculate it, pass it to the offset function, which would return the new range to IRR. Phew!!

Few more places, where you can use Offset function

Anywhere you want references to be returned, Offset function does come handy. Similarly I have found offset to be a very useful function if you are looking at creating scenarios, especially in Merger Modeling, Growth Assumptions, Economic Assumptions, etc. With the click of the mouse, it can completely update your model!

Beware

In my modeling experience I have found the following three functions to be quite versatile with the ability to surprise you with their power!

· Offset

· Indirect

· Index

They can return references. This is not usually what you expect Excel to do. So when you are not expecting they can return references to unknown ranges and surprise you. They can make understanding of model (for the reader of your model) fairly difficult and auditing your model difficult. So use them with care!

Do you use these functions?

These functions can make your financial model quite flexible. Do you use such functions in your models? Share your views and experience on making your model more flexible!

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration. Please click here to learn more about the program & sign-up. Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here. For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com