put multiple lines together on one line

Danean
Danean used Ask the Experts™
on
I have HL7 lines that start with the following:

MSH
PID
PVI
AL1
AL1
AL1
AL1
ORC
OBR

Sometimes there can be 4 "AL1" in the message, sometimes just 1.

For each message in the workbook I need the lines to be combined on one line, until the next message.  ie.

MSH
PID
PVI
AL1
AL1
AL1
AL1
ORC
OBR
MSH
PID
PVI
AL1
AL1
ORC
OBR

becomes:

MSH|PV1|AL1|AL1|AL1|AL1|ORC|OBR
MSH|PVI1|AL1|AL1|ORC|OBR

and so on until end of file.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this macro. I have assumed that your data is in column A and starts in A1

Sub xform()
For Each cel In Range("A1:A" & Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row)
If cel.Value = "MSH" Then
Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = cel.Value
Else
Range("B" & Rows.Count).End(xlUp) = Range("B" & Rows.Count).End(xlUp) & "|" & cel.Value
End If
Next cel
End Sub

Author

Commented:
This works well but it is putting both messages on one row.

I need 2 rows in the above example:

MSH|PID|PV1|AL1|AL1|AL1|AL1|ORC|OBR
MSH||PID|PV1|AL1|AL1|ORC|OBR

No it puts them in two rows. Are you working on the given data or some other data?

Please paste the one-line result as outputted by the macro for me to see.

Author

Commented:
You are right.  It does work if I just work from the beginning of the HL7 message.  Here is a sample of what the whole message looks like.

MSH|^~\&|MEDITECH|YVMC|INTELEPACS|RMR|000000000000||ORM^O01|000000000|P|2.3|||AL|NE
PID|1|M000000000|0000000|M00000|TESTDATA^EVA^M|JULIA|00000000|F||C|PO BOX 770000^TEST DATA DRIVE^SOMEWHERE^CO^80477||(000)000-0000|||M|CH|V00008200000|XXX-XX-XXXX
PV1|1|E|ER|ER|||TEST^DOCTOR^TEST^^^^MD|||||||HWA||||ER||MCR|||||||||||||||||||DI||RE|||000000000000||||||V00000000000
AL1|1|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
AL1|2|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
AL1|3|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
AL1|4|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
ORC|SC|000000.000DI|||X|X||||XXXXX|||||||YAMP
OBR|1|000000.000DI||X-RAY^CXR 1V^XR CHEST 1 VIEW||000000000000||||||||||TEST^TEST^TEST^^^^MD||0000000.000DI|1|1|||||||1^^^000000000000^^A|||||||TEST^TEST^TEST
MSH|^~\&|MEDITECH|YVMC|INTELEPACS|RMR|000000000000||ORM^O01|000000000|P|2.3|||AL|NE
PID|1|M000000000|0000000|M00000|TESTDATA^EVA^M|JULIA|00000000|F||C|PO BOX 770000^TEST DATA DRIVE^SOMEWHERE^CO^80477||(000)000-0000|||M|CH|V00008200000|XXX-XX-XXXX
PV1|1|E|ER|ER|||TEST^DOCTOR^TEST^^^^MD|||||||HWA||||ER||MCR|||||||||||||||||||DI||RE|||000000000000||||||V00000000000
AL1|1|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
AL1|2|DA|F000000000^XXXXXXXXXXXXXXXX|XX|XXXXXXXXXXXXXXX|00000000
ORC|SC|000000.000DI|||X|X||||XXXXX|||||||YAMP
OBR|1|000000.000DI||X-RAY^CXR 1V^XR CHEST 1 VIEW||000000000000||||||||||TEST^TEST^TEST^^^^MD||0000000.000DI|1|1|||||||1^^^000000000000^^A|||||||TEST^TEST^TEST
Try changing

If cel.Value = "MSH" Then

with

If left(cel.Value,3) = "MSH" Then

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial