Everyone,

I know I need to do this as VBA, but I am so confused on how to write it, I would like to get some help and direction. I have attached the spreadsheet that I am working with. I need to do the following:

A. Each row should begin with the program number. Therefore, I need to break out the program number in column A '[XXXXXXXXXX]' to
it's own column.
B. Take the next value from Column A nafter the [program_number] and create in a column called Program Name and move it there.
C. In column A, the address information may have 3 or 4 lines.
A. If 4 lines, then the columns are Addr Line 1, Addr Line 2, Addr Line 3, City/State and move the data into this column.
B. If 3 lines, it is Columns Addr Line 1, Addr Line 2, Addr Line 3 (blank), City/State and move the data into this column.
D. Column D, 1st row is Phone #, 2nd row is Fax #, 3rd row is email. I need to move row 2 and row 3 to their own column.

The objective is to get everything on 1 row for each program. Can someone plz help me with how to put some VBA code in place to do this? I would appreciate it big time. Thanx in advance.

Jerry

Attached Files

ACGME_IM_PROGRAM_LIST.xls (291.5 KB, 11 views)

If you adjust your data so that there are always 5 lines per record, as per the first 605 rows in the attached, the formulae on Sheet2 will do the job without the need for a macro. Simply copy the forumlae on Sheet2 down as far as you'll need (about another 250 rows).

If you look at Sheet 1, you'll see that '[1402121537] Leonard J Chabert Medical Center Program' has only 4 data rows. On Sheet2, the entry after that has 'VALUE#' in column A. Insert a line above '[1402122146] Ochsner Clinic Foundation Program' on Sheet1 and the Sheet2 output will be healed (the formulae in columns E & F adjust the output for 3 & 4 line addresses). Continue as necessary until all records on Sheet1 have 5 rows.

Attached Files

ACGME_IM_PROGRAM_LIST.zip (105.5 KB, 13 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I want to mark this as resolved and cannot find where to do it at. Can someone plz direct me? Thanx in advance.

Jerry

Hi Jerry,

The tools for marking a thread as solved are under the 'Thread Tools' dropdown.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]