Copying imported data (from column to row) of another sheet by using an array formula.

A short intro into array formulas:
An array formula can perform multiple calculations and either a single result or multiple results calculation. Array formulas act on two or more sets of values ??known as array arguments. Each array argument must have the same number of rows and columns. You can create array formulas in the same way as other formulas, except you press CTRL + SHIFT + ENTER to enter the formula.
The array formulas subject is so vast that you can write a whole book about it.

We will copy the numbers and stars of the draw from the “Results” sheet with an array formula  to the “Control” sheet.

On the “Control” sheet, select the cells C4 to G4, Click on “Borders”, choose “All Edges”, with the cells still selected, type in = TRANSPOSE (click on the “Results” sheet, select the cells B9 to B13)  and press Ctrl + Shift + Enter.
On the “Control” sheet, select the cells I4 and J4, Click on “Borders”, choose “All Edges”, with the cells still selected, type in = TRANSPOSE (click on the “Results” sheet, select the cells B14 and B15)  and press Ctrl + Shift + Enter.

On the “Control” sheet, select the cells C7 to G7, Press the Ctrl button and select the cells I7 and J7, Release the Ctrl key, click on “Borders” and choose “All Edges”.
Select the cells L3 and M3, Choose “Merge and Center” click on “Borders”, choose “Outside Edges”, type correct numbers and press Enter.
Select the cells L4 and M4, Click on “Borders”, choose “All Edges”, type “Numbers” in cell L4 and “Stars” in cell M4 and press Enter.

Compare data using an array formula.

We will compare our numbers with the numbers of the draw. Select the cells L7 and M7, click on “Borders” , select “All Edges”. Click in cell L7 and type the formula = SUM (COUNTIF (C7: G7, C $ 4: G $ 4)) (We use the absolute cell reference for cells C4 and G4 because we may later need to copy the formula to the cells below) and press Ctrl+Shift+Enter. The formula now changes to an array formula {= SUM (COUNTIF (C7: G7, C $ 4: G $ 4))}.
If we wish to obtain the same result with a simple formula that would look like this: (all typed consecutively).

= COUNTIF (C7: G7, C $ 4) + COUNTIF (C7: G7, D $ 4) +
COUNTIF (C7: G7, E $ 4) + COUNTIF (C7: G7, F $ 4) + COUNTIF (C7: G7, G $ 4).

Click in cell M7 and type the formula = SUM (COUNTIF (I7: J7; I $ 4: J $ 4)) (Here we make the cell I4 and J4 absolute) and press Ctrl + Shift + Enter..
The formula now changed into an array formula {= SUM (COUNTIF (I7: J7; I $ 4: J $ 4))}
Obviously this yields 0, since we have no numbers entered in cells C7 to G7, I7 and J7

Profit searching through an array formula.

We will use an array formula to lookup our profit amounts.
We use the function = INDEX and MATCH =.

On the “Control” sheet, select cell O7, Click on “Borders”, choose “All Edges”, Type = INDEX ( click on the “Results”sheet, select the cells F20 to F31 and make it absolute (Press F4), typecomma and MATCH ( click on the “Control”sheet, select cell L7 , type “& “, select cell M7 and type comma “,”. Click on the “Results”sheet, select the cells B20 to B31, make it absolute (Press F4), type “&”, select the cells C20 to C31, make it absolute (Press F4), type comma and 0 )) press Ctrl + Shift + Enter.
The formula now changes into an array formula. (Here also, type everything consequetively)

{= INDEX (Results! $ F $ 20: $ F $ 31, MATCH (Control! L7 & Control! M7,
Results! $ B $ 20: $ B $ 31 & Results! $ C $ 20: $ C $ 31, 0)))}

The cells L7 and M7 of the “control” sheet are joined together with the ampersand (&), the ampersand makes as if it is one cell. We can also do this to the contents of the cells B20: B31 and C20: C31 of the  “Results” sheet, row by row.

As you can see from the result we get an error ie # N / A , this error indicates that a value is not available to a function or formula.
The formula with combinations 0 & 0, 0 & 1, 1 & 0, 0 & 2, 2 & 0 are not found in the cell area of Results! $ B $ 20: $ B $ 31 & Results! $ C $ 20: $ C $ 31
We therefore apply the formula as follows, click on the “Control” sheet, place the cursor in cell O7, type  IF ((L7 + M7)<3,””, (in the formula bar after the equal sign), if the sum of the cells L7 and M7 less than 3, it gives nothing (two double quotes in a row means empty or nothing). Place the cursor after the last closing parenthesis of the formula and type an extra closing bracket, and then press CTRL + SHIFT + ENTER
The formula is (all consecutive)

{= IF ((L7 + M7) <3, “”, INDEX (Results! $ F $ 20: $ F $ 31, MATCH (Control! L7 & Control! M7;

Results! $ B $ 20: $ B $ 31 & Results! $ C $ 20: $ C $ 31, 0)))}

Make cell O7 as Currency.

Select the cells C7 through O7 and drag the fill handle of cell O7 as many rows down as the number of combinations you have.
Place the cursor in a cell (no matter where), press CTRL + A(Select all), press Ctrl + B (All Bold) click Center, and then back in the cell, so now everything neatly centeres and in bold.
Right click on the “Results”sheet and choose “Hide”.
Right click on the “Sheet3” and choose “Delete”.
On the “Page Layout”  tab of the ribbon, check the ‘view’ boxes under “Gridlines” and “headings” (1)

Refresh Data

We naturally want to know how much we won in each new draw, so we refresh our data.
We can use 3 methods here, self refresh, auto refresh or refresh using a macro, we will not discuss the macro method here.

Method 1 self refresh:

Go to the “Data” tab in the “Ribbon” and click on the icon above “Refresh All” (2), and the data is refreshed.

Method 2 Automatic refresh:

Go to the “Data” tab in the Ribbon and click on “Connections” (1)

There is only one connection in the  “Workbook Connections” window, click “Properties” (1).

In the “Connection Properties”, change the “connection”name to an appropriate name, eg “Control EuroMillions’ (1) and click in the box next to “Refresh data when opening the file “(2), the rest we leave it as it is and click OK, and then click “Close”.

Now everytime you open the file the data is refreshed.

In cells C7 to G7, I7 and J7 and the underlying cells, you can fill your own combinations.

Save the file with a suitable name eg “Control EuroMillions” and good luck with Euromillions

Used functions:
As
Transpose
Index Match

FYI, Swotster.com thinks of his students, so do remember Swotster.com

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats