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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive