Hi all,
For the following sample data I'm trying to perform following operations:
1.Group the data on basis of col1 ,col 4 and col2 and the second part of OP is updated and incremented accordingly.
2. Group the data on basis of col1 , col3 ,col5 and the first part of OP is updated and incremented accordingly
col1 | col2 | col3 | col4 | col5 | op |
Bottle | A | S | 06-07-2016 | 09-06-2018 | S1||A1 |
Bottle | A | S | 06-07-2016 | 09-06-2019 | S1||A2 |
Bottle | A | S | 08-09-2018 | 18-05-2020 | S2||A1 |
Cup | Q | A | 12-15-2011 | 01-11-2014 | A1||Q1 |
Cup | Q | A | 12-15-2011 | 01-11-2015 | A1||Q2 |
Cup | R | A | 12-9-2015 | 08-07-2011 | A1||R1 |
When I tried it with multi-row formula its treating each row as a new case and incrementing it accordingly, so despite my best efforts, I have been unable to complete the first part of the OP using the sample data provided above.
Could anybody help me resolve the issue ?
Thanks in advance
Solved! Go to Solution.
Actually I'm trying to get the op column as a whole ! I was stuck at on how to update the part 1 of OP that's what I meant .
@QurrathAmeena What is the logic that tells you where the first part of OP should be incremented? You say it is based on grouping on columns 1,3 and 5, but as column 5 has only unique values this will not aggregate the data.
The first part of OP seems to increment between rows 2 and 3, why is this? There is no set of values unique to just these two rows.
This just a sample set of my original data . In the actual data there are instances where col 5 has duplicate values and in those cases it has to be treated separately.
Let me explain in detail what I'm trying to do and achieve with this data:
1. For the second part of OP -- firstly , if they belong to same category in Col1
-- secondly , if the belong to same type in Col 2
-- lastly , if they belong to same date in Col 4
Ex: In case of Bottle category we see there are 3 different values belonging to same category but the date(col 4) for row 3 is different and hence the counter restarts in row 3
For the first part of OP -- firstly , if they belong to same category in Col1
--secondly , if they belong to same type in Col 3
-- lastly,if they are have same date in col 5
Input :
col1 | col2 | col3 | col4 | col5 |
Bottle | A | S | 06-07-2016 | 09-06-2018 |
Bottle | A | S | 06-07-2016 | 09-06-2019 |
Bottle | A | S | 08-09-2018 | 18-05-2020 |
Cup | Q | A | 12-15-2011 | 01-11-2014 |
Cup | Q | A | 12-15-2011 | 01-11-2015 |
Cup | R | A | 12-9-2015 | 08-07-2011 |
Output:
col1 | col2 | col3 | col4 | col5 | op |
Bottle | A | S | 06-07-2016 | 09-06-2018 | S1||A1 |
Bottle | A | S | 06-07-2016 | 09-06-2019 | S1||A2 |
Bottle | A | S | 08-09-2018 | 18-05-2020 | S2||A1 |
Cup | Q | A | 12-15-2011 | 01-11-2014 | A1||Q1 |
Cup | Q | A | 12-15-2011 | 01-11-2015 | A1||Q2 |
Cup | R | A | 12-9-2015 | 08-07-2011 | A2||R1 |
Column 5 is different between rows 2 and 3, so would this not restart the counter for the first part of OP?
The first part of OP is grouped based on col4 and second part of OP is grouped based on col 5
But column 4 is also different between rows 2 and 3? Just a bit confused why the first part of OP increments between these rows
For the first 2 rows col 1-4 has same values right ? that's why the first part of OP is same and the second part is incremented