I want to achieve the following as shown in screen shots. Is there any way I can use any trick in excel to achieve this very easily? As my data set is very large
Input:
Output:
CodePudding user response：
Summary: Use the Power Query Editor feature. Select desired data in a range: right click and select get data from table/range... follow on screen instructions
More specific steps :
1.0) Select data; then right click select get data from table/range
2.0) From Power Query editor popup...
2.1) Select range to unpivot data.. (Shift and select 1st & last columns with dates)
2.2) goto transform unpivot columns
2.2.1) now you should have 3 columns
2.3 select the value column with dates
2.3.1 transform--> Pivot Column
2.3.2 from the popup have attribute as your values column
2.3.2.1 select advanced and select either min or max (it doesn't matter which)
2.4 select close and load.
If needed, record the above as a macro for repeated execution; but the steps are pretty straight forward once you learn them.
CodePudding user response：
This is a Excel 365-formula solution for your question:
I added a table tblData to the sheet - data are not 100 % equal to yours ... benefit of using a table: columns are addressed by their names not "abstract" A,B,C
Formulas in A13, B12 and B13 are spill formulas - so the size of new table depends on the data of the data source.
A13: =tblData[Tool] spills down with all tools
B12: =LET(minDate,MIN(tblData[[ROM]:[EOP]]),maxDate,MAX(tblData[[ROM]:[EOP]]), SEQUENCE(1,maxDate-minDate 1,minDate)) spills to the right according to the date range from ROM and EOP column
B13: =LET(rgROM,FILTER(tblData,(tblData[#Headers]="ROM")), rgEOP,FILTER(tblData,(tblData[#Headers]="EOP")), rowIndex,SEQUENCE(ROWS(rgROM)), IF(INDEX(rgEOP,rowIndex)=B12#,"EOP", IF(INDEX(rgROM,rowIndex)=B12#,"ROM",""))) checks if the date from row 12 equals to either the date in EOP or ROM - if yes the according value is written.
If you add rows or change the dates within ROM and EOP the result table will handle this.
Except for formatting the dates - this has to be done manually - esp. if the date range increases.
Format condition is not spilling - but has to be applied manually. Maybe it's possible to enhance this too - but I haven't tried it.