• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subtotal function - how to fill blank cells

P

Pam

New Member
Hi there,

Sorry to be posting an inquiry that is probably so basic, but I am stuck. I have a series of year end sales history reports for clients. The subtotal function works beautifully for us - we sort on the item code. The only issue is that the description is left off. I've googled a solution, and found one but it does not work consistently for the whole column. Can you help me to fill those blanks with the information from the one above?
 
Hello Pam,
Welcome to the forum!

Are you wanting to lookup the description from some other worksheet? Is the description used as part of the subtotaling?

Can you upload a sample worksheet to show what you are looking to do? You can upload a file using the "Upload a File" button.

Cheers,
Sajan.
 
Thank you for the welcome.

I'm embarrassed to say that I'm stuck on something even more basic. In the subtotal rows, i want the description to populate from the one above. See my attached workbook and I think you'll understand.
Probably should mention that I am working with Excel 2003.
 

Attachments

  • pams sample workbook 11-07-13.xls
    112.5 KB · Views: 9
No need to be embarrassed! We all started with basic stuff!

Regarding your question... try the following formula in cell B4
=B3

Then copy that formula to the other cells where a description is needed.

If that is not what you are after, please clarify.

Cheers,
Sajan.
 
Go to Data Ribbon, Outline Group. Click on subtotal, and change the dropdown to be each change in "Desc" rather than "Item".

Oops, just saw you are in Office 2003. Umm, I'm afraid I'm a little rusty...hopefully you know how to get back to the Subtotal menu? Apologies for not knowing the exact route.
 
Sajan said:
No need to be embarrassed! We all started with basic stuff!

Regarding your question... try the following formula in cell B4
=B3

Then copy that formula to the other cells where a description is needed.

If that is not what you are after, please clarify.

Cheers,
Sajan.
Click to expand...


Thank you, but I am trying to work it so that when I have the subtotal function display in outline at level 2, the item code and the description are both visible. And I'd like to figure out a formula that allows me to do that for the whole column at once. Right now, i'm having to copy down for each subtotal description.
 
Luke M said:
Go to Data Ribbon, Outline Group. Click on subtotal, and change the dropdown to be each change in "Desc" rather than "Item".

Oops, just saw you are in Office 2003. Umm, I'm afraid I'm a little rusty...hopefully you know how to get back to the Subtotal menu? Apologies for not knowing the exact route.
Click to expand...
 
Hi - thank you. But as I mentioned in my last post and should have included in my first - my objective is to have BOTH the item code and the description display.

And I do know how to get back to subtotal - but thanks for being so gentle!
 
Hi Pam,
Can you post what your raw data looks like (format wise)? It may be better to create a Pivot table for your data.

A Pivot table would be able to show the Item #, Description and Total.

Cheers,
Sajan.


Cheers,
Sajan.
 
Hmmm, have no experience with pivot tables. Here is the raw data. It's just the same spreadsheet with the subtotal function turned off.
 

Attachments

  • pams sample workbook 11-07-13.xls
    86 KB · Views: 5
Hi,
Attached is a pivot table of your raw data. (Some item codes had multiple descriptions.)

Cheers,
Sajan.
 

Attachments

  • Chandoo-pams_sample_workbook_11-07-13.xls
    149 KB · Views: 15
Or, taking Sajan's earlier Subtotal idea of using a formula to fill in blanks...
Assuming you've used the Subtotal still on Item Code. Select cells B1:B1256. Hit Ctrl+g to bring up the Go-to dialogue. Click special cells, Blanks, ok out.
Now, hit the "=" key, then the up arrow (to refer to cell directly above), and then hit Ctrl+Enter to confirm the formula into all cells currently selected. Format cells as desired.
 
Yes! I have been able to duplicate the pivot table process, but not the totally elegant result. I've uploaded the file to show you the result I get. I just want the three columns and no blank spaces in the rows.

Thanks!
Pam
 

Attachments

  • pams sample workbook 11-07-13.xls
    151 KB · Views: 9
Hello Pam,
I am not sure what the menu options are in Excel 2003...
but in Excel 2010, I am able to right click on "Item Code" in Row Labels to get the "Field Settings" menu, and from there, I am able to choose "None" for Subtotals. That removes the subtotals lines.

Hope that helps.

-Sajan.
 
Voila! It works! Sheer perfection! I am writing up instructions and I will be a hero! But you are my hero! Thank you SO much - saves SO much time!

Happiness!
 
Hi Pam,
Thanks for the feedback! Glad to help! Glad to hear that the instructions worked in Excel 2003!

-Sajan.
 
You must log in or register to reply here.
Back
Top