Variable Cell Refence Problem

[hankbug]

1st Post. Thanks ahead to everyone.

I am having difficulty with a cell reference for a TRANSPOSE function where the 2nd reference has a variable row number. I have done a fair amount of googling with no answer.

For my problem, I have a workbook like the following, sorted by column B, then, column A.

. A B C D E
1 2 01
2 3 01
3 2 05
4 9 05
5 12 05

I need to transpose the data in collumn a across one row. for this example, lets say row 1. The starting cell for the transpose is known (in this case C2), but the ending row is variable and based on the the uniqueness of the data in column B. In my example, i would want to TRANSPOSE from A1 to A3 across row 1 beginning in column C. The next transpose would be from A3 to A5 across row 3 beginning in column C. So, I want the following:

. A B C D E
1 2 01 2 3
2 3 01
3 2 05 2 9 12
4 9 05
5 12 05

I am not concerned about the data in row 2, 4 or 5 because I plan to hide all rows where only each unique value in row B shows.

My problem is I am having trouble with the 2nd reference in the TRANSPOSE function I want to use because the row part of the reference is variable.

In cell C1, I want

=TRANSPOSE(A1:AX)
where X=1+(COUNTIF(A:A,A1)-1

Of course I can not use =TRANSPOSE(A1:A(1+(COUNTIF(A:A,A1)-1)), as it results in an error. So, how does one define a reference that is variable? If it just needed the value of a cell whose reference was variable, I could just use INDIRECT, but that doesn't apply here.

Thank you for your assistance. Please excuse my lack of knowledge.

[NBVC]

Maybe you mean?

=TRANSPOSE(A1:INDEX(A:A,1+(COUNTIF(A:A,A1)-1)))