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.

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.

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.

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

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.

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

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

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

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