In the report you have two reporting dimensions. Both the dimensions may have values from A to G. If both the dimensions in the same record hold the value F, then this should be notified. In this case, I am not interested in reporting values, like sales volumes, but only in dimensions.

Easier solution is that is Excel just writes a mark next to dimensions.

To define the dimension values I have used RANDBETWEEN function.

=CHAR(RANDBETWEEN(65;71)) is returning a random value between A to G. Of course, you can manually enter any values, but the function is to automate and fasten the process.

In case your reporting dimensions are in C and D columns, the if sentence in cell E3 is

=IF(AND(C3=”F”;D3=”F”);”2F”;””)

IF-sentence checks that both C3 and D3 hold the value F, in this case, the IF-formula returns the value 2F. If C3 and D3 are not Fs, the IF does not return anything.

As a result the third reporting dimension is created with a new dimension value.

If you want that dimensions are changed to something else than original value F, if both the dimensions hold the value F, then we need macro to overwrite F values to eg 2F.

Sub FF2()

Range(“s3”).Select

Do Until ActiveCell = “”

If ActiveCell = “F” Then

ActiveCell.Offset(0, -1).Select

    If ActiveCell = “F” Then

        ActiveCell = “2F”

        ActiveCell.Offset(0, 1).Select

        ActiveCell = “2F”

    Else

        ActiveCell.Offset(0, 1).Select

    End If

End If

    ActiveCell.Offset(1, 0).Select

Loop

End Sub

The program starts with cell S3. This is the first record, the right hand side dimension. Program is executed till the end of the data range, till the program finds the first empty cell. If the program finds “F” in right side column, then the program checks also the left side dimension in the same record. If that is “F” too, then the program changes both F values to 2F values. If only the right side is F but the left not, then this record should not be overwritten. At the end, program has changed F value to 2F, if both the dimensions in the same record are F.

Now no new columns are added to report, but a new dimension value 2F is introduced, if both the dimensions are F in the same record.

The report leaves some cells blank, when report is downloaded to Excel. If cell is blank, the cell should have same value as the cell above. Eg. A5 should be 1, and B5 B, A6 should be 1, and B6 B. In order to have data coherent for Pivot, all the cells in the range A4:B25 should be populated.

You can tackle this issue by VBA or by Excel’s own functionality.

Activate the cells A4:B25 and execute the macro below.

Sub Emptycell()
Dim cell As Range
For Each cell In Selection
If cell= “” then
cell. FormulaR1C1 = “=R[-1]C”
end if
Next cell
End Sub

An other way is to use Go to functionality in Excel.

Activate the cells A4:B25.

In Excel select home and in right corner find & select and Go To. Or press control + G.

Press special -button.

Select blanks and press ok

Write equals shift + 0 and press arrow upwards once. This should be the result.

Press control + enter.

After populating the cells, I normally copy and paste as values, so that the values would not be changed anymore.