Forum Discussion
Combining Rows of Data
I have a survey that has six different parts. A coaching observer selects which of the six coaching sessions to observe and they are taken to the relevant block of behaviors to observe.
Each person being observed should have six rows of data in the Excel file after it is downloaded, with the only things being the same are the demographics (employee ID, name).
I am looking for a function (or something) that I can use to combine the rows based on the Employee ID field as the common denominator so that I don't have to go through that manually after exporting the survey into Excel and dropping the data into another file.
Eventually, I will get up to 8,000 rows of data and that is way too much to do manually.
4 Replies
The phrase 'combining rows', I took that to mean you wanted to add up the scores for a person if they had more than 1 score from a particular coach AND you would wanted each employee to have only a single row.
If that's close to what you meant, then take a look at the attached spreadsheet. It only took me a few minutes with the new Sort and Unique functions.
JBF_54Thanks for the response. I am not sure what happened, but I did not see a change. It could be due to the fact that we have Excel 2013.
Anyway, I am looking to take all the coaching scores for each Employee ID and merge them into the same row so that I can copy/paste into a dashboard file which then uses a VLOOKUP to pull that data to a dashboard.If we don't combine all coaching scores for the same ID into one row, the VLOOKUP was only pulling data from the first instance of the employee ID
Jon_Hurtado The SORT and UNIQUE functions I used are in newer Excel's. However, I used them only to copy the Employee ID's to the 2nd tab in an automated fashion. You can do this manually is just few steps by highlighting the column of ID's on the 1st sheet, select the 2nd sheet, and paste them. Then use the Excel feature to remove duplicates (on the Data tab, Data Tools group).
Good luck.
