Convert Multiple Lines Record Into One

Jenny (not her real name) inherited a spreadsheet from her predecessor. The spreadsheet contains details of job applicants who have come for interviews. One of the columns, "Remarks", contains 2 sets of details, one for "Comments" and the other, "Position applied for". The rest of the columns are merged to facilitate navigation around the spreadsheet. This causes a problem for Jenny. The way the data are captured (2 rows per record) prevent it from being used as a source for a pivot table.

Jenny wants to transfer "Position applied for" to a new column next to "Comments" so that each record will take up only one row. She would then remove the second row for each record.

It would have been easy to cut and paste the information if there are only a few records. But in the spreadsheet, there are over one thousand records! How much time do you think Jenny would take to do the conversion?

If she has mastered the techniques we teach, it will take only 5 minutes. Here's how:

1. Apply Auto-filter to the worksheet.
2. Remove all the merge cells.
3. Filter the records using column "Contact Numbers" and select "Non-blanks" as the criteria.
4. Select all the visible cells in column G "Position applied for".
5. Apply Goto Special and select the option visible cells only.
6. In the active cell (e.g. G32), type in the equal sign followed by the cell F33. Instead of hitting the "ENTER" key, Use the control key "CTRL" and "ENTER" key together. This will place the details in column G on the same row as the rest of the details in each record.
7. Remove the filter applied on the column "Contact Numbers".
8. You need to copy the entire column G and paste back to column G as values. This will remove all the formulas in Column G.
9. Sort the list in Ascending order based on the S/N. The blank rows will be moved to the back.
10. Delete the rows that are without S/N and you are done.

If you've attended our courses before, you can login to the Members' Area and watch the whole process in action.

We are now on Facebook. Click on this link to become a fan of everydayExcel Business Lab so that we can keep in touch with you and provide you with regular updates on our activities.

The class photos are in there too!

That's how our advanced Excel course in Singapore is conducted.