skip a blank or zero cell

ashie

New Member
Joined
Mar 18, 2002
Messages
18
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!!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top