Hi,
I have below dataset. Column C3_flag is mark which I have to use and generate 2 columns as shown in output.
I have to create numbering as per the ascending order of date for each item in C1 considering C3_flag column. I want column C4 and Column C5 as shown in expected output.
Available dataset:
C1 | C2_date | C3_flag |
a | 4/6/2019 | |
a | 9/10/2020 | |
a | 22/11/2020 | 1 |
a | 20/1/2021 | |
a | 28/2/2021 | |
a | 4/8/2021 | |
b | 18/9/2018 | |
b | 22/1/2019 | 1 |
b | 26/2/2019 | |
b | 20/6/2019 | |
c | 20/9/2017 | |
c | 20/5/2018 | |
c | 26/7/2018 | |
c | 24/9/2018 | 1 |
c | 23/11/2018 | |
c | 11/1/2019 | |
c | 18/3/2019 | |
d | 20/4/2018 | |
d | 21/7/2018 | 1 |
d | 23/4/2019 |
Expected Output :
C1 | C2_date | C3_flag | C4 | C5 |
a | 4/6/2019 | -2 | ||
a | 9/10/2020 | -1 | ||
a | 22/11/2020 | 1 | 1 | 1 |
a | 20/1/2021 | 2 | 2 | |
a | 28/2/2021 | 3 | 3 | |
a | 4/8/2021 | 4 | 4 | |
b | 18/9/2018 | -1 | ||
b | 22/1/2019 | 1 | 1 | 1 |
b | 26/2/2019 | 2 | 2 | |
b | 20/6/2019 | 3 | 3 | |
c | 20/9/2017 | -3 | ||
c | 20/5/2018 | -2 | ||
c | 26/7/2018 | -1 | ||
c | 24/9/2018 | 1 | 1 | 1 |
c | 23/11/2018 | 2 | 2 | |
c | 11/1/2019 | 3 | 3 | |
c | 18/3/2019 | 4 | 4 | |
d | 20/4/2018 | -1 | ||
d | 21/7/2018 | 1 | 1 | 1 |
d | 23/4/2019 | 2 | 2 |
Solved! Go to Solution.
@PB41091
This is an interesting one for me and its like reverse order.
Hi,@PB41091
please see other way to without Multi-Row Formula :
C1 | C2_date | C3_flag | C1 | C2_date | C3_flag | C4 | C5 | |
a | 4/6/2019 | a | 4/6/2019 | -2 | ||||
a | 9/10/2020 | a | 9/10/2020 | -1 | ||||
a | 22/11/2020 | 1 | a | 22/11/2020 | 1 | 1 | 1 | |
a | 20/1/2021 | a | 20/1/2021 | 2 | 2 | |||
a | 28/2/2021 | a | 28/2/2021 | 3 | 3 | |||
a | 4/8/2021 | a | 4/8/2021 | 4 | 4 | |||
b | 18/9/2018 | b | 18/9/2018 | -1 | ||||
b | 22/1/2019 | 1 | b | 22/1/2019 | 1 | 1 | 1 | |
b | 26/2/2019 | b | 26/2/2019 | 2 | 2 | |||
b | 20/6/2019 | b | 20/6/2019 | 3 | 3 | |||
c | 20/9/2017 | c | 20/9/2017 | -3 | ||||
c | 20/5/2018 | c | 20/5/2018 | -2 | ||||
c | 26/7/2018 | c | 26/7/2018 | -1 | ||||
c | 24/9/2018 | 1 | c | 24/9/2018 | 1 | 1 | 1 | |
c | 23/11/2018 | c | 23/11/2018 | 2 | 2 | |||
c | 11/1/2019 | c | 11/1/2019 | 3 | 3 | |||
c | 18/3/2019 | c | 18/3/2019 | 4 | 4 | |||
d | 20/4/2018 | d | 20/4/2018 | -1 | ||||
d | 21/7/2018 | 1 | d | 21/7/2018 | 1 | 1 | 1 | |
d | 23/4/2019 | d | 23/4/2019 | 2 | 2 |