Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Don't hesitated to say this is too much for a forum question....
I am attempting to place a series of values from a column into rows, but the details of this task are much more than typical transpose. I have played with formulas, MATCH, OFFSET, etc. and got tangled up. That still my be good way to go. Here bleow is DATA is in COL A and B. Output starts in COL C and continues across as many columns as needed. When values repeat in A, transpose the values from COL B for that repeated COL A value starting at COL C through possibly 100 columns, i.e. COL A value could repeat 100 times. It is important to note that in a grouping of repeaded rows (defined by repeating value in COL A), the resulting transposed values from COL B repeat down for each repeated row. This is why all the rows of data for the f's are the same-- this is intentional. In other words, transpose all the values in COL B for a group of repeating values in A, and transpose across the row containing first value of COL A, the fill down identically for all COL A group. Why am I attempting this? It is to identify identical groupings of values, e.g. somewhere else in the list there will be another group of rows, say 4 rows with "z" in COl A, and those four rows have the same values as found in COL B, therefore one can say group f and group z are identical-- a list of identical groups is the objective. There may be several identical groupings, not just two. ALternatives are welcome. (In my soloution I expect to concatenate the values in COL C... and sort on that concatenation, then subtotal/count.) COL A COLB COL C COL D COL E COL F COL G a kk kk b mm mm c mm mm d nn nn pp d pp nn pp e qq qq f rr rr ss tt uu f ss rr ss tt uu f tt rr ss tt uu f uu rr ss tt uu g xx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
I think this macro will do what you want (set the DataStartRow constant as
required)... Sub TransposeDuplicates() Dim X As Long, StartRow As Long, LastRow As Long, HowMany As Long Const DataStartRow As Long = 2 LastRow = Cells(Rows.Count, "A").End(xlUp).Row HowMany = 1 For X = DataStartRow To LastRow If HowMany = 1 Then StartRow = X Do While Cells(X + HowMany - 1, "A").Value = _ Cells(X + HowMany, "A").Value HowMany = HowMany + 1 Loop End If Cells(X, "C").Resize(, HowMany) = WorksheetFunction.Transpose( _ Cells(StartRow, "B").Resize(HowMany)) If X = StartRow + HowMany - 1 Then HowMany = 1 Next End Sub -- Rick (MVP - Excel) "KIM W" wrote in message ... Don't hesitated to say this is too much for a forum question.... I am attempting to place a series of values from a column into rows, but the details of this task are much more than typical transpose. I have played with formulas, MATCH, OFFSET, etc. and got tangled up. That still my be good way to go. Here bleow is DATA is in COL A and B. Output starts in COL C and continues across as many columns as needed. When values repeat in A, transpose the values from COL B for that repeated COL A value starting at COL C through possibly 100 columns, i.e. COL A value could repeat 100 times. It is important to note that in a grouping of repeaded rows (defined by repeating value in COL A), the resulting transposed values from COL B repeat down for each repeated row. This is why all the rows of data for the f's are the same-- this is intentional. In other words, transpose all the values in COL B for a group of repeating values in A, and transpose across the row containing first value of COL A, the fill down identically for all COL A group. Why am I attempting this? It is to identify identical groupings of values, e.g. somewhere else in the list there will be another group of rows, say 4 rows with "z" in COl A, and those four rows have the same values as found in COL B, therefore one can say group f and group z are identical-- a list of identical groups is the objective. There may be several identical groupings, not just two. ALternatives are welcome. (In my soloution I expect to concatenate the values in COL C... and sort on that concatenation, then subtotal/count.) COL A COLB COL C COL D COL E COL F COL G a kk kk b mm mm c mm mm d nn nn pp d pp nn pp e qq qq f rr rr ss tt uu f ss rr ss tt uu f tt rr ss tt uu f uu rr ss tt uu g xx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Hi
I think this is what you need, just remember that a heading is required in A1: Sub MyTranspose() Dim FilterRange As Range Dim DataRange As Range Dim UniqueArray() 'For filter purpose a Heading is needed in A1 LastRow = Range("A1").End(xlDown).Row Application.ScreenUpdating = False Set FilterRange = Range("A1:A" & LastRow) Set DataRange = Range("B2:B" & LastRow) FilterRange.AdvancedFilter Action:=xlFilterInPlace, Unique:=True UniqueVal = Array(FilterRange.SpecialCells(xlCellTypeVisible)) ReDim UniqueArray(FilterRange.SpecialCells(xlCellTypeVis ible).Count - 1) For Each cell In FilterRange.SpecialCells(xlCellTypeVisible) UniqueArray(c) = cell.Value c = c + 1 Next ActiveSheet.ShowAllData For c = 1 To UBound(UniqueArray) FilterRange.AutoFilter Field:=1, Criteria1:=UniqueArray(c) DataRange.SpecialCells(xlCellTypeVisible).Copy DataRange.SpecialCells(xlCellTypeVisible).Offset(0 , 1).PasteSpecial _ Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Next FilterRange.AutoFilter Application.ScreenUpdating = True End Sub Regards, Per "KIM W" skrev i meddelelsen ... Don't hesitated to say this is too much for a forum question.... I am attempting to place a series of values from a column into rows, but the details of this task are much more than typical transpose. I have played with formulas, MATCH, OFFSET, etc. and got tangled up. That still my be good way to go. Here bleow is DATA is in COL A and B. Output starts in COL C and continues across as many columns as needed. When values repeat in A, transpose the values from COL B for that repeated COL A value starting at COL C through possibly 100 columns, i.e. COL A value could repeat 100 times. It is important to note that in a grouping of repeaded rows (defined by repeating value in COL A), the resulting transposed values from COL B repeat down for each repeated row. This is why all the rows of data for the f's are the same-- this is intentional. In other words, transpose all the values in COL B for a group of repeating values in A, and transpose across the row containing first value of COL A, the fill down identically for all COL A group. Why am I attempting this? It is to identify identical groupings of values, e.g. somewhere else in the list there will be another group of rows, say 4 rows with "z" in COl A, and those four rows have the same values as found in COL B, therefore one can say group f and group z are identical-- a list of identical groups is the objective. There may be several identical groupings, not just two. ALternatives are welcome. (In my soloution I expect to concatenate the values in COL C... and sort on that concatenation, then subtotal/count.) COL A COLB COL C COL D COL E COL F COL G a kk kk b mm mm c mm mm d nn nn pp d pp nn pp e qq qq f rr rr ss tt uu f ss rr ss tt uu f tt rr ss tt uu f uu rr ss tt uu g xx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Just so you don't confuse requirements, I wanted to point out that Per's
solution and mine are totally different... with mine, the DataStartRow can be in Row 1, in case you need that, and it can just as easily be Row 100 as the code will adjust around it automatically (I just used an example DataStartRow of 2 making the assumption that your data might have a header row). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I think this macro will do what you want (set the DataStartRow constant as required)... Sub TransposeDuplicates() Dim X As Long, StartRow As Long, LastRow As Long, HowMany As Long Const DataStartRow As Long = 2 LastRow = Cells(Rows.Count, "A").End(xlUp).Row HowMany = 1 For X = DataStartRow To LastRow If HowMany = 1 Then StartRow = X Do While Cells(X + HowMany - 1, "A").Value = _ Cells(X + HowMany, "A").Value HowMany = HowMany + 1 Loop End If Cells(X, "C").Resize(, HowMany) = WorksheetFunction.Transpose( _ Cells(StartRow, "B").Resize(HowMany)) If X = StartRow + HowMany - 1 Then HowMany = 1 Next End Sub -- Rick (MVP - Excel) "KIM W" wrote in message ... Don't hesitated to say this is too much for a forum question.... I am attempting to place a series of values from a column into rows, but the details of this task are much more than typical transpose. I have played with formulas, MATCH, OFFSET, etc. and got tangled up. That still my be good way to go. Here bleow is DATA is in COL A and B. Output starts in COL C and continues across as many columns as needed. When values repeat in A, transpose the values from COL B for that repeated COL A value starting at COL C through possibly 100 columns, i.e. COL A value could repeat 100 times. It is important to note that in a grouping of repeaded rows (defined by repeating value in COL A), the resulting transposed values from COL B repeat down for each repeated row. This is why all the rows of data for the f's are the same-- this is intentional. In other words, transpose all the values in COL B for a group of repeating values in A, and transpose across the row containing first value of COL A, the fill down identically for all COL A group. Why am I attempting this? It is to identify identical groupings of values, e.g. somewhere else in the list there will be another group of rows, say 4 rows with "z" in COl A, and those four rows have the same values as found in COL B, therefore one can say group f and group z are identical-- a list of identical groups is the objective. There may be several identical groupings, not just two. ALternatives are welcome. (In my soloution I expect to concatenate the values in COL C... and sort on that concatenation, then subtotal/count.) COL A COLB COL C COL D COL E COL F COL G a kk kk b mm mm c mm mm d nn nn pp d pp nn pp e qq qq f rr rr ss tt uu f ss rr ss tt uu f tt rr ss tt uu f uu rr ss tt uu g xx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Excel 2007, Table
Count unique repeated row sequences, list in columns and tag with CF. Alternate method, no code. http://www.mediafire.com/file/mzztyn...01_31_10a.xlsx |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Thanks to all! I will be applying and adapting these in the next couple of
days. "KIM W" wrote: Don't hesitated to say this is too much for a forum question.... I am attempting to place a series of values from a column into rows, but the details of this task are much more than typical transpose. I have played with formulas, MATCH, OFFSET, etc. and got tangled up. That still my be good way to go. Here bleow is DATA is in COL A and B. Output starts in COL C and continues across as many columns as needed. When values repeat in A, transpose the values from COL B for that repeated COL A value starting at COL C through possibly 100 columns, i.e. COL A value could repeat 100 times. It is important to note that in a grouping of repeaded rows (defined by repeating value in COL A), the resulting transposed values from COL B repeat down for each repeated row. This is why all the rows of data for the f's are the same-- this is intentional. In other words, transpose all the values in COL B for a group of repeating values in A, and transpose across the row containing first value of COL A, the fill down identically for all COL A group. Why am I attempting this? It is to identify identical groupings of values, e.g. somewhere else in the list there will be another group of rows, say 4 rows with "z" in COl A, and those four rows have the same values as found in COL B, therefore one can say group f and group z are identical-- a list of identical groups is the objective. There may be several identical groupings, not just two. ALternatives are welcome. (In my soloution I expect to concatenate the values in COL C... and sort on that concatenation, then subtotal/count.) COL A COLB COL C COL D COL E COL F COL G a kk kk b mm mm c mm mm d nn nn pp d pp nn pp e qq qq f rr rr ss tt uu f ss rr ss tt uu f tt rr ss tt uu f uu rr ss tt uu g xx |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Excel 2007, Table
Count unique repeated row sequences. Added Rick's great macro for your convenience. http://c0444202.cdn.cloudfiles.racks...01_31_10a.xlsm |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced transpose/grouping question
Thank you for you very nice compliment... I appreciated it very much.
-- Rick (MVP - Excel) "Herbert Seidenberg" wrote in message ... Excel 2007, Table Count unique repeated row sequences. Added Rick's great macro for your convenience. http://c0444202.cdn.cloudfiles.racks...01_31_10a.xlsm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced transpose (columns to rows) function? | Excel Discussion (Misc queries) | |||
Advanced transpose (columns to rows)? | Excel Discussion (Misc queries) | |||
advanced paste special transpose | Excel Discussion (Misc queries) | |||
Transpose Question | Excel Worksheet Functions | |||
Transpose Question | Excel Programming |