Combine Multiple Rows into Single Row

[t0mato]

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!

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

[t0mato]

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

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