skip a blank or zero cell

[ashie]

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!!

[Dave Hawley]

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

[ashie]

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

[Juan Pablo González]

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

[Yogi Anand]

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!

[Dave Hawley]

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.

[ashie]

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

[Jay Petrulis]

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