Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging slightly similar records | Excel Discussion (Misc queries) | |||
merging multiple files | Excel Discussion (Misc queries) | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Merging multiple worksheets | Excel Worksheet Functions | |||
multiple cell merging? | New Users to Excel |