skip a blank or zero cell
skip a blank or zero cell
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: skip a blank or zero cell

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am trying to create a table using lookup(the lookup table has 20 columns and in excess of 4000 rows)
    I have a lot of zero's or blanks being returned to me which creates a table just as big as the lookup table. Have tryed a pivot table then a lookup but there are too many columns for it to cope with.
    Help - am no good at macro's!!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ashie


    Try the Database functions. Excels help has some very good examples on these.


    I also have some examples here:

    http://www.ozgrid.com/download/default.htm

    Under: DFunctionsWithValidation.zip




  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thankyou for reply - DGET does work although in my case multiple values in my column meet the criteria - how do I get round this - have tried but to no avail

    Thanks

    Ashie UK

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why isn't it working ? I mean, what is it actually returning ? can you post some example data ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 13:09, ashie wrote:
    thankyou for reply - DGET does work although in my case multiple values in my column meet the criteria - how do I get round this - have tried but to no avail

    Thanks

    Ashie UK
    Hi Ashie:
    Use of Dfunctions will naturally return a single value. What I understand is that you would like to consolidate your table by omitting 0 values and blanks -- if this is the case then you ought to try DATA|FILTER|Advanced_Filter ... use criteria to filter values >0
    here is a small example

    table with unfiltered data (contains 0s and blanks)
    colA colB colC colD
    0001 1110 09.15 10156.50
    0002 1113 24.45 27212.85
    0003 1110 08.95 9934.50
    0004 2134 02.95 6295.30
    0005 5412 05.45 29495.40
    0006 0000 01.45 000.00
    0007 0012 01.29 015.48
    0008 0004 000.00

    now the same table with criteria to show only thhose rows that have a value>0 in columns B, C, and D
    colA colB colC colD
    0001 1110 09.15 10156.50
    0002 1113 24.45 27212.85
    0003 1110 08.95 9934.50
    0004 2134 02.95 6295.30
    0005 5412 05.45 29495.40
    0007 0012 01.29 015.48

    HTH
    Please post if it works for you ... otherwise explain a little further and let us take it from there!

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi ashie


    I feel you are over contiplating a relatively simple issue.

    If you are only wanting a table of your datbase without all the zeros and empty text there should be no reason why a Pivot Table could not handle this. By default a Pivot Table may well give you too much informaton and hence tell you that all the data cannot be shown. All you should need to do is place most of your headings in the PageField then as drag down one at a time and hide any info you do not want, eg Subtotals, Counts etc. Then go into Options for the Pivot Table and tell it to not show zeros and/or blanks.





  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Basically I have been given a spreadsheet that is 45 columns * 2500 rows for example (I do not own this spreadsheet so cannot change it!!)

    Column A = Purchase Order Nos
    Column B = Supplier Name
    Column C = PO Amount
    Column D = PO Date
    Column E = Spreads the PO Amount over the months it relates to - could be over 36 months (hence the amount of columns)
    etc.....


    My problem is that there is multiple suppliers, PO numbers etc.

    - I can do a sumif on these but what I need is an automatic table that for example lists a supplier and then lists all PO's and invoices with relating amounts on an individual basis.

    - A pivot table doesn't work because there are too many columns - I can't cut this down because all the data is relevant.

    Thankyou for taking the time to look at this - it is driving me crazy. I know it should be and is simple....

    Ashie UK

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-24 13:33, ashie wrote:
    Basically I have been given a spreadsheet that is 45 columns * 2500 rows for example (I do not own this spreadsheet so cannot change it!!)

    Column A = Purchase Order Nos
    Column B = Supplier Name
    Column C = PO Amount
    Column D = PO Date
    Column E = Spreads the PO Amount over the months it relates to - could be over 36 months (hence the amount of columns)
    etc.....


    My problem is that there is multiple suppliers, PO numbers etc.

    - I can do a sumif on these but what I need is an automatic table that for example lists a supplier and then lists all PO's and invoices with relating amounts on an individual basis.

    - A pivot table doesn't work because there are too many columns - I can't cut this down because all the data is relevant.

    Thankyou for taking the time to look at this - it is driving me crazy. I know it should be and is simple....

    Ashie UK
    Hi Ashie UK,

    Have you tried using sorting accouring to supplier and possibly date, and then using subtotals?

    It looks to mee that Data>Subtotals would work on the sorted data.

    Regards,
    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com