Multi row formula increment by grouping

[QurrathAmeena]

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 

[FinnCharlton]

One method is to use the tile tool. This works well for generating the second part of the "op". However I'm unsure how you are planning to generate the first part of the "op", as all of your values in column 5 are unique. Grouping by this column won't aggregate the data.

[QurrathAmeena]

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 .

[FinnCharlton]

@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.

[QurrathAmeena]

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

[FinnCharlton]

Column 5 is different between rows 2 and 3, so would this not restart the counter for the first part of OP?

[QurrathAmeena]

The first part of OP is grouped based on col4 and second part of OP is grouped based on col 5

[FinnCharlton]

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

[QurrathAmeena]

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

[DataNath · Accepted solution]

Hey @QurrathAmeena, is this what you're after?