Sunday, June 1, 2014

yogi_Pull Select Data Into Another Sheet Except For Specified Columns And Columns With Blank Cells

                                         Google Spreadsheet   Post  #1661
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jun-01-2014
post by ghorning166: (!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

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