if cell text repeats, move the adjacent column to the same row

  • Thread starter Thread starter ILoveMyCorgi
  • Start date Start date
I

ILoveMyCorgi

In the first column I have a student number, the second column the book
title. In the next row the same student number, with a different title, and
so on until I get to the next student number and a new set of book titles the
student has checked out. Is there a way using visual basic where the one
unique student number displays with all of the titles for that student in the
same row?
 
This example assumes that the original data is on Sheet1 and the revised
format will be entered in Sheet2:

Sub newlist()
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Cells(1, 1).Value = w1.Cells(1, 1).Value
w2.Cells(1, 2).Value = w1.Cells(1, 2).Value
Ide = Cells(1, 1).Value
w1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 3
kk = 1
For i = 2 To n
If w1.Cells(i, 1).Value = Ide Then
w2.Cells(kk, k).Value = w1.Cells(i, 2).Value
k = k + 1
Else
kk = kk + 1
k = 3
Ide = w1.Cells(i, 1).Value
w2.Cells(kk, 1).Value = Ide
w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value
End If
Next
End Sub
 
Here is some code to do it

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

.Cells(i, "B").Resize(, 100).Copy .Cells(i - 1, "C")
.Rows(i).Delete
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
THANK YOU!!! You just made my life a little easier... Now I can incorporate
the output with another list for a longer row of data I plan to use later.
Thanks a million! susan
 
You have no idea how much this has helped me... I appreciate learning the two
different ways of accomplishing this task... thanks again, Bob. Susan
 
I've been trying to use this macro and it keeps crashing my system. I'm sure
I'm doing it wrong because I'm not very advanced with Visual Basic. I have
some data that I've got to find a way to organize.

Column A: Last Name
Column B: First Name
Column C: ID number

These are the rows that are being duplicated that I'd like to put into one
row where the columns of data are in D - M.

I've got about 6,000 rows of data that should be about 3,000 when all is
said and done.

Any suggestions?
 
Back
Top