Combining N/A row data fields with data from another column with the same ID?

Hi all!

New user here with a few questions.

I'm wondering if it's possible to combine 2 or more rows with columns that have N/A values in some cells and replace those with N/A values from other cells in the same column but different row that have the same identifier (Email)

After that, count how many values are not null.

The headers after "Attendance Duration" will change in every report since these are questions to test knowledge after a training so I can't rely on looking for specific text in the headers.

I tried using Multi-Field Formula to change N/A to 0 but that didn't work.

Then Transpose to do...whatever Transpose does for this (I honestly don't know why that's there but other topics mentioned adding it in similar situations so I thought I'd try it out).

Thanks in advance!

Combine Rows.yxmd

Workflow

Text Mining

Accepted answers

IraWatt

Hey @jwlam

yes that's all possible, I ticked the dynamic columns on the join and added a select tool and that works to alow a dynamic number of columns all the way to the end of the workflow.

let me know if any issues

Additional Columns 2.yxmd

All comments

IraWatt

Hey @jwlam,

Not sure how to calculate total non N/A but this workflow does the rest:

please ask if any questions

combine details.yxmd

jwlam

Thanks! This looks great.

A few things I'm hoping to follow up with you on.

If I were to delete the N/A from the columns and have it be read as Null then could we use Summarize to count Non Null at the end of the workflow? I feel like that might be the best solution?

The Duration didn't seem to Sum all rows. Is it possible? I'm hoping we can get it to say 60 in that field.

It looks like in the Join you replaced LastName with Email. Could we switch that up and replace FirstName and leave LastName?

Thank you so much for your help!!

IraWatt

Hey @jwlam,

No worries I've updated it to include last name and sum duration. I've also added a summarize at the bottom to count the non null values for each email.

combine details (1).yxmd

jwlam

Last question (I hope).

I grabbed a workflow from someone else's question and put it into mine. It works to count non empty columns. But when I add an additional column to the input data it only counts the original 4. It seems like the 5th column isn't making it into the first Join?

What do I need to do to get it to read different numbers of columns (dynamically if possible)? The reports I get can range anywhere from 4-30 columns. Of course, I can export to Excel and make a formula there to count if that isn't possible here.

Thanks.

Additional Columns.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703