Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reorganizing a filtered list. (excel 2002)

    Hello,

    I have a long list on my hands for individual companies and their corresponding employees, telephone number for the employees, and positions etc. My task is to split up the list so that the individual people each have their own row with corresponding telephone numbers positions etc whereas in the current list they are all groupd into the same rows. Anybody have an idea of how to do this not manually. Thanks.
    Attached Files Attached Files

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

    Re: Reorganizing a filtered list. (excel 2002)

    I'm afraid it's not clear to me how the list should be split.

    For example, cell D8 contains 3 names but cell E8 contains 4 lines with telephone numbers - how can I tell which number belongs to whom?

    Conversely, while cell D15 also contains 3 names, cell E15 contains 2 lines, one of which appears to be an extension.

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reorganizing a filtered list. (excel 2002)

    Hey Hans. Thanks for the help with the previous Post!

    Would it be possible to split it up in a way that gave each person an individual line with their bank and name etc, but to make it so that each line for the individual person included all of the possible numbers from the original row, along with the positions and emails?
    Attached Files Attached Files

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

    Re: Reorganizing a filtered list. (excel 2002)

    Here is a macro that splits the D column, but I don't think you'll like the result - all phone numbers, positions and e-mail addresses are listed for each person. It is not possible to determine what belongs to whom, for the reasons explained earlier.
    <code>
    Sub SplitLines()
    Dim r As Long
    Dim m As Long
    Dim arrNames
    Dim i As Integer
    Application.ScreenUpdating = False
    m = Range("D" & Rows.Count).End(xlUp).Row
    For r = m To 2 Step -1
    arrNames = Split(Range("D" & r), vbLf)
    For i = UBound(arrNames) To 1 Step -1
    If Not arrNames(i) = "" Then
    Range("D" & r).EntireRow.Copy
    Range("D" & (r + 1)).EntireRow.Insert
    Range("D" & (r + 1)) = arrNames(i)
    End If
    Next i
    If UBound(arrNames) > -1 Then
    Range("D" & r) = arrNames(0)
    End If
    Next r
    Application.ScreenUpdating = True
    End Sub</code>

  5. #5
    New Lounger
    Join Date
    Jul 2008
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reorganizing a filtered list. (excel 2002)

    Hey i figured it out! Thanks again.

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

    Re: Reorganizing a filtered list. (excel 2002)

    If you need this macro for this specific workbook only, you can store it in a module in the workbook itself. One way to do so is via the Tools | Macro | Macros dialog; another way is to activate the Visual Basic Editor by pressing Alt+F11, then creating an empty module by selecting Insert | Module. You can then write a macro or paste code from Woody's Lounge or elsewhere.
    Clicking in a word of the code and pressing F1 will display help about that word, if available.

    If you need to use the macro in several workbooks, you can store it in your personal macro workbook Personal.xls. See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post>.

Posting Permissions

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