Saturday, December 24, 2011

yogi_Rearrange Data In A Multi-Column Table And Present As Sorted Into Two Columns

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user rkarbowski said:
Formula Rubik's Cube: combining and re-arranging data
Hey all!
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 :)
Thanks much,
---------------------------------
following is a solution to the problem: