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

jbyrne

Board Regular
Joined
Feb 22, 2002
Messages
178
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Let me try it again, I may be doing something wrong.... I'll try it and get back to you....Thanks
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top