No takers among the Excel expert division so far it seems so I thought I might make a few suggestions.
Depending on the nature of the data (and this is a very long shot in most cases!) you might be able to do something with a SUMMARY using the values in your second example column as an ACROSS display.
Normally that would just give you a count figure but IF you can control the effective column heading to known values of the second column you can change the count to another displayed value with some success. (That said the only time I have found it useful was dealing with years where the known year number range allowed control of the values in columns using MIN or MAX measures.)
A quick way would be to create a simple summary from the values just as they are and then set the summary to suppress duplicates in column one. Export the result to a report file.
No you can define a template that captures the column 2 rows for each column 1 value in a single multi-row field, albeit with spaces between the values rather than ";". If you need the semi-colon spearator create a calculated field that uses the data from the column 2 extraction but replaces the spaces with ";". There are a few ways to do that with Monarch functions. (If they are not familiar to you let us know and we can suggest some options - assuming the first part of the solution appeals to you!)
Hide the original column 2 and you should have the result you need (I think).
Essentially, you want to denormalize the database. Denormalization is never a fun process, and the best solution I have found is to use Access.
First, get the spreadsheet to Access (pick your favorite method, but Monarch will work fine).
Then, create a module with a Denormalize function. Give it a string argument pt. Then, it opens a cursor to the database, and reads all records that match on pt, and writes them to one string with a semicolin between each value.
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim Results As String
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Results = ""
If rst.RecordCount > 0 Then
Do Until rst.EOF
If (Results = "") Then
Results = rst!Value
Results = Results & ";" & rst!Value
Denormalize = Results
/font[/quote]Finaly, write a query that selects the unique occurances of each pt, and calls the denormalize function.
FROM ExcelTable[/font][/quote]This query can be imported to Monarch, exported as an Excel spreadsheet, or whatever you need.
If you have a limited number of values for a given primary key (such as there can be no more than 3), then you can do it with just Monarch, but it is a multi-pass operation, and even messier than this solution (which can handle unlimited values for a given key). I haven't come up with a better solution for denormalization in Monarch, but I'm always open to new ideas. Let me know if you have any questions.