Multi Row Question

[Emil_Kos]

Hi everyone,

Usually I am trying to answer questions of people but I am stuck and I am not sure how to move forward. 

I have a table with 20m+ rows and I am looking to identify if each line is in the chain. In order to do so at least one recipient that is the same for the previous row(comma is as a separator).

This is the data set: 

request_id	Server ID	recipient
11111111111	Server 1	RecipientA,RecipientB
11111111112	Server 1	RecipientB,RecepientC
11111111113	Server 1	RecepientA,RecepientB
11111111114	Server 1	RecepientB
11111111115	Server 1	RecepientC

This is desired output:

request_id	Server ID	recipient	Output/Chain
11111111111	Server 1	RecipientA,RecipientB	Server 1
11111111112	Server 1	RecipientB,RecepientC	Server 1
11111111113	Server 1	RecepientA,RecepientB	Server 1
11111111114	Server 1	RecepientB	Server 1
11111111115	Server 1	RecepientC

I wanted to use multi row formula:

IF [Server ID]=[Row-1:Server ID] AND
[recipient]=[Row-1:recipient]
Then [Server ID] Else '' ENDIF

But it will not work in my examples. 

I wanted to use text to columns but because recipient is not always in the same order or I can have couple different recipients for each line I don't think it will be a best solution.

Please advise me with some suggestions. I am sure the answer is quite easy but I am missing something obvious. 

[atcodedog05]

Hi @Qiu & @Emil_Kos 

I built a lengthy workflow considering there might be a scenarios with more then 2 recipients😅 

[Emil_Kos]

Hi @atcodedog05,

Actually there is a scenario like this. 

I think there might be 3 recipients at most at the same time.

I need also to think about the efficiency because the amount of rows that I got is quite big and the data set have couple more columns that I need to work with. 

[atcodedog05 · Accepted solution]

Hi @Emil_Kos 

If you know what is the at most recipients. I think @Qiu 's method is much preferable 😅

[Qiu · Accepted solution]

@Emil_Kos 
Thank you for the feedback.

With our little comments, I believe you are already good to go and good luck with you workflow.

[Emil_Kos]

Hi @atcodedog05 @PhilipMannering @Qiu,

Thank you for your help and it took me while but actually I found really simple way to solve my problem.

After using text to columns and splinting the data to rows I needed to sort it in a right order.

Afterwards using multi row formula allowed me to get the proper result. 

Thank you for your help!

[Qiu]

@Emil_Kos 

Good to know you get all sorted out.

[atcodedog05]

Glad to know you sorted it out @Emil_Kos 🙂