Combine Multiple Rows into Single Row

Hello,

I have a large dataset with multiple rows associated to each candidate. See example data screenshot below:

I want to be able to have all information for each candidate in a single row. So instead of 2 rows for Yasmin, there would be 1 row showing the change in her From Step and To Step. Ideally, with the first line's information of listed first (left to right) to final step. An example output would look as follows (row 1):

As indicated in the first image, Rex Brady has 6 rows, thus I would expect to see a From Step 6, To Step 6, and Step Change Date/Time 6 all within a single row.

How can I achieve this? Thank you!

Workflow

Accepted answers

All comments

apathetichell

post some data if you need me to do a mockup but basically you'll want to transpose using candidate as key column. then tile tool (candidate and name as unique values) - then you'll want to change the name for those areas where the tile sequence number is 2 (or 3) to something like [name] + " 2"... then crosstab with key column, name and value...

t0mato

Thanks! Please see sample data here. I'd appreciate a mockup here.

Data.csv

t0mato

Thanks! Please see sample data here. I'd appreciate the mockup!!

Data.csv

apathetichell

Problem: on an application which has 78 different rows devoted to the same person/application - how many successor columns do you want?

If you just want the second action on an application, I can shoot you over to it - but 78 creates like 500 columns.

t0mato

Hmm that's really odd and i very much appreciate your help here... could you let me know which individual has 78 rows so I can take a look? Ultimately, the goal is to capture the changes in From Step and To Step for each candidate in a single row so that we can calculate the average amount of time the candidate spent in each step (from the Step Change Date/Time field) to get an overall average time in each step.

apathetichell

Goyao is 147 records in your original .csv - I have grouped by application date and one of those showed up as 78 records (so potentially 78 * your number of original columns) columns.

apathetichell

If I may make a suggestion - choose an arbitrary number (say 5) and then filter out those with 5 or less record lines. transpose them (so 35 or so columns) - keep those with 5 or more record lines in the original style and unique to get their names to the forefront so you can do further investigation...

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703