Excel Spreadsheet Help Requested

[Anonymous]

Can someone with Excel spreadsheet expertise that's better than mine help me with a small problem:

I have a check-list where I check items that I need to buy (grocery list). Anything not checked, I delete that whole entry. I do this manually one at a time which is very tedious.

What I need is (I guess) a macro which I can apply to the whole spreadsheet that, if there's no entry in a cell in column-C, will delete the whole line.

I have no Excel training – I'm an "intuitive" (but not very) user of an old (year 2000) version.

[Anonymous]

[Michael GilliganParticipant @michaelgilligan61133]

If I understand the problem correctly, Peter … it would probably be more efficient, overall, to hide the row rather than delete it.

It’s a while since I did much with Excel, but I've just found this page which might help: **LINK**

https://social.technet.microsoft.com/Forums/ie/en-US/622a6e96-6378-4574-9dba-1bf3fb8d6fa8/hide-rows-in-active-sheet-if-cells-are-blank?forum=excel

MichaelG.

[SteviegtrParticipant @steviegtr]

I will not be much help. I used to do all my Electrical estimating using excel. I used to format columns somehow. But long forgotten since retirement.

It is not hard to do. Youtube may be your friend in this field or Microsoft as Michael above is pointing to.

Steve.

[Grindstone CowboyParticipant @grindstonecowboy]

Could be done, but possibly too complex to explain at a distance.

A simpler way might just be to sort on Column C, thus grouping all of the unmarked rows together, whence they can be selected as a group and deleted or hidden as preferred.

Rob

[John HaineParticipant @johnhaine32865]

What should be quite easy if your old version of Excel has the feature is to use the filter function. This is designed for exactly this. If you have a table of items with a column that has a tag against each row (say a 1 or 0), then you set up a filter on the whole range, then you can set the criteria so that only rows with a specific tag value are shown. You can change that to show all rows, or the rows with the opposite tag. A very basic database function

If your Excel version doesn't have this you could try LibreOffice Calc which is free and the latest version of that may have a similar function though I seldom use Calc.

[Clive FosterParticipant @clivefoster55965]

+1 for Robs suggestion of sort on C to put all the untagged items at the bottom out of the way.

I'd go a bit more sophisticated and do a combined sort with something in another column to put things in a sensible order. Alphabetical is possible but may well not give sensible results.

Presumably you print out the "thinks to buy" list or transfer it to your phone so you can take it with you. Setting print area or limiting the export will effectively hide the unwanted this time items.

Can't see any point in deleting items from the base spreadsheet. To me the big advantage would be in having a ready made list of everything you might buy ready to be sorted into this weeks list. Something I started but couldn't be bothered to finish as my shopping lists are pretty short.

I do use a multiple column sort in my financial spreadsheets. Most useful in what I call "Running Money" which keeps track of expected (pension) income and expected payments for a whole year. Starts with everything expected at the beginning of the financial year and I steadily update by putting what I've actually spent at the bottom then sorting into its rightful place in date order. So I've always got a very good idea of how much "uncommitted" spending money I have right now and how long it will take to save up for ……

Clive

[Michael GilliganParticipant @michaelgilligan61133]

This doesn’t really answer your question, Peter … but it’s another possibility

I did it in Apple’s “Numbers” App, but it should work the same in Excel

.

I’m showing the formula for cell D2, but the logic is similar for each of D2 through E4

MichaelG.

[duncan webster 1Participant @duncanwebster1]

Clearly Michael is a health food freak

[Martin ConnellyParticipant @martinconnelly55370]

I would go with filters as John suggested. Just right click on the first cell below the header row and select filter. You can select the criteria used to only show the rows required and then delete them as a block. Then clear the filter to show all. Sorting to lump all the ones you want to delete is more work than using a filter. Play around on a new spreadsheet to understand how to use a filter and then you will know how you want to use them in the way that suits you best.

Martin C

Edited By Martin Connelly on 01/12/2021 01:21:34

[Anonymous]

Thanks a bunch to all you helpful people – lots to think about.

A few explanatory points:

– the list is divided into sections with labels. Simply sorting all the blank cells to group them would screw that up. (Unless I could "unsort" afterwards).

– when I start a new list I load the base spreadsheet and then immediately save to a date-named file. The base spreadsheet is not altered and forms the master.

– either hiding or deleting the unwanted rows should be fine I think.

– I print the final list (my phone skills are even worse than my Excel skills).

– filters sound interesting. I think I poked around in there once before but not very seriously.

–

Edited By Peter Greene on 01/12/2021 01:47:39

[John HaineParticipant @johnhaine32865]

