Is there a way to replace all X's in a row with what is listed in a specific column

[rmartori]

I will provide a simplified example below but there are two things I need to do I am trying to figure out.

	D	E	F	G	H	I	J
			John Smith	Bob Robertson	Joni Johanson	Sirius Sam	Susan Anthonu
2	Answers
3	A - Yes		X				X
4	B - No				X
5	C - Maybe			X
6	No Answer					X

So Basically the two things I need to accomplish is first replace all of the X's in each row with the corresponding answer in Column D.

Second I need to basically Transpose and collapse everything from F onwards so I am left with

Names	Answers
John Smith	A - Yes
Bob Robertson	C - Maybe
Joni Johanson	B - No
Sirius Sam	No Answer
Susan Anthonu	A - Yes

[bpatel]

hi @rmartori ,

i mocked up a workflow that hopefully helps!

[MichaelLaRose · Accepted solution]

Hi @rmartori,

See the attached workflow.

I used the Multi-field formula to do the reassignment and then transposed and filtered the data to your desired result. 

[Jonathan-Sherman]

Hi @rmartori,

I took a slightly different approach to @bpatel with just one transpose doing the heavy lifting.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[rmartori]

Thank you MichaelLaRose,

The reassign one worked great.

Followup question, Would it be possible to do this with multiple sheets at once?

So one excel file and have like Sheet1 sheet2 sheet3 etc? and the output be effectively the same and keep doing that till it runs out of sheets?

[MichaelLaRose]

I don't see why not.

If you use a dynamic input tool to pull all of the records from each sheet so you should be able to.

If the sheets have a different field structure you may need to batch the macro and be a little creative with the output to make sure it doesn't throw an error.

Would you mark an accepted solution on this post? You can mark multiple if others were also things that would work for you.

Best,

Michael