Wedding Dinner

When I was preparing for my wedding dinner , I have to count how many guests I have to invite, colleagues, ex-colleagues, Church friends, etc. All these names were pulled out from my memory. The worry was "Did I miss out anybody important?"

In business, we also have multiple list of names which we labelled them as customers. Where do they stay, how old they are, what products are they interested in, how did we get these customers. These are important information (or data) that will help you plan the type of new products to launch or the promotion that you wish run. You need to analyze them to make sure that the decision you make offers you the best returns.

Would you like to learn more? Sign up for my newsletter at https://www.excel-course.com/analyze-your-business-data-to-maximize-profit/

A new formula called TEXTJOIN

I was exploring how I could help one of my friends how to join text together that I stumble on this new formula called TEXTJOIN which was added a few months ago by Excel. With the formula, you are able to return multiple values in one single cell. More details can be found in this link https://exceljet.net/formula/multiple-matches-in-comma-separated-list

 

Does the cell contain any of the items in the list

One of my friends got an excel list and he wishes to find out in this list whether any of words he has in a second list can be found in the first list. He came out with a formula which is extremely long and find that it is impossible to use the formulas if his second list expands to 200 items. So he shouted for help to come out with a shorter formula. I didn't come out with one for him but googled the right key words to find somebody has done it. It is in this page https://exceljet.net/formula/cell-contains-one-of-many-things

 

Why you should not use VLOOKUP?

I was just pasting some data over from one worksheet to another in the same file. The destination worksheet contains a few thousand VLOOKUP formula. When I paste the data over, it takes quite a few minutes to complete the paste. This is because Excel keep re-calculating the VLOOKUP formulas as the data get pasted over. In this case, you can either turn of auto-calculation which I don't like to do because I usually forget to turn on after that. The other way is to use MSQuery to link the data together. The third and newer way is to use PowerPivot which can perform the calculation at a faster rate without the re-calculation problem.

Excel Table

Excel Table is a new function in Excel. It can organize information neater compared to a normal Excel worksheets. But one thing Excel Table cannot do is insert cut cells from another row of the table. For example, I would like to swop the values between E5 and E6. The way I would do it is to cut the cell in E6, go to E5 and press Ctrl and + key together. The values will exchange positions. But in Excel Table, this cannot be done.

VBA Excel – Goto Function

In one of the projects that I was working on, I wrote a set of Macros VBA code to select and highlight the cells that I want to keep. It behaves the same way as when the GOTO function is applied manually. The following code is what I used to highlighted the filtered cells yellow:

            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With

It was working perfectly well until I encounter a case when the filtered result returns zero row. The macros highlighted all the hidden rows instead. After searching high and low for the code to capture this scenario, I finally found that the way to do it is to use the COUNTIF function to count the number of records that the filtered results is likely to generate. This exceptional scenario that I encountered can be captured easily if the COUNTIF function returns the value zero which is zero count. To make it work perfectly in your codes, you need to run the COUNTIF first. If there is more than one count, then go ahead with the GOTO function. The codes to do that is given below. In the code below, I assume that I am trying count how many cells in K2:K100 contain the number 100. If there is at least one, continue with the GOTO Function. If not, skip it.

        xx = Application.WorksheetFunction.CountIf(ActiveSheet.Range("K2:K100"), "100")
        'Highlight cells yellow if auto-filter returns at least one row
        If xx > 0 Then
            ActiveSheet.Range("K2:K" & dlast_row).Select
            Set rCcells = Selection.SpecialCells(xlCellTypeVisible)
            With rCcells
                .Interior.Color = RGB(255, 255, 0)
            End With
        End If