Peter Greene | 30/11/2021 22:09:05 |
865 forum posts 12 photos | 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.
I have no Excel training - I'm an "intuitive" (but not very) user of an old (year 2000) version.
|
Michael Gilligan | 30/11/2021 22:27:56 |
![]() 23121 forum posts 1360 photos | 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. |
Steviegtr | 30/11/2021 22:36:40 |
![]() 2668 forum posts 352 photos | 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 Cowboy | 30/11/2021 22:56:30 |
1160 forum posts 73 photos | 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 Haine | 30/11/2021 22:57:35 |
5563 forum posts 322 photos | 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 Foster | 30/11/2021 23:56:30 |
3630 forum posts 128 photos | +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 Gilligan | 01/12/2021 00:14:47 |
![]() 23121 forum posts 1360 photos | 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 | 01/12/2021 00:41:14 |
5307 forum posts 83 photos | Clearly Michael is a health food freak |
Martin Connelly | 01/12/2021 01:20:59 |
![]() 2549 forum posts 235 photos | 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 |
Peter Greene | 01/12/2021 01:46:20 |
865 forum posts 12 photos | 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 Haine | 01/12/2021 07:34:23 |
5563 forum posts 322 photos | 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 4 | 01/12/2021 08:14:09 |
257 forum posts 14 photos | 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. |
AndrewD | 01/12/2021 08:45:15 |
19 forum posts 9 photos | This should do the trick: Sub delLine() With Sheets("Sheet1" 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 |
Howi | 01/12/2021 09:08:31 |
![]() 442 forum posts 19 photos | 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 4 | 01/12/2021 10:41:11 |
257 forum posts 14 photos | 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. Shaw | 01/12/2021 10:42:34 |
![]() 1531 forum posts 44 photos | 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 Connelly | 01/12/2021 15:09:14 |
![]() 2549 forum posts 235 photos | 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 |
AndrewD | 01/12/2021 15:27:40 |
19 forum posts 9 photos | Thanks! Too late to edit unfortunately.
Edited By AndrewD on 01/12/2021 15:27:54 |
Peter Greene | 01/12/2021 22:55:56 |
865 forum posts 12 photos | Posted by Peter G. Shaw on 01/12/2021 10:42:34:
Simple version - use a piece of paper!
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 Gilligan | 01/12/2021 23:13:05 |
![]() 23121 forum posts 1360 photos | 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 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 |
Please login to post a reply.
Want the latest issue of Model Engineer or Model Engineers' Workshop? Use our magazine locator links to find your nearest stockist!
Sign up to our newsletter and get a free digital issue.
You can unsubscribe at anytime. View our privacy policy at www.mortons.co.uk/privacy
You can contact us by phone, mail or email about the magazines including becoming a contributor, submitting reader's letters or making queries about articles. You can also get in touch about this website, advertising or other general issues.
Click THIS LINK for full contact details.
For subscription issues please see THIS LINK.