Log In
Register
Home
Active Member
September 5, 2021
Offline
Hello. Hope you all are ok.
I am trying to transform my data but not able to progress. Here is the problem.
Have a table with the following columns
Can you please help me?
Thanks
| DESCRIPTION | DATE | SALARY |
| FINISH 1 | 10/30/2017 | 100 |
| START 1 | 10/18/2016 | 100 |
| FINISH 2 | 6/11/2003 | 1091.25 |
| MODIFICATION 1 | 1/1/2003 | 1091.25 |
| MODIFICATION 2 | 12/1/2002 | 1091.25 |
| MODIFICATION 3 | 11/1/2002 | 1634.14 |
| MODIFICATION 4 | 10/1/2002 | 1636.09 |
| MODIFICATION 5 | 9/1/2002 | 1634.14 |
| MODIFICATION 6 | 6/1/2002 | 1634.49 |
| MODIFICATION 7 | 5/1/2002 | 1728.61 |
| MODIFICATION 8 | 4/1/2002 | 1367.06 |
| MODIFICATION 9 | 3/1/2002 | 1361.9 |
| MODIFICATION 10 | 2/1/2002 | 1366.79 |
| MODIFICATION 11 | 1/1/2002 | 1053.75 |
| MODIFICATION 12 | 6/16/2001 | 1008.75 |
| MODIFICATION 13 | 1/1/2001 | 1008.75 |
| MODIFICATION 14 | 8/1/2000 | 947 |
| MODIFICATION 15 | 1/1/2000 | 947.5 |
| MODIFICATION 16 | 12/3/1998 | 861.25 |
| MODIFICATION 17 | 1/1/1998 | 755 |
| MODIFICATION 18 | 4/1/1997 | 661.25 |
| MODIFICATION 19 | 4/1/1996 | 557.41 |
| MODIFICATION 20 | 12/4/1995 | 484.88 |
| MODIFICATION 21 | 6/1/1995 | 457.5 |
| MODIFICATION 22 | 4/1/1995 | 448.98 |
| MODIFICATION 23 | 1/1/1995 | 408.5 |
| MODIFICATION 24 | 1/1/1994 | 381.75 |
| MODIFICATION 25 | 7/21/1993 | 256.86 |
| MODIFICATION 26 | 1/1/1993 | 142.7 |
| START 3 | 9/28/1992 | 133.3 |
| FINISH 4 | 11/11/1991 | 119 |
| MODIFICATION 27 | 11/16/1990 | 119 |
| FINISH 5 | 10/19/1990 | 100.8 |
and I need the following:
| DESCRIPTION | START DATE | FINISH DATE | SALARY |
| FINISH 1 | 10/18/2016 | 10/30/2017 | 100 |
| MODIFICATION 1 | 1/1/2003 | 6/11/2003 | 1091.3 |
| MODIFICATION 2 | 12/1/2002 | 1/1/2003 | 1091.3 |
| MODIFICATION 3 | 11/1/2002 | 12/1/2002 | 1634.1 |
| MODIFICATION 4 | 10/1/2002 | 11/1/2002 | 1636.1 |
| MODIFICATION 5 | 9/1/2002 | 10/1/2002 | 1634.1 |
| MODIFICATION 6 | 6/1/2002 | 9/1/2002 | 1634.5 |
| MODIFICATION 7 | 5/1/2002 | 6/1/2002 | 1728.6 |
| MODIFICATION 8 | 4/1/2002 | 5/1/2002 | 1367.1 |
| MODIFICATION 9 | 3/1/2002 | 4/1/2002 | 1361.9 |
| MODIFICATION 10 | 2/1/2002 | 3/1/2002 | 1366.8 |
| MODIFICATION 11 | 1/1/2002 | 2/1/2002 | 1053.8 |
| MODIFICATION 12 | 6/16/2001 | 1/1/2002 | 1008.8 |
| MODIFICATION 13 | 1/1/2001 | 6/16/2001 | 1008.8 |
| MODIFICATION 14 | 8/1/2000 | 1/1/2001 | 947 |
| MODIFICATION 15 | 1/1/2000 | 8/1/2000 | 947.5 |
| MODIFICATION 16 | 12/3/1998 | 1/1/2000 | 861.25 |
| MODIFICATION 17 | 1/1/1998 | 12/3/1998 | 755 |
| MODIFICATION 18 | 4/1/1997 | 1/1/1998 | 661.25 |
| MODIFICATION 19 | 4/1/1996 | 4/1/1997 | 557.41 |
| MODIFICATION 20 | 12/4/1995 | 4/1/1996 | 484.88 |
| MODIFICATION 21 | 6/1/1995 | 12/4/1995 | 457.5 |
| MODIFICATION 22 | 4/1/1995 | 6/1/1995 | 448.98 |
| MODIFICATION 23 | 1/1/1995 | 4/1/1995 | 408.5 |
| MODIFICATION 24 | 1/1/1994 | 1/1/1995 | 381.75 |
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Victor,
You have:
MODIFICATION 1- Start Date: 1/1/2003 , Salary: 1091.25
In your desired output, from where is Finish Date coming from, not seeing it in the initial data?
| DESCRIPTION | START DATE | FINISH DATE | SALARY |
| MODIFICATION 1 | 1/1/2003 | 6/11/2003 | 1091.3 |
Active Member
September 5, 2021
Offline
Hi Catalin, the start date is formed by the previous date:
Example:
from below table,
Start 1 = 10/18/2016
Modification 1 = Start date 1/1/2003 and finish date 6/11/2003
Modification 2 = start date 12/1/2002 and finish date 1/1/2003
Hope this helps
Thanks in advance
| DESCRIPTION | DATE | SALARY |
| FINISH 1 | 10/30/2017 | 100 |
| START 1 | 10/18/2016 | 100 |
| FINISH 2 | 6/11/2003 | 1091.25 |
| MODIFICATION 1 | 1/1/2003 | 1091.25 |
| MODIFICATION 2 | 12/1/2002 | 1091.25 |
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Ok, so Modification 1 has the finish date of FINISH 2 ?! What's the order of the operations, as it's not as obvious as you may think. The date column looks like it is a start date or an end date, depending on description, correct?
But, why do you have in your sample data (first post) START 1 and 3, FINISH 1, 2, 4 and 5 and MODIFICATION 1 to 27. Why there are gaps and which ones are related and must be analyzed as a group?
Please provide the entire logical chain, we can't imagine our own process, guessing always leads to wasted time.
Thanks for understanding, we'll try to help you.
1 Guest(s)
