Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-30-2012, 01:06 PM
ballj_35 ballj_35 is offline Moving data from multiple rows to 1 row for each program number Windows XP Moving data from multiple rows to 1 row for each program number Office 2007
Novice
Moving data from multiple rows to 1 row for each program number
 
Join Date: May 2011
Posts: 9
ballj_35 is on a distinguished road
Default Moving data from multiple rows to 1 row for each program number

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
File Type: xls ACGME_IM_PROGRAM_LIST.xls (291.5 KB, 9 views)
Reply With Quote
  #2  
Old 07-31-2012, 09:55 PM
macropod's Avatar
macropod macropod is offline Moving data from multiple rows to 1 row for each program number Windows 7 64bit Moving data from multiple rows to 1 row for each program number Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
File Type: zip ACGME_IM_PROGRAM_LIST.zip (105.5 KB, 11 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 08-01-2012, 02:36 AM
ballj_35 ballj_35 is offline Moving data from multiple rows to 1 row for each program number Windows XP Moving data from multiple rows to 1 row for each program number Office 2007
Novice
Moving data from multiple rows to 1 row for each program number
 
Join Date: May 2011
Posts: 9
ballj_35 is on a distinguished road
Default

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

Jerry
Reply With Quote
  #4  
Old 08-01-2012, 05:10 AM
macropod's Avatar
macropod macropod is offline Moving data from multiple rows to 1 row for each program number Windows 7 64bit Moving data from multiple rows to 1 row for each program number Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Jerry,

The tools for marking a thread as solved are under the 'Thread Tools' dropdown.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving data from multiple rows to 1 row for each program number Mail Merge to create specific number of table rows flackend Mail Merge 4 12-01-2023 02:49 PM
Moving data from multiple rows to 1 row for each program number Fixing number or rows in a table burnsie Word 2 07-12-2011 02:59 AM
Moving data from multiple rows to 1 row for each program number moving data from single to multiple columns? mzimmers Excel 3 08-23-2010 08:20 AM
Moving data from multiple rows to 1 row for each program number Moving formula range multiple cells when moving sum over one cell FraserKitchell Excel 4 02-26-2010 10:38 AM
Moving data from multiple rows to 1 row for each program number Table rows moving to new page when you don't want them to graham Word Tables 1 11-16-2009 08:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:54 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft