Background:
Attached excel book shows price Data (Base Excel workbook in 97-2003 format, but using latest Office 2013)
Cloumn A - Date
Column B - Open
Column C - High
Column D - Low
Column E - Close
Column F - sometihng (in current book, empty column)
From Data range (180 rows, practically it could be any number of rows, last used row range)
Manually, I've sequentially highlighted Values with date as
Lowest Low (Column D),
Lowest Close (Column E),
Highest High (Column C),
Highest Close (Cloumn E)
Adjacement to data, I've marked Peak/Valley on Chart with Arrows to show Highest High, Highest Close and Lowest Low / Lowest Close.
(In real life work, i dont want chart, which is just here for visual presentation, which value to peak).
Requirement:
I need a VBA Macro, that can tabulate the data either under (given in G35:Q47) from any ACTIVE WORKSHEET (As I would be having multiple sheets)
Scenario 1 - Sequential High, Low with date, Highest High (from Column C), and Lowest Low (from Column D)
Scenario 2 - Sequential High, Low with date, Highest Close (from Column E), and Lowest Close (from Column E)
Both of above scenario date/values, I've done manually to explain, what value exactly i want.
Good if I can get data under both the scenarios with flexibility, which requires just tweaking of column reference number !!!
After summary table is populated, I dont want to retain original raw data which should be deleted/cleared to reduce file size, and Scenario 1, and/or Scenario 2 Tabulation should only appear as final output.
Sheet Named Output- is just for illustration, how final output sould look like in main data sheet(DATA), erasing all input data, in format Range Column A:C using either of the scenarios.
Sample Workbook attached for reference, what i describe above.
Thanks
Bookmarks