Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing rows that don't show the lowest stock each day
Hi
I have a spreadsheet that has a date column A and a stock level column Z. Due to stock coming in and going out at different times of the same day there are often a number of dates the same in col A (time is recorded separately in col B) and varying values in col Z. I need to produce a graph of the lowest stock level from every day on which the stock level changes. Thus:- A Z 1 1/1/5 100 2 1/1/5 150 3 1/1/5 99 4 1/1/5 120 5 2/1/5 130 6 2/1/5 120 7 6/1/5 100 8 6/1/5 150 9 6/1/5 175 etc Therefore I want to be able to hide/delete (somehow?) all but row 3, 6 and 7 in this example so I can use the remaining rows date and stock levels for the graph. Thanks, any help would be greatly appreciated. Guy. |
#2
|
|||
|
|||
Hi Guy,
I would have thought that Rows 3, 1 and 7 would be the lowest? Have you tried Autofilter, that could give you the lowest 10, and that should be easy to work from? Otherwise, you need to look at a macro "Guy" wrote: Hi I have a spreadsheet that has a date column A and a stock level column Z. Due to stock coming in and going out at different times of the same day there are often a number of dates the same in col A (time is recorded separately in col B) and varying values in col Z. I need to produce a graph of the lowest stock level from every day on which the stock level changes. Thus:- A Z 1 1/1/5 100 2 1/1/5 150 3 1/1/5 99 4 1/1/5 120 5 2/1/5 130 6 2/1/5 120 7 6/1/5 100 8 6/1/5 150 9 6/1/5 175 etc Therefore I want to be able to hide/delete (somehow?) all but row 3, 6 and 7 in this example so I can use the remaining rows date and stock levels for the graph. Thanks, any help would be greatly appreciated. Guy. |
#3
|
|||
|
|||
Thanks Kassie, rows 1, 3 and 7 do have the lowest 3 numbers but they are not
the lowest values for each of the 3 different dates given. The autofilter of the 10 lowest numbers isn't appropriate for the same reason plus I have over 1000 rows of data which equates to at least 150 different dates, and thus at least 150 different lowest stock levels. How would a macro work? Thanks. Guy "Kassie" wrote: Hi Guy, I would have thought that Rows 3, 1 and 7 would be the lowest? Have you tried Autofilter, that could give you the lowest 10, and that should be easy to work from? Otherwise, you need to look at a macro "Guy" wrote: Hi I have a spreadsheet that has a date column A and a stock level column Z. Due to stock coming in and going out at different times of the same day there are often a number of dates the same in col A (time is recorded separately in col B) and varying values in col Z. I need to produce a graph of the lowest stock level from every day on which the stock level changes. Thus:- A Z 1 1/1/5 100 2 1/1/5 150 3 1/1/5 99 4 1/1/5 120 5 2/1/5 130 6 2/1/5 120 7 6/1/5 100 8 6/1/5 150 9 6/1/5 175 etc Therefore I want to be able to hide/delete (somehow?) all but row 3, 6 and 7 in this example so I can use the remaining rows date and stock levels for the graph. Thanks, any help would be greatly appreciated. Guy. |
#4
|
|||
|
|||
I've managed to work this one out myself by using the 'subtotal' function -
it inserts a row with the minimum value based on the date enabling me to use that as the basis of the graph. "Guy" wrote: Hi I have a spreadsheet that has a date column A and a stock level column Z. Due to stock coming in and going out at different times of the same day there are often a number of dates the same in col A (time is recorded separately in col B) and varying values in col Z. I need to produce a graph of the lowest stock level from every day on which the stock level changes. Thus:- A Z 1 1/1/5 100 2 1/1/5 150 3 1/1/5 99 4 1/1/5 120 5 2/1/5 130 6 2/1/5 120 7 6/1/5 100 8 6/1/5 150 9 6/1/5 175 etc Therefore I want to be able to hide/delete (somehow?) all but row 3, 6 and 7 in this example so I can use the remaining rows date and stock levels for the graph. Thanks, any help would be greatly appreciated. Guy. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find lowest and highest value from a set of rows | Excel Discussion (Misc queries) | |||
Show Rows Button | Excel Discussion (Misc queries) | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Why can't I show or hide rows in an outline on a protected sheet? | Excel Discussion (Misc queries) |