Given your operating method Peter, filters are exactly what you need. Open the base list, set the tag on all the items you want, apply the filter, and print the result.

[Alan Wood 4Participant @alanwood4]

Single tab as a pivot table is the simple solution.

You can leave all the items on display and sort by any column on clicking the heading.

You could even add a column to sort this week's shop in the order you go round the shop.

[AndrewDParticipant @andrewd]

This should do the trick:

Sub delLine()
Dim r As Long, i As Long

With Sheets("Sheet1"
r = .Cells(.Rows.Count, "A".End(xlUp).Row
For i = r To 2 Step -1
If .Cells(i, "C".Value = "" Then
.Rows(i).EntireRow.Delete
End If
Next i
End With

End Sub

**Please replace the smilies with close brackets. I can't seem to get rid of them**

Open the VBA editor by holding Alt and pressing F11.

Once open, select your spreadsheet in the upper left window and click on Insert > Module.

Copy and Paste the above code (Sub …. End sub) into the main window.

The code assumes that the last used cell in Column A is the total length of the list.

The code assumes that the worksheet is called Sheet1. If, as likeley, it isn't, then please change "Sheet1" in the above code to whatever the sheet of interest is called in your spreadsheet, preserving the quotes.

The code also assumes that your list starts on row 2. If not, please change the 2 on line For i = r To 2 Step -1 to wherever your list starts.

To run the code, open the VBA editor as before (Alt+F11), click the little green 'play' button. A window will appear. Select 'delLine' from the list (it may be the only thing on the list) and click 'Run'.

Alternatively, you can add a button to the spreadsheet to run the code but will need access to the Developer tab to do so. Please let me know if you would like help with this.

Edited By AndrewD on 01/12/2021 08:46:25

Edited By AndrewD on 01/12/2021 08:46:45

Edited By AndrewD on 01/12/2021 08:48:24

Edited By AndrewD on 01/12/2021 08:59:02

[HowiParticipant @howi]

read up on conditiional formatting, simple to use but very powerful.

use the fact that if a cell is blank, to reformat other cells to white text on white background (i.e hide entry) otherwise leave text as black on white.

there are lots of ways to do what you want, you just have to pick the one you find easiest to use.

when I worked in IT I did a lot with spreadsheats, what I couldn;t do with conditional formatting, I would do with a macro, I just feel that using macros is a bit of overkill.

[Alan Wood 4Participant @alanwood4]

Hi Peter

Further to my earlier message I have created a simple pivot table sheet for you to use. This allows you to keep a running list of all your shopping from multiple stores and with duplicate items from these stores. Each item has its own price associated.

To go shopping simply put a 'Y' in the column against the items you want this week and a quantity you wish to buy. When the sheet sees a Y it calculates the line price of that item. The total at the top of the page gives you your spend. You can leave your normal buy quantity there all the time – the Y entry is what brings everything into play for this week's shop.

The magic of pivot tables is that each column header has a drop down arrow to allow you to sort by that column. So if you click on the Buy header you will see a small dialogue box and if you tick just the Y items the sheet will just show the Y items. To get the full list back you drop the arrow again and select All.

Once you have all the Y items only selected you can then further sort by the column headers to sort them into which shop you are buying from today and the order in the shop that you will come to the items,

Once you have the list as you want it to be then select the area concerned with your mouse and do a Print Selection to have your list to take shopping,

If you need more columns or lines then use the Insert command but only while you are within the working area of the pivot table.

It sounds complicated but is quite simple once you are familiar with the use of the drop down selector to give you what you want. You cannot lose your overall list unless you do something really silly like delete the tab or the sheet.

There is one critical formula in the Total column that only creates the Total for the Y items. If you add extra lines you might have to copy this formula down.

Here is the link to my blog download page where you will find the Exel file in a ZIP file.

If you need more help with it let me know and I can send you a video tutorial file.

As an aside, this sheet could be readily adapted to a workshop asset list. I have seen a number of widows and family left with a workshop full of tools to dispose of and have no idea of their value before the wide boy workshop clearance team arrives and rips them off. Get it documented now with this simple sheet. Whether you put the price you paid or the price you told your wife you paid doesn't matter. At least get it documented to save them a bit less grief on your passing.

Alan

[Peter G. ShawParticipant @peterg-shaw75338]

Simple version – use a piece of paper!

Ok, I know that sounds facetious, but even in these days of supposedly cutting down on paper (and using electronics instead) we still receive more than enough junk mail to be able to find bits of paper for shopping lists. For example, almost every A4 envelope that comes through the door can be cut up to provide a blank A4 sheet of paper for use in the printer. And sometimes, there is a sheet of paper inside which has one side blank. Now ok, one wouldn't use these sheets to send letters, but for home use, eg shopping lists, they are more than good enough.

Cheers,

Peter G. Shaw

[Martin ConnellyParticipant @martinconnelly55370]

AndrewD, put a space between quotation marks and the bracket and they go away. Don't know if this affects the code you have written though.

Martin C

[AndrewDParticipant @andrewd]

Thanks! Too late to edit unfortunately.

 

Edited By AndrewD on 01/12/2021 15:27:54

[Anonymous]

Posted by Peter G. Shaw on 01/12/2021 10:42:34:

Simple version – use a piece of paper!

i.e. start with a blank sheet and add to it? And hope you haven't missed something(s)?

The whole point of the spreadsheet is that it starts with a sheet full of everything we ever buy and I select the specific items that we need this week. Nothing gets missed.

Necessarily though, on any given week, most of the items remain unselected and it is those that I want to remove from final working list to make it manageable (pocket sized).

Alan, my list is already in sections to group the items by type, which pretty much puts them in the order I go around the store.

(Some of the discussion in this thread is over my head.).

[Michael GilliganParticipant @michaelgilligan61133]

Posted by Peter Greene on 01/12/2021 22:55:56:

[…]

The whole point of the spreadsheet is that it starts with a sheet full of everything we ever buy and I select the specific items that we need this week. Nothing gets missed.

.

That’s the part where my little formulae might help … by providing a basic stock-control feature

Actually hiding or deleting a row though, doesn’t appear to be achievable with simple logic
IF […], THEN […], ELSE […]

Hopefully, someone will tell me I am mistaken, and show me how.

MichaelG.
 

.

Edited to kill the stupid smiley things

Edited By Michael Gilligan on 01/12/2021 23:14:34

[Anonymous]

Posted by Michael Gilligan on 01/12/2021 23:13:05:

That’s the part where my little formulae might help … by providing a basic stock-control feature

 

I couldn't really see what your formula did for me Michael …. but then I readily admit to being as thick as three short planks when it comes to spreadsheets.

I really thought there would be a simpler answer. There is an existing function (on the right click button) which says "Delete entire row" …. it's what I use to do it manually – either one row at at time or CTRL or SHIFT select for multiple rows. (And in all honesty, it doesn't take that long – it's just tedious).

I thought there might be a way of checking a cell and invoking the aforementioned function if the cell is empty.

 

| Edited to kill the stupid smiley things   "

 

(Wouldn't it be nice if we could do inline responses? Real ones I mean.)

Edited By Peter Greene on 02/12/2021 01:57:34

[Michael GilliganParticipant @michaelgilligan61133]

Posted by Peter Greene on 02/12/2021 01:55:02:

Posted by Michael Gilligan on 01/12/2021 23:13:05:

That’s the part where my little formulae might help … by providing a basic stock-control feature

 

I couldn't really see what your formula did for me Michael ….

.

Column B shows your current stock-count

Column C is the quantity that you like to have in stock

Column D makes the decision whether you should buy more

Column E tells you how many to buy [to restore your preferred stock-level]

.

If you print it out, complete with the blanks, then ; when the Supermarket has a special offer … you can easily make an informed decision ‘on the hoof’

MichaelG.

Edited By Michael Gilligan on 02/12/2021 07:07:14

[SillyOldDufferModerator @sillyoldduffer]

Posted by Michael Gilligan on 02/12/2021 07:06:26:

Posted by Peter Greene on 02/12/2021 01:55:02:

Posted by Michael Gilligan on 01/12/2021 23:13:05:

That’s the part where my little formulae might help … by providing a basic stock-control feature

I couldn't really see what your formula did for me Michael ….

.

Column B shows your current stock-count

Column C is the quantity that you like to have in stock

Column D makes the decision whether you should buy more

Column E tells you how many to buy [to restore your preferred stock-level]

.

If you print it out, complete with the blanks, then ; when the Supermarket has a special offer … you can easily make an informed decision ‘on the hoof’

MichaelG.

…

And if really keen, Michael's approach can be extended by adding more columns, such as:

Column F – Due-Out (quantity booked for later consumption, such as tomorrows dinner.)

Column G – Future Requirement (quantity known to be required in future, such as Xmas Turkey)

Column H – Due-In (quantity ordered in advance but not delivered yet)

Column I – Lead Time (time taken between reordering and actual delivery)

Column J – Shelf Life (time after which the item goes bad, such as Milk)

and many more…

I write lists on the back of an envelope and forget to take them with me…

Dave

[Author]

Posts