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.

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.

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.

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.