Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Transpose Arrays with variable row counts
I have a list that I need to transpose and can't figure out how to accomplish it. Spreadsheet looks like this:
Column A Column B AA Apple AA Pear BB Orange BB Pineapple BB Strawberry CC Orange and I want to transpose it to be: Column A Column B Column C Column D AA Apple Pear BB Orange Pineapple Strawberry CC Orange Can anyone help?? |
#2
|
|||
|
|||
This macro worked ok for me with the data starting in row 1.
Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim TopRow As Long Dim iRow As Long Dim oCol As Long Set wks = Worksheets("sheet1") With wks TopRow = 1 'toprow of grouping FirstRow = TopRow + 1 'leave row 1 alone! LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row oCol = 2 For iRow = FirstRow To LastRow If .Cells(TopRow, "A").Value = .Cells(iRow, "A").Value Then oCol = oCol + 1 .Cells(TopRow, oCol).Value = .Cells(iRow, "B").Value .Cells(iRow, "B").ClearContents Else TopRow = iRow oCol = 2 End If Next iRow On Error Resume Next .Columns(2).Cells.SpecialCells(xlCellTypeBlanks).E ntireRow.Delete On Error GoTo 0 End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm bmac184 wrote: I have a list that I need to transpose and can't figure out how to accomplish it. Spreadsheet looks like this: Column A Column B AA Apple AA Pear BB Orange BB Pineapple BB Strawberry CC Orange and I want to transpose it to be: Column A Column B Column C Column D AA Apple Pear BB Orange Pineapple Strawberry CC Orange Can anyone help?? -- bmac184 -- Dave Peterson |
#3
|
|||
|
|||
Here's a formulas play which seems to be able to drive out the desired
results as well (Link to a sample file is provided below) In Sheet1: Source data is assumed in A2:B7 in Sheet1 Put in C2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) Copy down to say, C10 Put in D1: =IF(ISERROR(SMALL($C:$C,COLUMNS($A$1:A1))),"",TRAN SPOSE(INDEX($A:$A,MATCH(SM ALL($C:$C,COLUMNS($A$1:A1)),$C:$C,0)))) Copy across to say, H1 In D1:H1 will be extracted the unique items in col A, i.e.: AA, BB, CC Put in D2: =IF(D$1="","",IF($A2=D$1,ROW(),"")) Copy across to H2, fill down to H10 to populate the grid In Sheet2: Put in A2: =OFFSET(Sheet1!$C$1,,ROWS($A$1:A1)) Copy down to A6 (I.e. by as many rows as the # of cols in D1:H1 in Sheet1) This transposes the list of unique items from D1:H1 in Sheet1 into A2:A6 Put in B2: =IF(ISERROR(SMALL(OFFSET(Sheet1!$C$2:$C$10,0,MATCH ($A2,Sheet1!$C$1:$H$1,0)-1 ),COLUMNS($A$1:A1))),"",TRANSPOSE(INDEX(Sheet1!$B$ 2:$B$10,MATCH(SMALL(OFFSET (Sheet1!$C$2:$C$10,0,MATCH($A2,Sheet1!$C$1:$H$1,0)-1),COLUMNS($A$1:A1)),OFFS ET(Sheet1!$C$2:$C$10,0,MATCH($A2,Sheet1!$C$1:$H$1, 0)-1),0),))) Copy B2 across to say, G2, fill down to G6 [copy across as many cols as there are items per unique to be extracted, i.e. Apple, Pear, etc] In A2:G6 will be returned the desired results Sample file with the implemented construct: http://flypicture.com/p.cfm?id=62362 (Right-click on the link: "Download File" at the top in the page, just above the ads) File: bmac184_wksht_1.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "bmac184" wrote in message ... I have a list that I need to transpose and can't figure out how to accomplish it. Spreadsheet looks like this: Column A Column B AA Apple AA Pear BB Orange BB Pineapple BB Strawberry CC Orange and I want to transpose it to be: Column A Column B Column C Column D AA Apple Pear BB Orange Pineapple Strawberry CC Orange Can anyone help?? -- bmac184 |
#4
|
|||
|
|||
Think the site you're posting from / reading these responses --
ExcelBanter? -- unfortunately removes all the "greater than", "less than" or "not equal to" symbols/operators from posts/responses, including those within formulas (which poses real problems to ExcelBanter users, I'd figure <g) Note that in the line: Put in C2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) there's a "greater than" symbol between "COUNTIF($A$2:A2,A2)" and "1" (the "greater than" symbol won't appear in ExcelBanter) Alternatively, just see the sample file [link to d/l provided earlier] To read the post / response in google w/o any distortions: http://tinyurl.com/c6eqa -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
Here's another way...
Assumptions: A1:B6 contains your source data D1: AA D2: BB D3: CC Formula: E1, copied across and down: =IF(COLUMN()-COLUMN($E1)+1<=COUNTIF($A$1:$A$6,$D1),INDEX($B$1:$ B$6,SMALL( IF($A$1:$A$6=$D1,ROW($A$1:$A$6)-ROW($A$1)+1),COLUMNS($E1:E1))),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , bmac184 wrote: I have a list that I need to transpose and can't figure out how to accomplish it. Spreadsheet looks like this: Column A Column B AA Apple AA Pear BB Orange BB Pineapple BB Strawberry CC Orange and I want to transpose it to be: Column A Column B Column C Column D AA Apple Pear BB Orange Pineapple Strawberry CC Orange Can anyone help?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Arrays | Excel Discussion (Misc queries) | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
I Need VBA Assistance for global variable question | Excel Discussion (Misc queries) | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
TRANSPOSE() | Excel Worksheet Functions |