Transpose!!
Hi, I have quite a tricky problem.. (I've attached a sheet as an example, I'll explain it further down) I have a list of names in column a (a very large amount), there are lots of duplicate names, and next to them in column b are the categories associated with each name. So if a name is repeated 5 times in column a, it will have 5 different categories. I need to summarize it so that I only have 1 name per person, but all of the categories associated with the person going along in each column as per the sheet attached. I've been trying to figure it out but can't do it (except manual paste specialtranspose which would take me weeks!) If anyone has any idea how I could possibly automate this I'd be very grateful. thanks very much for any suggestions. regards. +-------------------------------------------------------------------+ |Filename: Example_Transpose.zip | |Download: http://www.excelforum.com/attachment.php?postid=4426 | +-------------------------------------------------------------------+ -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=519801 |
Transpose!!
test post
-- Anne Murray "saybut" wrote: Hi, I have quite a tricky problem.. (I've attached a sheet as an example, I'll explain it further down) I have a list of names in column a (a very large amount), there are lots of duplicate names, and next to them in column b are the categories associated with each name. So if a name is repeated 5 times in column a, it will have 5 different categories. I need to summarize it so that I only have 1 name per person, but all of the categories associated with the person going along in each column as per the sheet attached. I've been trying to figure it out but can't do it (except manual paste specialtranspose which would take me weeks!) If anyone has any idea how I could possibly automate this I'd be very grateful. thanks very much for any suggestions. regards. +-------------------------------------------------------------------+ |Filename: Example_Transpose.zip | |Download: http://www.excelforum.com/attachment.php?postid=4426 | +-------------------------------------------------------------------+ -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=519801 |
Transpose!!
create two new columns having the following formulas:
Column C: =IF(A2=A1,CONCATENATE(B2,", ",C1),B2) Column D: =IF(A2=A3,1,0) (This is assuming that A is your "names" column, and B is your "Categories" column, and that Row 1 contains column names or is blank) After you have filled down, sort the spreadsheet by column D (so all the zeroes are first) This should arrange things so the upper portion of you spreadsheet contains the combined categories for every name, and each name is listed only once. -- Anne Murray "saybut" wrote: Hi, I have quite a tricky problem.. (I've attached a sheet as an example, I'll explain it further down) I have a list of names in column a (a very large amount), there are lots of duplicate names, and next to them in column b are the categories associated with each name. So if a name is repeated 5 times in column a, it will have 5 different categories. I need to summarize it so that I only have 1 name per person, but all of the categories associated with the person going along in each column as per the sheet attached. I've been trying to figure it out but can't do it (except manual paste specialtranspose which would take me weeks!) If anyone has any idea how I could possibly automate this I'd be very grateful. thanks very much for any suggestions. regards. +-------------------------------------------------------------------+ |Filename: Example_Transpose.zip | |Download: http://www.excelforum.com/attachment.php?postid=4426 | +-------------------------------------------------------------------+ -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=519801 |
Transpose!!
Hi,
Try this - input from "Sheet1" to output "Sheet2" Sub a() Dim ws1 As Worksheet, ws2 As Worksheet Dim lastrow As Long, r As Long, n As Integer Dim rnga As Range r = 2 Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") ws2.Range("a1:f1") = Array("E-Mail", "Category", "Category", "Category", "Category", "Category") With ws1 lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row Set rnga = ws1.Range("a2:a" & lastrow) Do n = Application.CountIf(rnga, .Cells(r, "A")) .Cells(r, "A").Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2) .Cells(r, "B").Resize(n, 1).Copy ws2.Cells(ws2.Cells(Rows.Count, "A").End(xlUp).Row, "B").Resize(1, n) _ .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True r = r + n Loop Until r lastrow End With End Sub "saybut" wrote: Hi, I have quite a tricky problem.. (I've attached a sheet as an example, I'll explain it further down) I have a list of names in column a (a very large amount), there are lots of duplicate names, and next to them in column b are the categories associated with each name. So if a name is repeated 5 times in column a, it will have 5 different categories. I need to summarize it so that I only have 1 name per person, but all of the categories associated with the person going along in each column as per the sheet attached. I've been trying to figure it out but can't do it (except manual paste specialtranspose which would take me weeks!) If anyone has any idea how I could possibly automate this I'd be very grateful. thanks very much for any suggestions. regards. +-------------------------------------------------------------------+ |Filename: Example_Transpose.zip | |Download: http://www.excelforum.com/attachment.php?postid=4426 | +-------------------------------------------------------------------+ -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=519801 |
Transpose!!
Hi, thank you so much for your replies, they're brilliant, thanks a lot. I've ended up using yours Anne just for the simplicity and the speed. Thanks very much again for helping me, I really appreciate it. Regards, Mark. -- saybut ------------------------------------------------------------------------ saybut's Profile: http://www.excelforum.com/member.php...fo&userid=5949 View this thread: http://www.excelforum.com/showthread...hreadid=519801 |
All times are GMT +1. The time now is 03:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com