Transpose Arrays with variable row counts

[bmac184]

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??

 

[Dave Peterson]

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).EntireRow.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

 

[Max]

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))),"",TRANSPOSE(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

 

[Max]

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

 

[Domenic]

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!