## Tuesday, August 13, 2013

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.)

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