# 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

##### New Member
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!

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Nevermind, I was able to create what I was looking for, this did the trick:

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

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.
Code:
``````[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

Replies
0
Views
193
Replies
3
Views
423
Replies
6
Views
318
Replies
6
Views
134
Replies
5
Views
825

1,203,467
Messages
6,055,590
Members
444,800
Latest member
KarenTheManager

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back