Rubik’s cube solution
December 24, 2011 Leave a comment
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  


Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! 

Matrix verstehen 
Copy it down to remaining Cells