VBA to arrange Dynamic data series

smide

Board Regular
Joined
Dec 20, 2015
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product 1[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]Product 2[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]Product 3[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12.09. 23:13[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]11.09. 22:10[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]12.09. 11:02[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12.09. 23:19[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]16.09. 10:55[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]13.09. 15:12[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]14.09. 12:28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]17.09. 10:30[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]16.09. 15:26[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]16.09. 15:28[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Sheet 1 (after macro run)

[TABLE="class: grid, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Product 1[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]Product 2[/TD]
[TD="align: center"]48[/TD]
[TD="align: center"]Product 3[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12.09. 23:19[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]11.09. 22:10[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"] 12.09. 11:02[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]14.09. 12:28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]16.09. 10:55[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]13.09. 15:12[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]16.09. 15:26[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]17.09. 10:30[/TD]
[TD="align: center"]37[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"] 16.09. 15:28[/TD]
[TD="align: center"]69[/TD]
[TD="align: center"][/TD]
[TD="align: center"]48[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]75[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

* 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
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top