Monthly Archives: December 2021

Excel, VBA

Search and count

You have a data range and you should find how many times certain character exists within the data area.

This can be done easily with COUNTIF as follows.

The data range.

Copy the range and select data – data tools – remove duplicates.

Press ok.

The list of 12 values was shortened to 4.

The data range includes unique values D, C, A  and B.

The arguments for COUNTIF are the range where the value is searched and the value which is searched for.

However, if you want to just fast get a number how many times A appears in the data range, we can tackle that with VBA below.

Sub z_count()

Dim cell As Range

Dim arv, cnt

arv = InputBox(“Search term”)

cnt = 0

For Each cell In Selection

If cell.Value = arv Then

cnt = cnt + 1

End If

Next cell

MsgBox arv & “:” & cnt, vbOKOnly, “Search macro”

End Sub

The range selection consists of cell variables. Arv is the value to be searched for. Cnt is the counted value of arv. The data range is browsed through and every time arv is found, that is increasing the value of cnt by one. At the end, the cnt is printed in message box.

Activate the data range and execute the macro.

Select the value to be searched for.

The result is the same with COUNTIF. If you want to list all the values, the COUNTIF is practical. In case you repeat this process many times a day and you just want to get the value, VBA might be the worth of trying.

Excel

Flash fill

You will find flash fill functionality under data menu and there in a data tool box.

The icon has a flash in top left hand corner.

An example demonstrates how flash fill works.

In the B-column you have a list data. In the C-column you should take the digits after 123_. The C5 should hold value dr43 and so on.

Activate the C4 and press flash fill.

That saved some time.

When you press flash fill options, you will get following parameters.

Undo simply removes what flash fill just filled. Accept suggestion simply fixes the new values and the option box disappears. Select all 4 changed cells activates the cells C5:C8.

Another example is if you want to round down the numbers with several decimals. Just enter 423 manually into D3 and press flash fill.

Press flash fill.

The results are correct.

The same results can be achieved by ROUNDDOWN.

You have list of names.

You should create email addresses based on first name and last name. In D2 there is an example. Note that D2 needs be a static value not a formula.

Place the cursor in D2 and press flash fill.

It is difficult to find out all the examples when flash fill could be useful. In case you face a situation that you need to create a new column based on another column, just don’t forget flash fill. It might be useful.