Sunday, November 2, 2014

yogi_Pull Into Another Sheet Invoices Not Sent And Sort Output By Needed_For Column

                 Google Spreadsheet   Post  #1818
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-02-2014
post by  Rens Duijsens:
(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/PNsuw8RCymM)
Remove the empty lines in a ARRAYFORMULA result and make results manually manipulative.
Hello,

I have a worksheet called "Administration" with the following data:

     |============||=============|============|==============|==============|==============|==============|
     |     A      ||      B      |     C      |       D      |       E      |      F       |       G      |
     |============||=============|============|==============|==============|==============|==============|
|  1 |Date        ||Ordered at   |Needed for  |Invoice to    |Invoice send  |Description   |Invoice amount|
|  2 |  10-10-2014||ACME         |Project_A   |John Do       |No            |Descr_1       |       $100.00|
|  3 |  12-10-2014||ACME         |Project_A   |John Do       |Yes           |Descr_2       |       $100.00|
|  4 |  14-10-2014||ACME         |Project_B   |John Do       |No            |Descr_3       |       $100.00|
|  5 |  15-10-2014||ACME         |Project_B   |John Do       |Yes           |Descr_4       |       $100.00|
|  6 |  15-10-2014||ACME         |Project_A   |Jane Roe      |No            |Descr_5       |       $100.00|
|  7 |  17-10-2014||ACME         |Project_B   |Jane Roe      |Yes           |Descr_6       |       $100.00|
|  8 |  17-10-2014||ACME         |Project_C   |John Do       |No            |Descr_7       |       $100.00|
|  9 |  17-10-2014||ACME         |Project_C   |Jane Roe      |No            |Descr_8       |       $100.00|
The header is in a fixed row. Column A is fixed as well
All columns have range names assigned to them
A2:A = adm_Date 
B2:B = adm_OrderedAt 
C2:C = adm_NeededFor 
D2:D = adm_InvoiceTo 
E2:E = adm_InvoiceSend 
F2:F = adm_Description 
G2:G = adm_InvoiceAmount 



I have a second worksheet called "Invoices" that looks like this:
     |============|=============|============|==============|==============|
     |     A      |      B      |     C      |       D      |      E       |
     |============|=============|============|==============|==============|
|  1 |Invoice to  |Ordered at   |Needed for  |Description   |Invoice amount|
|  2 |            |             |            |              |              |
|  3 |            |             |            |              |              |
|  4 |            |             |            |              |              |
|  5 |            |             |            |              |              |
|....|            |             |            |              |              |
The headers are in a fixed row.

In the header cells Array formula's are placed to fill the rows below.
A1=ArrayFormula(IF(ROW(A1:A)=1;"Invoice to" ;IF(adm_InvoiceSend <> "Yes";adm_InvoiceTo ;) ))
B1=ArrayFormula(IF(ROW(A1:A)=1;"Ordered at" ;IF(adm_InvoiceSend <> "Yes";adm_OrderedAt ;);)) 
C1=ArrayFormula(IF(ROW(A1:A)=1;"Needed for" ;IF(adm_InvoiceSend <> "Yes";adm_NeededFor ;);))
D1=ArrayFormula(IF(ROW(A1:A)=1;"Description" ;IF(adm_InvoiceSend <> "Yes";adm_Descrption ;);))
E1=ArrayFormula(IF(ROW(A1:A)=1;"Invoice Amount";IF(adm_InvoiceSend <> "Yes";adm_InvoiceAmount;);))
I fill the headers with these formula's so one could copy all rows below the fixed rows as one pleases without copying the formula's. 
Works great. :-)



What comes out looks like this:
     |============|=============|============|==============|==============|
     |     A      |      B      |     C      |       D      |      E       |
     |============|=============|============|==============|==============|
|  1 |Invoice to  |Ordered at   |Needed for  |Description   |Invoice amount|
|  2 |John Do     |ACME         |Project_A   |Descr_1       |       $100.00|
|  3 |            |             |            |              |              |
|  4 |John Do     |ACME         |Project_B   |Descr_3       |       $100.00|
|  5 |            |             |            |              |              |
|  6 |Jane Roe    |ACME         |Project_A   |Descr_5       |       $100.00|
|  7 |            |             |            |              |              |
|  8 |John Do     |ACME         |Project_C   |Descr_7       |       $100.00|
|  9 |Jane Roe    |ACME         |Project_C   |Descr_8       |       $100.00| 
| 10 |            |             |            |              |              | | 11 |            |             |            |              |              | 
|....|            |             |            |              |              | 
So.... At first glimpse, all works fine.
But I would really like to suppress the empty lines.
I cannot edit or reorganize any lines by hand, since they will be re-filled automatically as they where.

Question 1: Is there s simple way to suppress the empty lines?
Question 2: How can I enable for instance sorting by hand, at (lets say) column C by clicking the down arrow in the sheet en select "Sort sheet A->Z"

Friendly greetings
Rens Duijsens
---
Share a document with data.... Okay, Here it is.....:
https://docs.google.com/spreadsheets/d/1Do_fu5v17XzAN4nVR11BvguWpe9bmgy3VCOv7cdRdFA/edit?usp=sharing

I hoped I had pretty clear explained what I'm trying to do.
What would you like me to explain more? I will is you let me know what info you are missing.....

What am I trying to accomplish:
1: Like I said in the first post--> How do I get rid of the empty lines
2: And second-->How do I manualy sort by clicking the down arraw next to the "C" and select "Sort --> A-Z"?
----------------------------------------------------------------------------------------------------------------------
in the following solution I have used a single formula which also includes sorting by column C automatically