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

[jbyrne]

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.

[Jay Petrulis]

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

[jbyrne]

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.

[jbyrne]

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

[Jay Petrulis]

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

[jbyrne]

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

[jbyrne]

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.

[Jay Petrulis]

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