VBA to arrange Dynamic data series

[smide]

Hello.

I'm receiving data from external source (on every 5-10) minutes for analysis and I'm interested only for last five samples.
Data range is a quite extensive on two Sheets (Sheet1 , row 3 columns A-EN and same for Sheet2).
I would like to avoid all those annoying and time consuming copy-paste with some simple macro.

Here is my current situation:

- my last five samples/prices are always in rows 4-8 (every second column - B,D,F,H...EN)
- left columns (A,C,E..) are date and time columns - time when price was recived

What I would like to do is:

In the case that data series contains all five prices and new obtained price differs from current last series price then:

1) when new price(s) is obtained I would like to eliminate (delete) first price (oldest) from those series, then move all other prices one place downwards and finally put that new obtaind price
to the last position in series (row 8).


In the case that data series contains less than five prices and new obtained price differs from current last series price then:

2) just add that new obtaind price in first empty cell (rows 4-8)

If new obtained price is equal to last data series price then - do nothing!

Example.

- product names: row3
- data series/prices for product: always in the same column as product (in rows 4-8)
- date an time: always in "left" column
- new obtained price: row 3 to the right of product cell

Sheet1
- Product 1 - all five samples are in data series - if new obtaind price differs from last price (69) then do 1)
- Product 2 - three samples in series (less than five) - if new obtaind price differs from last price just add new obtained in first empty cell
- Product 3 - new obtained price is equal to last series price (19) - do nothing!
cells C3,E3,G3 - new obtained prices for Product's

	A	B	C	D	E	F	G
1
2
3		Product 1	75	Product 2	48	Product 3	19
4	12.09. 23:13	25	11.09. 22:10	49	12.09. 11:02	22
5	12.09. 23:19	36	16.09. 10:55	35	13.09. 15:12	19
6	14.09. 12:28	18	17.09. 10:30	37
7	16.09. 15:26	16
8	16.09. 15:28	69

<tbody>
</tbody>

Sheet 1 (after macro run)

	A	B	C	D	E	F	G
1
2
3		Product 1	75	Product 2	48	Product 3	19
4	12.09. 23:19	36	11.09. 22:10	49	12.09. 11:02	22
5	14.09. 12:28	18	16.09. 10:55	35	13.09. 15:12	19
6	16.09. 15:26	16	17.09. 10:30	37
7	16.09. 15:28	69		48
8		75

<tbody>
</tbody>

* note : below row 8 (rows 12-75, columns A-EN) I have cells with formulas and I would like to keep them after macro runs