Extracting specific lines from datasheet

**SlipperyPete** · 12-19-2006, 11:52 AM

Here's my problem;

I have a sheet with thousands of lines of data captured, eg:

A B C
ABC 1231 4321
PID M1234 H1234
OBR 98767 LEVO500
XYZ 5432 23456

I want to extract only those lines of data that have certain content in column A, in this case any lines that begin with "PID" or "OBR" I want to capture:

A B C
PID M1234 H1234
OBR 98767 LEVO500

Furthermore, I'd like to combine these output lines into one line, with or without the content of column A included.

Ideally, my output based on the above dataset would be:

M1234 H1234 98765 LEVO500

Suggestions are very much appreciated!
Thanks very much!

**jetted** · 12-19-2006, 02:47 PM

Hi

Your requirement are not to clear. However, this code will loop trough sheet named Original_List looking for PID or OBR and will copy this info to sheet name Sort_List.

It is always a good practice to attached a sample of your work and sample of what you are expected to have.

Sub sort_pid()
    'Assuming your data is on sheet named Original_List
    'Assuming the new info will be on a sheet named Sort_List
    Sheets("Original_List").Select
    rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
    For i = 1 To rowcount
        Sheets("Original_List").Select
        Range("a" & i).Select
        valu = ActiveCell.Value
        If valu = "PID" Or valu = "OBR" Then
            ActiveCell.EntireRow.Copy
            Sheets("Sort_List").Select
            rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
            Range("a" & rowcount + 1).Select
            ActiveCell.PasteSpecial (xlPasteAll)
        End If
    Next
End Sub

**SlipperyPete** · 12-19-2006, 05:36 PM

Thanks for the advice jetted -- I've attached a file with three sheets; I believe what you've posted will take me from sheet 1 to sheet 2; any suggestions on how to merge the adjacent lines in sheet 2 to create the output in sheet 3?

I'm a total novice when it comes to visual basic -- all help's appreciated!

Thanks!

**SlipperyPete** · 12-19-2006, 05:53 PM

I tried your code, subbed my file name for the one you provided, but am getting a "Compile error: Invalid Outside Procedure".

**jetted** · 12-21-2006, 10:30 AM

Hi

Sorry for the delay. You will find attached a spreadsheet, run the macro named sort_pid and it will pull the info. As for the merging, this part is still on clear his the data always in this format

PID
OBR
PID
OBR
PID
OBR.... I do I know which PID goes with OBR, I need a common field between them so it could be associated with.