Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MJD MJD is offline
external usenet poster
 
Posts: 5
Default Merging Multiple Records into One Ro

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default Merging Multiple Records into One Ro

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/comm...=en-us&m=1&p=1

GS is brilliant at this stuff!!

--
RyGuy


"MJD" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MJD MJD is offline
external usenet poster
 
Posts: 5
Default Merging Multiple Records into One Ro

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.

"ryguy7272" wrote:

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/comm...=en-us&m=1&p=1

GS is brilliant at this stuff!!

--
RyGuy


"MJD" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Merging Multiple Records into One Ro

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

On Apr 8, 7:56 pm, MJD wrote:
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MJD MJD is offline
external usenet poster
 
Posts: 5
Default Merging Multiple Records into One Ro

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?

"vezerid" wrote:

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

On Apr 8, 7:56 pm, MJD wrote:
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Merging slightly similar records Ken DeYoung - Educational Consultant Excel Discussion (Misc queries) 4 January 9th 08 09:17 PM
merging multiple files ILXCEL Excel Discussion (Misc queries) 1 March 27th 06 06:16 PM
return multiple records matching multiple criteria Karthik Excel Worksheet Functions 2 March 22nd 06 05:42 PM
Merging multiple worksheets commissionerwalker Excel Worksheet Functions 1 February 7th 06 05:24 PM
multiple cell merging? Coops New Users to Excel 3 December 22nd 05 04:35 AM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"