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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: