Need a macro\Formula to see if a cell contains a text string, if it does, then i need to transpose the data to a column

[DrFistington]

I'm currently working with a report that's out of infoview, but I need to easily reformat the data. This report will need to be looked at weekly, so I'd like to find a way to easily reformat the data with a macro or formula. Here's my situation:

I get an infoview report about IV starts in patients. The patients Name, Department, MRN, and IV Placement Date all come on one row, but the information about the IV is entered in the row below the patients name. I need to bring that information up into a column in the row above. To sum it up visually I need to turn this:

A B C D
Pat Name -Department - MRN - Placement Date
IV Placement Info
Pat Name -Department - MRN - Placement Date
IV Placement Info

Into this:
A B C D E
Pat Name -Department - MRN - Placement Date - IV Placement Info
Pat Name -Department - MRN - Placement Date - IV Placement Info

The only monkey wrench to throw in is that some patients may have multiple IV's, so it won't always just be every other line that has the IV info. Sometimes it will look like this:
A B C D
Pat Name -Department - MRN - Placement Date
IV Placement Info
IV Placement Info
IV Placement Info
Pat Name -Department - MRN - Placement Date

Any help on this would be great!

 

[DrFistington]

Nevermind, I was able to create what I was looking for, this did the trick:

=IF(ISNUMBER(SEARCH("Removal",A3)),A3, "Not Found")

It looks at the row below the column I pasted this in and seems to do the job just fine.

 

[MickG]

Try this:-
Assumed that your Actual Data starts row(2) (Row1 headers) and that column (B) cells in rows with "IV Placement Info" are Empty.

[COLOR=navy]Sub[/COLOR] MG18Jun58
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] AcRng       [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Col         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nRng        [COLOR=navy]As[/COLOR] Range
    [COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not Dn.Offset(, 1) = vbNullString [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] Col = Cells(Dn.Row, Columns.Count).End(xlToLeft)
            c = 0
        [COLOR=navy]Else[/COLOR]
            c = c + 1
            Col.Offset(, c) = Dn
            [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] nRng = Dn
            [COLOR=navy]Else[/COLOR]
                [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]If[/COLOR] Not nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] nRng.EntireRow.Delete
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick