How to merging 7 rows into 1

[soncoku]

Hey community.

Hope you'll doing well.

I'm trying to merge 7 rows into 1. 
As you can see in the picture below...

I have 7 payment statuses.
I need to have one column that says how many payments are in a row and display the number.

If in a row there are for example: Paid, Paid, Need to pay (the now column should say: Need to Pay 3) as the need to pay status was on the 3-rd column

Any ideas on how to?

[atcodedog05]

Hi @soncoku ,

Can you help us out by providing a sample data file.

We can prep up a workflow for this

[atcodedog05 · Accepted solution]

Hi @soncoku ,

Here is a workflow for the task. 

Input

Output

Workflow

Hope this helps : )

If this helps please mark it as solution.

[Maskell_Rascal]

Hi @soncoku

This should work for you. I'm using the transpose and some formula tools to be able to group your data and identify the column with "Need to Pay". I then just flip the new column back with crosstabs and join it to our existing data. 

Input:

Workflow/Output:

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[soncoku]

@atcodedog05  This would work perfectly but I solved it yesterday by grouping all 7 fields into one and then counting the words with regex match.
Thanks anyway, will mark this as the solution as it does exactly what I was looking for.
Cheers 🙂

[atcodedog05]

Thank you for accepting my solution @soncoku 

Happy to help : ) 

Cheers and happy Analysing : )