Consolidating one column of data into an existing row of data in Excel

Question

I have a Crystal Report that is exported as data only into excel to create a flat file. The issue is that the way the data is returned in crystal, it returns a record that looks like this:

Date PT/ ID/ Case Type/ pt arrives /pt on table/ start/ end /out of room/ procedures/doctor

10/14/2013 12345 diagnostic cath 5:15 5:30 6:00 6:30 7:15 left heart cath  dr. bob
                                                          Coronary Angio
                                                          Arch Angiography

I need to get everything for the patient on to one row.

The procedures return in a list format. The issue is that every patient has a different set of procedures and they are done in different sequence so I can't assign a seq# to each procedures or say grab first 4 procedures and put in single row. Pivot is not working and Concatenate each record takes too long.

This is done on a weekly basis and could involve hundred or so records. Using Excel 2002(?) and Crystal 9

Answer 1

Well, import the data as Text Separated Value using / as the delimiter.

Were it my data to work with, I might also do a little list prep with grep, sed and awk from http://gnuwin32.sourceforge.net/summary.html which gives batch files a jet pack with an afterburner, if Crystal can't export into a Text Separated Value ('TSV', 'TXT') or Comma Separated Value ('CSV') file in the format you need for import.