VideoPhoto

26 October, 2021

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

25 October, 2021

Header/Footer: What can be entered there?

Excel offers the following built-in headers and footers. They can be added to your worksheet easily, with a single mouse click.

However, we're not limited to these options. In fact, you can enter any text/string into your worksheet cell and add it to any part of the header or footer with just one line of VBA code of a macro. E.g., this line will add contents of cell B2 to the left part of a header:

ActiveSheet.PageSetup.LeftHeader=Range("B2").Value

Such a line can be included in the VBA code I'm presenting at the bottom of this post.

20 October, 2021

In Reverse: Going Backwards with ARRAY formulas

Do you need to type or use expressions or numbers in reverse?

Trivial or not, in fact we need sometimes to display or read some numbers or text / expressions in reverse. There are several options available in Excel to do just that. You can do it in a macro way or using some known Excel formulas. E.g. you may use the following ARRAY formula for reversing digits in a number:

=SUM(VALUE(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))*10^(ROW(INDIRECT("1:"&LEN(B2)))-1)) 

However, at a closer look, the formula works fine with digits only. If you enter 12481632 into cell B2, it returns 23618421, but after entering 2.35 it returns error #VALUE! . In addition, it doesn't return trailing zeroes.

So, after some experimenting, I've created my own dynamic ARRAY formula. Here is its exemplary application:

The formula reverses whatever string, number or a mix of letters and digits you'd like to reverse. Just remember: this is an ARRAY formula, so - after typing it in - you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER on your keyboard.

 

17 October, 2021

TOP TIP: Counting Cells Containing Numbers Meeting Specific Criteria

Most of the time we organize our data in orderly Excel tables making their analysis pretty straightforward. Sometimes, however, we may have to deal with chaotic sets of numbers located all over different ranges/worksheets, like in this somewhat bloated example:

How to count and analyse them? Couple of examples follow.

14 October, 2021

How to Check if the Contents of Two Ranges/Worksheets is Different

You are not sure if some ranges of data in your worksheet, or in two different worksheets, contain the same data. How to  figure it out? And if they are different, is the difference significant?

Use ARRAY formula to check it out. You can count the differing cells using e.g. this kind of array formula in Sheet1 to compare with cell contents in Sheet2:

=SUM(IF(B2:G100=Sheet2!B2:G100,0,1))

Remember that to enter it you need to simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets.

Also, make sure that the size of compared ranges is the same. Location, however, can be different, like e.g. A2:F20 in one worksheet and C5:H23 in another.

13 October, 2021

To COUNT any Characters or Strings in any Range of Cells

Use  an ARRAY formula. You can find out the number of occurrences of anything (e.g. BS, 2016, s, graph, three days, 50%, etc.) within a given cell or a range of cells.

Assuming that your data range is e.g. A2:D100, select any cell outside that range and type the formula similar to this one:

 =SUM((LEN(A2:D100)-LEN(SUBSTITUTE(A2:D100,"Fig.","")))/LEN("Fig."))

and simultaneously hold down the CTRL and the SHIFT keys as you press ENTER. The formula will show up in the formula bar, surrounded by curly brackets. And the cell where you array-entered the formula will show the result, like here in cell G1:

12 October, 2021

Extract Formulas to External File

If your workbook/worksheet is full of Excel formulas it may be worth to keep track of them in one place, in a compact listing, in a separate text file.

Here is a simple short macro that serves the purpose:

Sub ListFormulas()
Dim rng As Range
'Extracts all formulas from a worksheet to a text file (Notepad/Wordpad)
Open "C:\Users\Adam\Desktop\XFormulas.txt" For Output As #1
For Each rng In Sheets("Formulas").UsedRange.Cells
If rng.Formula <> "" And Left(rng.Formula, 1) = "=" Then
           Print #1, rng.Address; Tab; rng.Formula
End If
Next
Close #1
End Sub