Merging Multiple Records into One Ro

  • Thread starter Thread starter MJD
  • Start date Start date
M

MJD

I apologize if this question is a no-brainer. I have some data that I need to
sort and there has to be an easier way than the way I'm doing it.

Col A: Col B: Col C: Col D:
Company Mon Tues Wed

ABC 12
ABC 34
ABC 56


I would like this to sort, remove duplicate entries and look like this:

Company Mon Tues Wed
ABC 12 34 56

Any suggestions?

Thanks,
Micah
 
I didn't develop this; found it on the DG a while back:
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


Regards,
Ryan--

PS, here is the original link:
http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1

GS is brilliant at this stuff!!
 
Thank you! Just to clarify, I'm not that great with Visual Basic, so the
Company name is in Column A, a company id number is in Column B, and the I
want to move to one rows are in Columns C through K. The total amount of rows
is 8,500.

How would that look in this script? Sorry, I'm very green at this stuff.
 
It seems like a Pivot table could do this job very easily. As for a
formula solution, assuming that the destination table starts at K2
("ABC"), then for Mon (L2):

=SUMPRODUCT(($A$2:$A:101=$K2)*(B$2:B$101=L$1))

This formula is assuming there will be only one instance of ABC and
Monday.

HTH
Kostis Vezerides
 
I've been trying this out and it keeps freezing up on me.

Here are the rows:
Column A: Last Name
Column B: First Name
Column C: Unique ID

Then the data I need compiled into one record are
Column D - M.

I've cleaned it up a bit so it's down to 5,900 records that should be around
3,300 when the table is run.

I think I'm missing something. Any help?
 
Back
Top