Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Moving 2nd 3rd 4th row to the end of 1st row with the same n

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I was at work and typing fast so I don’t think I came across clear at all. I will now try to explain what I am trying to accomplish now that I am not doing more than 15 things at a time!!

    I have the following worksheet Columns A thru M
    A NAME
    B STREET ADDRESS
    C BLANK (ADDRESS2)
    D CITY
    E STATE
    F ZIP CODE WITH 4 DIGIT EXTENTION
    G A “FROM DATE” WHEN THE ADDRESS WAS REPORTED
    H A “TO DATE” WHEN THE ADDRESS WAS REPORTED
    I SOCIAL SECURITY NUMBER
    J NAME OF EMPLOYER1
    K DATE EMPLOYER 1 WAS REPORTED
    L NAME OF EMPLOYER 2
    M DATE EMPLOYER 2 WAS REPORTED

    The problem I am having is that some people have more addresses than others. What I need to do the following. Let’s say for instance we have this….

    John Smith 123 5th Street Tallahassee Fl 32301-5837 Oct-98 Nov-00 123-45-6789 Tallahassee Diner 1-Aug AJ Auto Parts Feb-00

    John Smith may have 4 different listings taking up row 1-4. What I want to do is put all of John Smiths info on 1 row. (Putting the 2nd listing for John Smith and all his info starting at N1 thru Z1 and so on…. Hope this is more clear to everyone. Any help would be appreciated.


  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Did the routines I posted fail? It seems to me that changing the range which is being copied from
    ,4).copy
    to
    ,13).copy

    would work (sorry, I can't recall the code off hand)

    Let us know what is wrong with it, or what else you need.

    Additionally, please don't start a new thread on the same topic, if possible. This should've been added to your prior thread so that there is no loss of continuity, and clowns like me don't have to go find the previous post.

    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay,

    Sorry about that starting a new thread. I'm new to realitively new to the board but loving all the help I get. I know now thanks to you to continue on the same thread. I copied the code and ran the macro. It seemed to take the first row of each name and than I got an error when it pasted the 4th record.

    I believe that was my fault for not explaining exactly what I needed.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What I basically want is excel to look at a2 and if a2 is the same name as a1 than move row 2 to N1 TO NZ
    than continue downward....
    look at a3, if same as a1 then move entire row to end of row 1

    look at a4, if different name, look at a5, if same as a4 than cut entire row 5 and paste to end of row 4.

    hope this helps

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    The routine is working for me, no matter how many records.

    What it is doing is getting a list of all the uniques names in column A of the data sheet and copies them to the second sheet. It then goes through the data set 1 by 1 and loads the corresponding values to each row where the names match. Multiple names get sent to the end of the row as requested.

    When you say it bombs, what exactly is happening? I do not doubt that you are having a problem, it is just that I cannot replicate it.

    Jay

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Let me try it again, I may be doing something wrong.... I'll try it and get back to you....Thanks

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    177
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I ran the macro again, it inserted 7 names in column G and than received a run bug error #9.

    I looked at sheet2 and there was nothing there.


  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Delete the sub and use this one, instead.

    ---------------
    Sub test()
    Dim counter As Long, lastrow As Long, x As Long
    Dim matchrow As Long, lastcol As Integer

    With ActiveSheet
    .UsedRange
    lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Columns("N:N").Clear
    .Columns("A:A").AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True
    .Range("N1").Delete shift:=xlUp

    counter = WorksheetFunction.CountA(.Range("N:N"))
    .Range("N1:N" & counter).Copy Sheets("sheet2").Range("A1")
    .Range("N1:N" & counter).ClearContents

    For x = 2 To lastrow
    matchrow = Application.Match(.Cells(x, 1), Sheets("Sheet2").Range("A:A"), 0)
    lastcol = Sheets("Sheet2").Cells(matchrow, 256).End(xlToLeft).Column
    .Range(.Cells(x, 2), .Cells(x, 13)).Copy Sheets("Sheet2").Cells(matchrow, lastcol + 1)
    Next x
    End With
    MsgBox "Done!"

    End Sub
    --------------------

    Please note that this will *not* duplicate the names, only the extra information. If that is required, it will be an easy fix, but we need to get the main routine running for you.

    Bye,
    Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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