Vbalearner85
Board Regular
- Joined
- Jun 9, 2019
- Messages
- 139
- Office Version
- 2016
- Platform
- Windows
Hi,
I am trying to automate large amounts of data analysis. Data structure and expected data analysis snapshot is attached.
I believe macros can do it, but not quite competent to achieve this..any help will be greatly appreciated!!!
Assumptions
Data starts from Row 2- Headers in row 1
Column A - Stock name - will be similar to data worksheet name "ACC" and is same in entire column until data range
Each stock has its own Data worksheet with similar structure and in total around 200 data worksheets
Column B - is Date and time stamp of data record time
Column H - Range (will have values only as HR,NR or LR and will be consecutive until switching to any of other 2 options)
Column J- is Move - can be positive , Negative or 0 numeric
Column K- is Timer - timer for measuring time of consecutive move in hh.mm.ss format for which any of column H option repeat consistently. Can be consecutive secs or might be missing some secs
Data can run into thousands of lines for each worksheet of this data.
There are some misc./other data groupings in between these columns, not relevant for this analysis
Expected Data Analysis (on Right hand side of same data starting from row 1
Stock Name- same as Column A
Time from- Time from column B- corresponding to which range LR-NR-HR starts-have 1st occurrence
Range-Range(LR/NR/HR) for above step from column H
L-Move - Lowest Range for each of the instances of LR or NR or HR
H-Move - Highest Range for each of the instances of LR or NR or HR
TimerEndRange-Timer(column K) value where each instance ends (LR or NR or HR ) has last occurrence
Data Analysis can be created on each stock worksheet on the right (suppose starting at column Z-row 1.
Regards,
PK
I am trying to automate large amounts of data analysis. Data structure and expected data analysis snapshot is attached.
I believe macros can do it, but not quite competent to achieve this..any help will be greatly appreciated!!!
Assumptions
Data starts from Row 2- Headers in row 1
Column A - Stock name - will be similar to data worksheet name "ACC" and is same in entire column until data range
Each stock has its own Data worksheet with similar structure and in total around 200 data worksheets
Column B - is Date and time stamp of data record time
Column H - Range (will have values only as HR,NR or LR and will be consecutive until switching to any of other 2 options)
Column J- is Move - can be positive , Negative or 0 numeric
Column K- is Timer - timer for measuring time of consecutive move in hh.mm.ss format for which any of column H option repeat consistently. Can be consecutive secs or might be missing some secs
Data can run into thousands of lines for each worksheet of this data.
There are some misc./other data groupings in between these columns, not relevant for this analysis
Expected Data Analysis (on Right hand side of same data starting from row 1
Stock Name- same as Column A
Time from- Time from column B- corresponding to which range LR-NR-HR starts-have 1st occurrence
Range-Range(LR/NR/HR) for above step from column H
L-Move - Lowest Range for each of the instances of LR or NR or HR
H-Move - Highest Range for each of the instances of LR or NR or HR
TimerEndRange-Timer(column K) value where each instance ends (LR or NR or HR ) has last occurrence
Data Analysis can be created on each stock worksheet on the right (suppose starting at column Z-row 1.
Regards,
PK
