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
B1=ArrayFormula(IF(ROW(A1:A)=1
C1=ArrayFormula(IF(ROW(A1:A)=1
D1=ArrayFormula(IF(ROW(A1:A)=1
E1=ArrayFormula(IF(ROW(A1:A)=1
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
Rens Duijsens
---
Share a document with data.... Okay, Here it is.....:
https://docs.google.com/
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.....
----------------------------------------------------------------------------------------------------------------------https://docs.google.com/
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"?
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
No comments:
Post a Comment