Tuesday, August 13, 2013

Google Spreadsheet Redesign Data with Arrayformula and Vlookup

Question:


( by Ilya12345 )

How do you arrange identical items with different corresponding values in the same rows alphabetically? e.g.:

Original:         A          B         C         D       E        F
                    Bricks    18    Nails      100    Clay     30 
                    Mortar   12    Mortar     27     Bricks   13 
                    Clay      13    Glue       3       Nails      44
                    Glue      17    Screws   500   Screws   20


I want to have:

                   A         B       C       D        E            F
                   Bricks 18       -         -        Bricks   13
                   Clay    13       -        -         Clay      30
                   Glue    17     Glue    3         -           -
                   Mortar 12     Mortar  27       -           - 
                   -          -       Nails   100      Nails    44
                   -          -       Screws 500    Screws 20


It's important to have each row devoted to only one name. Some cells will be left empty because there are no corresponding names in some columns.
I have a full sheet which I want to rearrange this way preferably automatically. 

(The items in columns come from other sheets through formulas: =EXPAND(ArrayFormula( 'Sheet1'!A:A)), =EXPAND(ArrayFormula( 'Sheet1'!B:B)); =EXPAND(ArrayFormula( 'Sheet2'!A:A)),=EXPAND(ArrayFormula( 'Sheet2'!B:B));=EXPAND(ArrayFormula( 'Sheet1'!A:A)),=EXPAND(ArrayFormula( 'Sheet1'!B:B)) etc.)

Thanks in advance.


Solution:


Have a look at the following screenshot of "Sheet1":




Have a look at the following screenshot of "Sheet2":


in the above sheet I have the following formulas in Cell:
A1:
=unique(query(vmerge('Sheet1'!A:B;'Sheet1'!C:D;'Sheet1'!E:F);"select Col1 where Col1<>'' order by Col1"))

B1:
=arrayformula(iferror(vlookup(A:A;'Sheet1'!A:B;{1,2}*sign(row(A:A));false)))

D1:
=arrayformula(iferror(vlookup(A:A;'Sheet1'!C:D;{1,2}*sign(row(A:A));false)))

F1:
=arrayformula(iferror(vlookup(A:A;'Sheet1'!E:F;{1,2}*sign(row(A:A));false)))

And if you don't want to have Column A as shown in above sheet2, then
Have a look at the following screenshot of "Sheet3":


in the above sheet I have the following formulas in Cell:
A1:
=arrayformula(iferror(vlookup(unique(query(vmerge('Sheet1'!A:B;'Sheet1'!C:D;'Sheet1'!E:F);"select Col1 where Col1<>'' order by Col1"));'Sheet1'!A:B;{1,2}*sign(row({}:{}));false)))

C1:
=arrayformula(iferror(vlookup(unique(query(vmerge('Sheet1'!A:B;'Sheet1'!C:D;'Sheet1'!E:F);"select Col1 where Col1<>'' order by Col1"));'Sheet1'!C:D;{1,2}*sign(row({}:{}));false)))

E1:
=arrayformula(iferror(vlookup(unique(query(vmerge('Sheet1'!A:B;'Sheet1'!C:D;'Sheet1'!E:F);"select Col1 where Col1<>'' order by Col1"));'Sheet1'!E:F;{1,2}*sign(row({}:{}));false)))



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

1 comment:

  1. Kishan, thanks very much for looking into this and writing the formulas. This is exactly what I needed. One thing I don't understand, when I try copying these formulas, I get errors in google spreadsheet. I wrote a list such as given above in Sheet1. In Sheet2 I inserted first formula in A1 and it gave me #ERROR. I tried different ways. Still I have trouble. Why could that be?

    Ilya

    ReplyDelete