Conditional Population / fill-In and Transposing Data in table

[ibesmond]

Hello

I have a data set that has some problems and needs cleanup, but I'm not sure how to accomplish this feat.

Basically I want to flatten the data as much as possible without loosing any data.

The data came from different sources so each row represents a row, and the column is where the data is located.

If This was a small set I would just go manually, but this is only a small subset of the data.

The sources are in no particular order.  

The first data set is problem.

The second data set is solution 1.  If I could get the data to this position I would be happy, but

The third tab is solution 2, if I could get it to this stage that would be the ultimate goal.

I appreciate the advise.  Thank you

[BrandonB · Accepted solution]

I think that this should help 🙂

Workflow is attached

 

[BrandonB · Accepted solution]

The trick is to transpose all of the columns to rows, filter out all of the null values, and then crosstab it back. 

[ibesmond]

Thank you so much @BrandonB   I know I needed the cross tab and transpose tool, I just could figure out the configuration.