Excel Workshop Part 1

From Several Columns to a Single Column with a Formula

/in , /by

This formula is useful in case you have a data table in the following format and you want to transform it to “Preferred Results” format.

A-Data-1 B-Data-1 C-Data-1 D-Data-1
A-Data-2 B-Data-2 C-Data-2 D-Data-2
A-Data-3 B-Data-3 C-Data-3 D-Data-3
A-Data-4 B-Data-4 C-Data-4 D-Data-4

Preferred Results:

A-Data-1
A-Data-2
A-Data-3
A-Data-4
B-Data-1
B-Data-2
B-Data-3
B-Data-4
C-Data-1
C-Data-2
C-Data-3
C-Data-4
D-Data-1
D-Data-2
D-Data-3
D-Data-4

Excel Formula:

$A$1:$D$4 – change to your data range

A$7:A7 – change to the first cell where your results data begins. In our example that would be the cell holding A-Data-1 data.

Apply the formula to the first data results cell and fill down.

=INDEX($A$1:$D$4,MOD(ROWS(A$7:A7)+ROWS($A$1:$D$4)-1,ROWS($A$1:$D$4))+1,INT((ROW()-ROW($A$7))/ROWS($A$1:$D$4))+1)

Excel Workshop Part 1

Add a New WorkSheet in Excel – VBA Macro Code

/in , /by

This VBA code will add a new worksheet in your Excel project.

Sub AddNewWorksheet()

 Dim NewWorkSheet As Worksheet
 Set NewWorkSheet = Sheets.Add

End Sub