Rubik’s cube solution

In my first range of data, I have a list of bloggers and the IDs of the campaigns they participate in:

BLOGGER | CP1 | CP2 | CP3
alice | 1 | 3 | 5
beth | 2 | |
chris | 1 | 2 | 4
dara | 1 | 3 |

Essentially, I need a formula to arrange the above data like so:

CAMPAIGN | BLOGGER
1 | alice
1 | chris
1 | dara
2 | beth
2 | chris
3 | alice
4 | dara
5 | alice

I’ve seen some of the other posts on combining arrays, etc, and I couldn’t quite apply it to what I’m trying to do here… so sorry if this question has been answered before ūüôā

Sheet2

  A B C D
1 BLOGGER CP1 CP2 CP3
2 ALICE 1 3 5
3 BETH 2    
4 CHRIS 1 2 4
5 DATA 1 3  
6        
7 CAMPAIGN BLOGGER    
8 1 ALICE    
9 1 CHRIS    
10 1 DATA    
11 2 BETH    
12 2 CHRIS    
13 3 ALICE    
14 3 DATA    
15 4 CHRIS    
16 5 ALICE    

Spreadsheet Formulas
Cell Formula
A8 =SMALL($B$2:$D$5, ROWS($A$8:A8))
B8 {=IF($A8<>"",INDEX($A$2:$A$5,SMALL(IF($B$2:$D$5=$A8,ROW($B$2:$D$5)-ROW($B$2)+1),COUNTIF($A$8:A8,A8))),"")}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
Matrix verstehen

Copy it down to remaining Cells

Advertisements

SUMPRODUCT FUNCTION AS COUNTER

COUNTIF and SUMIF are used to count and to sum when a single condition/criterion is involved.

In case of multiple conditions for both counting and summing you need SUMPRODUCT or array formulas.

Often you see SUMPRODUCT/array formulas built up with boolean terms. The following example will explain this

Let Assume the data is laid as below

 

A

B

1

A

B

2

A

B

3

A

B

4

A

B

5

B

A

6

B

A

The formula will be

=SUMPRODUCT(–(A1:A6=”A”)*(B1:B6=”B”))

will count records/rows that contain “A” and “B” values.

How it works? It genereates first 2 arrays like:

{TRUE, TRUE,TRUE,TRUE,FALSE,FALSE}*{TRUE,TRUE, TRUE,TRUE,FALSE ,FALSE}

and multiplying (Adding) these two, it gets:

sumproduct({1;1;1;1;0;0})

Multiplying logical values coerce Excel to treat TRUE as 1 and FALSE as 0.

Finally, the numbers in the last array are summed, producing a count.

INDIRECT FUNCTION IN EXCEL

In its simplest usage, the¬†INDIRECT¬†function allows you to put the address of one cell in another, and get data from the the first cell by referencing the second.¬† For example, if cell¬†A1¬†has the value “C3”, then¬†=INDIRECT(A1)¬†will return the value in¬†C3.

The real power of the INDIRECT function is that it can turn any string into a reference. This includes any string that you build up using string constants and the values of other cells in the formula, strung together with the & concatenation operator.   For example, the simple formula

=SUM(A5:A10)

will sum the values in the range¬†A5:A10.¬† However, suppose you want to be able to specify which range of rows to sum “on the fly”, without having to change the formula.¬† The¬†INDIRECTfunction allows you to do this.¬† Suppose you put your starting row cell¬†B1, and your ending row in¬†C1.¬† Then, you can use the formula

=SUM(INDIRECT(“A”&B1&”:A”&C1))

The argument to the INDIRECT function is

“A”&B1&”:A”&C1

If¬†B1¬†contains 5 and¬†C1¬†contains 10, this evaluates to the string¬†¬†“A5:A10”.¬† The¬†INDIRECT¬†function converts this string to an actual range reference, which is passed to the¬†SUM¬†function.

Another useful feature of the¬†INDIRECT¬†function is that since it takes string argument, you can use it to work with cell references that you don’t want Excel to automatically change when you insert or delete rows.¬†¬† Normally, Excel will change cell references when you insert or delete rows or columns, even when you use Absolute referencing. If you have the formula¬†=SUM($A$1:$A$10), and then insert a row at row 5, Excel will convert the formula to¬†=SUM($A$1:$A$11).¬†If you don’t want this to happen, use the¬†INDIRECT¬†function to change a text string to a reference:

=SUM(INDIRECT(“A1:A10”))

Since Excel sees¬†“A1:A10”¬†as a text string rather than a range reference, it will not change it when rows or columns are deleted or inserted.

This feature is important when working with some array formulas.  Frequently, an array formula will use the ROW() function to return an array of numbers.  For example, the following formula will return the average of the 10 largest numbers in the range A1:A60 :

=AVERAGE(LARGE(A1:A60,ROW(1:10)))

However, if you insert a row between rows 1 and 10, Excel will change the formula to

=AVERAGE(LARGE(A1:A60,ROW(1:11)))

which will return the average of the 11 largest numbers.¬† If we use the¬† function with a string, Excel won’t change the reference, so the formula will remain correct, regardless of whether and where rows are inserted or deleted.

=AVERAGE(LARGE(A1:A60,ROW(INDIRECT(“1:10”))))

You can use the INDIRECT function in conjunction with the ADDRESS function. The ADDRESS function uses row and column numbers to create a string address. For example, the formula=ADDRESS(5,6) returns the string $F$5, since $F$5 is the 5th row of column 6. You can use then pass this to  INDIRECT to get the value in cell F5. For example,  =INDIRECT(ADDRESS(5,6)) . While this example may seem trivial, it illustrates a technique that you can use to build more complicated formulas.