Login  | |
|
 |
multiple data in cell to multiple rows - 3/22/2008 5:53:29 AM
|
|
 |
|
| |
mountaingoat
Posts: 2
Score: 0
Joined: 3/22/2008
Status: offline
|
I have an Excel worksheet that has multiple rows and columns. In two of the columns, there are some rows that contain multiple pieces of data. I'd like to copy that data, and the common info in the other columns for that particular row, and copy it all into a new row.
Below is what the original worksheet looks like (I added the row numbers):
1Location Department Admin IP Hostname Building
2Alaska HR John Doe 1.1.1.1 lak0001 D-17B
3California Customer Service Scott Marks 2.2.2.1 2.2.2.2 2.2.2.3 2.2.2.4 2.2.2.5 2.2.2.6 2.2.2.7 2.2.2.8 dca0001 dca0004 lca0239 lca7230 lca0337 dca099 dca293 dca991 A37 West
4Colorado Information Technology John Brock 3.3.3.273 3.3.5.2 3.3.270.179 dco230 lco09 dco62 North Court 17
5Idaho Accounting Lisa Doe 4.4.4.7 did39 East Concourse
6Montana Maintainence Mark Smith 5.5.5.107 dmt07 717-A
7Pennsylvania Security Bill Sylvia 6.270.15.37 lpa038 BWO
8Wyoming Executive Joe Bloe 7.2.55.9 lwy010 whistler
This is what I'd like the new worksheet to look like (I added the row numbers):
1Location Department Admin IP Hostname Building
2Alaska HR John Doe 1.1.1.1 lak0001 D-17B
3California Customer Service Scott Marks 2.2.2.1 dca0001 A37 West
4California Customer Service Scott Marks 2.2.2.2 dca0004 A37 West
5California Customer Service Scott Marks 2.2.2.3 lca0239 A37 West
6California Customer Service Scott Marks 2.2.2.4 lca7230 A37 West
7California Customer Service Scott Marks 2.2.2.5 lca0337 A37 West
8California Customer Service Scott Marks 2.2.2.6 dca099 A37 West
9California Customer Service Scott Marks 2.2.2.7 dca293 A37 West
10California Customer Service Scott Marks 2.2.2.8 dca991 A37 West
11Colorado Information Technology John Brock 3.3.3.273 dco230 North Court 17
12Colorado Information Technology John Brock 3.3.5.2 lco09 North Court 17
13Colorado Information Technology John Brock 3.3.270.179 dco62 North Court 17
14Idaho Accounting Lisa Doe 4.4.4.7 did39 East Concourse
15Montana Maintainence Mark Smith 5.5.5.107 dmt07 717-A
16Pennsylvania Security Bill Sylvia 6.270.15.37 lpa038 BWO
17Wyoming Executive Joe Bloe 7.2.55.9 lwy010 whistler
I'm totally new to vb scripting, and don't have a clue. I've been trying to write code for this, and have searched VisualBasicScript using a large variety of search word combos, and have searched the web, but with no luck.
I know that I'll need a loop to search within the two columns (D and E) for cells with multiple entries (IPs and Hostnames), then loop through each of those multiple pieces of info, and then copy each one into a new row, along with all of the common data from columns A, B, C and F.
I have tried so many different versions of code to try to get this to work, than I have no idea which of the 10 or so versions to even post -- since they've all failed with errors at runtime in even trying to do the most basic of parts of this.
The worksheet that I need to run this on is over 9000 rows deep. I created the data above just to represent the type of info with which I need to work.
I'd appreciate any and all help. Thanks.
Warren
|
|
| |
|
|
|
|
RE: multiple data in cell to multiple rows - 3/24/2008 3:00:56 AM
|
|
|
|
| |
dm_4ever
Posts: 2641
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline
|
Have you put anything together yet? If not, please do so and let us see what you currently have. We can then start helping you find a solution.
_____________________________
dm_4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm
|
|
| |
|
|
|
|
RE: multiple data in cell to multiple rows - 3/24/2008 11:23:27 AM
|
|
|
|
| |
mountaingoat
Posts: 2
Score: 0
Joined: 3/22/2008
Status: offline
|
Sub test()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngSrcIPs As Range, rngSrcHosts As Range
Dim arrIPs, arrHosts
Dim NoOfIPs As Long, NoOfHosts As Long
Dim LastRowDst As Long
Set wsSrc = ActiveSheet
Set wsDst = Worksheets.Add
NoOfIPs = 1
NoOfHosts = 1
'wsSrc.Range("A1:F1").Copy wsDst.Range("C1")
Set rngSrcIPs = wsSrc.Range("A1")
Set rngSrcHosts = wsSrc.Range("A1")
LastRowDst = 1
While rngSrcIPs.Value <> ""
' Array info for IP Addresses
arrIPs = Split(rngSrcIPs.Offset(, 3).Text, " ")
If IsArray(arrIPs) Then
NoOfIPs = UBound(arrIPs) + 1
Else
NoOfIPs = 1
End If
' 7 is the number of columns from source worksheet to write to dest worksheet
rngSrcIPs.Resize(, 7).Copy wsDst.Range("A" & LastRowDst).Resize(NoOfIPs)
' Copy locations for IP Addresses
' "D" is column where IP array data is written
wsDst.Range("D" & LastRowDst).Resize(NoOfIPs) = Application.WorksheetFunction.Transpose(arrIPs)
' "4" is column where IP array data is written; "E" is first column after the
' array data that was just written, where the regular data is written
rngSrcIPs.Offset(, 4).Copy wsDst.Range("E" & LastRowDst).Resize(NoOfIPs)
' Array info for Hostnames
arrHosts = Split(rngSrcHosts.Offset(, 4).Text, " ")
If IsArray(arrHosts) Then
NoOfHosts = UBound(arrHosts) + 1
Else
NoOfHosts = 1
End If
' 7 is the number of columns from source worksheet to write to dest worksheet
rngSrcIPs.Resize(, 7).Copy wsDst.Range("A" & LastRowDst).Resize(NoOfHosts)
' Copy locations for Hostnames
' "E" is column where IP array data is written
wsDst.Range("E" & LastRowDst).Resize(NoOfHosts) = Application.WorksheetFunction.Transpose(arrHosts)
' "5" is column where IP array data is written; "F" is first column after the
' array data that was just written, where the regular data is written
rngSrcHosts.Offset(, 5).Copy wsDst.Range("F" & LastRowDst).Resize(NoOfHosts)
' Check this line and change NoOfHosts to NoOfIPs if errors
LastRowDst = LastRowDst + NoOfHosts
Set rngSrcIPs = rngSrcIPs.Offset(1)
Wend
End Sub
It seems to be having problems when it hits one of the cells with nothing entered.
|
|
| |
|
|
|
|
|
Printable Version
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread