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

[jwlam]

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!

[IraWatt]

Hey @jwlam,

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

please ask if any questions :)

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

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

[IraWatt · Accepted solution]

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

[jwlam]

Thank you so much!!!

[jwlam]

Last last question.

I put the same headers in an excel file and used Input Data rather than Text Input. I noticed the results were coming out wrong.

When I run the entire report the user comes out with 28 of 28 answered.

I pulled out a single user and ran him through and he came out with the correct result: 25 of 28 answered.

The weird thing is I replaced the names in the report so I could share the actual raw data with you but the results come back correct again.

Do you have any ideas on why I'm getting different results based on the number of rows and when the FirstName, LastName, and/or Email columns are changed?

[IraWatt]

I think the only think to do is compare row ID one when 28 are answered and see why the blanks are not being counted by the summarize:

[jwlam]

The first Transpose has him with Null in those three questions he didn't answer.

Here's the output from the last Transpose. He shows up with all the answers even though the raw data doesn't have him answering 3 of the questions.

Looking at the Cross Tab that's seemingly where his answers are getting filled in even in columns that he didn't answer.