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

 

A Sum formula that always adds new rows

What do you do if you have a list and you constantly have to add the new records to the grand total using the SUM formula? If your SUM formula is immediately below the record, you will have to keep changing your range as the SUM formula would not include the new row added before the grand total row.

One work around is to add in an empty row and include that in your SUM formula. Whenever there is a need to add new records, add it before this blank row so that the SUM formula will always include the new records in the SUM formula.

What if you are not allowed to keep a blank row for this purpose? Today, I am going to show you a new solution.

Assuming that the SUM formula is in A11 and is currently adding the numbers in the range A1:A10. Instead of creating a SUM formula that simply adds up the range A1:A10 [=SUM(A1:A10)], create the range in text format and use the ROW formula. The range including the inverted commas becomes "A1:A"&Row()-1. The ROW()-1 will always calculate one row before the SUM formula. Since this is a text and cannot be read as a range, you need to the INDIRECT formula to convert the text based range into a proper range. Adding the INDIRECT and SUM formula, you new SUM formula should be =SUM(INDIRECT("A1:A"&ROW()-1)). This new SUM formula will save you the time to change the range constantly and you will not have to live with an empty row between your data and the grand total anymore.