Results 1 to 11 of 11
  1. #1
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Sorta combining rows (Excel 2000 SR1)

    OK, so I have this big honkin' spreadsheet (to me, at least) that's a homegrown database of network printers that have been configured for use with SAP. It was inherited, and I want to simplify it. As it works now, if we add the same printer to three separate servers, we add 3 lines for that printer (as an example, I'll say they are rows 101, 102 and 103) - each identical except for the server name, which is column S. I'd like to be able to combine all the rows for each printer and use the cells after S101 so that the second and third server names would occupy cells T101 and U101, instead of S102 and S103. The problem is that the number of servers varies for each printer - many are on only one while others can be on as many as 7 or 8. There's no data after column S, so I wouldn't have to worry about overwriting any data or a varying number of inserted cells for each row. Is there a way to do something like this?

    Thanks, as always -
    Have a cookie -

    Don

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    Is your data like this:

    <table border=1><td></td><td align=center>R</td><td align=center>S</td><td align=center>101</td><td>Printer 1</td><td>ServerA</td><tr><td align=center>102</td><td align=right>
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Sorta combining rows (Excel 2000 SR1)

    Basically yes, that's the format. And columns A through Q all pertain to the printer, and all are identical for any given printer - the only thing different between the rows would be the server name.
    I want to not only eliminate the repetition and unnecessary bulk, but also make it easier to search for printers that meet certain criteria without pulling duplicate records.
    Have a cookie -

    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    Here is a macro that will modify your table. Instructions:
    1. <LI>Make a backup copy of your workbook!
      <LI>Switch to the Visual Basic Editor, and insert a Module (not a Class Module)
      <LI>Copy the macro from this post into the module.
      <LI>Check the section in the code marked "--- Adapt as needed ---" and change the values according to your situation:
      <UL><LI>Replace "Printers" by the name of your worksheet.
      <LI>Replace "A1" by the upper left corner of your printer table (including heading row).
      <LI>lngIDColumn must hold the number of the column within the printer table that contains the unique identifier for the printer. In the code "as is", this is 1, corresponding to column A.
      <LI>lngServerColumn must hold the number of the column within the printer table that contains the server name. By default, it is 19, corresponding to column S.
      <LI>If the printer table doesn't have a heading row, replace lngRow = 2 by lngRow = 1.
    <LI>Switch back to Excel.
    <LI>Close and save the workbook and make another backup copy. You now have a backup of the original situation, and of the workbook with the macro.
    <LI>Open the workbook again and select Tools | Macro | Macros...
    <LI>Select ModifyTable and click Run.[/list]<img src=/w3timages/blueline.gif width=33% height=2>

    Sub ModifyTable()
    Dim rngSource As Range
    Dim lngRow As Long
    Dim lngRowCount As Long
    Dim lngColumn As Long
    ' --- Adapt as needed ---
    Set rngSource = Worksheets("Printers").Range("A1").CurrentRegion
    Const lngIDColumn As Long = 1 ' Column A
    Const lngServerColumn As Long = 19 ' Column S
    lngRow = 2 ' Assumes that first row of rngSource contains headers
    ' --- End of values to adapt ---
    Do While rngSource.Cells(lngRow + 1, lngIDColumn) <> ""
    lngColumn = lngServerColumn
    Do While rngSource.Cells(lngRow + 1, lngIDColumn) = _
    rngSource.Cells(lngRow, lngIDColumn)
    lngColumn = lngColumn + 1
    rngSource.Cells(lngRow, lngColumn) = _
    rngSource.Cells(lngRow + 1, lngServerColumn)
    rngSource.Cells(lngRow + 1, 1).EntireRow.Delete
    Loop
    lngRow = lngRow + 1
    Loop
    Set rngSource = Nothing
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  5. #5
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Sorta combining rows (Excel 2000 SR1)

    Thanks, Hans - are there any characters or character combinations that Visual Basic doesn't like to see in a macro? Specifically, the macro halts on the Set rngSource line - with the message "Runtime error '9'"............"Subscript out of range"

    I replaced the worksheet name with the one on the worksheet itself (PRINTER Master List 2003) and the column of the printer's unique identifier is E, for which I put 5 in the field IngIDColumn. I figure I'm doing something wrong, but I have no idea what it could be.

    I tried removing the spaces in the worksheet name, but that didn't work....so I stopped before I make a mess.
    Have a cookie -

    Don

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    If your statement looked something like this:

    <pre> Set rngSource = Worksheets("PRINTER Master List 2003").Range("A1").CurrentRegion
    </pre>


    with the worksheet name enclosed in quotes, then that message is saying that you don't have the correct name for the worksheet. Maybe there are some spaces missing in the string. Try using Ctrl/C to copy the name from the sheet and paste it into the statement to be sure you have it correct.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    <P ID="edit" class=small>(Edited by JohnBF on 17-Jul-03 15:30. Must not have saved the working version: ".Offset(2, 1).EntireRow.Delete" munges the For loop, " should be ".Offset(2, 1).Clear")</P>Hans never sleep, and so although I had prepared these two macros, he beat me to posting code, so I wasn't going to. I had the same questions he did, which included clarifying the range to be operated on. So I wrote mine on the basis that you, the user, have to select the topmost cell from which you want the code to run. It may be easier to work with, though Hans code is better and "all-in-one". First run this:

    Sub aligncol2rows()
    Dim rngOp As Range, rngCell As Range
    Dim lngC As Long
    If vbYes <> MsgBox("is the selected cell at the top cell of the Printer column info?", _
    vbYesNoCancel) Then Exit Sub
    Application.ScreenUpdating = False
    On Error Resume Next
    Set rngOp = Range(ActiveCell, Cells(ActiveSheet.Rows.Count, _
    ActiveCell.Column)).SpecialCells(xlCellTypeConstan ts)
    If Not rngOp Is Nothing Then
    For Each rngCell In rngOp
    With rngCell
    .Offset(0, 2).Value = .Offset(1, 1).Value
    .Offset(1, 1).Clear
    .Offset(0, 3).Value = .Offset(2, 1).Value
    .Offset(2, 1).Clear
    End With
    Next rngCell
    End If
    Set rngOp = Nothing
    Application.ScreenUpdating = True
    End Sub

    Then select the area with the blank rows and run this:

    Sub DeleteBlankRows()
    Dim rngToUse As Range
    Dim lngCtr As Long
    Application.ScreenUpdating = False
    Set rngToUse = Intersect(ActiveSheet.UsedRange, Selection)
    If rngToUse Is Nothing Then Exit Sub
    For lngCtr = rngToUse.Row + rngToUse.Rows.Count To rngToUse.Row Step -1
    If Application.WorksheetFunction.CountBlank(Rows(lngC tr)) = _
    Application.Columns.Count Then Rows(lngCtr).Delete Shift:=xlUp
    Next lngCtr
    Set rngToUse = Nothing
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    Your requirements for reducing redundancy, and querying the printer data independently, suggests separating the printer & server data onto two worksheets (tables). One contains the A->Q information relating specifically to the printer, while the other contains references to each printer (on the other sheet) followed by a list of servers that use it. Would this be of any value?

    Alan

  9. #9
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Sorta combining rows (Excel 2000 SR1)

    Um...the bell finally rang - the light bulb over my head finally got replaced: you mean the SHEET name, as in on the tab; NOT the name of the spreadsheet. (How sheepish can I look right now? baaaaaaaaaa) That was one of the few things I really should have known anyway. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    I'll try it in the morning.

    And as far as simplification goes...one of the reasons I'm doing this is because sometime next year I don't expect to be gainfully employed here, so I'm trying to make it easy enough for the simpletons who are replacing my team to understand what's going on. Hey - they're getting paid less, so of course they can do the job better than us! <img src=/S/hosed.gif border=0 alt=hosed width=73 height=24>
    Have a cookie -

    Don

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorta combining rows (Excel 2000 SR1)

    Yup. The file is known as a Workbook and the sheets in the Workbook are known as Worksheets.
    Legare Coleman

  11. #11
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Sorta combining rows (Excel 2000 SR1)

    Well, it worked like a charm - reduced the spreadsheet from over 3300 rows down to 1125.

    Thanks, Hans - and everyone else too!
    Have a cookie -

    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •