Google Spreadsheet Post #1661
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jun-01-2014
post by ghorning166: (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/0rPm2lLJcAg)
Removing blank cells and certain cells from an array to create a new list
I have a sheet called JOBS that contains about 100 columns, some of which are sums of other cells in the same row. Most of these cells refer to options the customer wants us to do for the job. So, each job has several options but only a few are chosen. Scrolling through the whole width of the JOB sheet is tedius so I would like to create another sheet (call it RESULTS) that condenses the options chosen for any particular business, to one simple list, For every option chosen in the JOBS sheet, the contents of that cell is displayed on the new RESULTS sheet.
The catch to this is, I don't want any blank cells, I don't want some of the columns (such as subtotal columns), and I want only the most recent job showing up.
Here is how it might look:
------------------------------
JOBS Sheet
Name Date ProductA Item Subtotal Service ItemB
Business 1 5/15/14 Product3 $44.00 Service88 Item44
Business 2 5/15/14 Product7 Item12 $25.00 Service89
Business 3 5/17/14 Product2 $14.00 Service88
Business 1 5/20/14 Product1 Item15 $35.00 Item55
Business 5 5/23/14 Product3 $44.00 Service89 Item22
------------------------------
RESULTS Sheet
Name: Business1 ( I will type the business name in to get the report below that I want)
Date: 5/20/14
ProductA: Product1
Item: Item15
ItemB: Item55
------------------------------
Notice above that Business1 has two entries in the JOBS Sheet but I only want the most recent. The subtotal column is skipped and since there is nothing in the Service column, that is also skipped in the report.
Any help would be appreciated. Thanks
---------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment