How to Pivot Rows with Duplicate Values

I know this will probably seem elementary to the more advanced crowd here, I'v written my fair share of VBA code and macros

but I can't seem to get this to work.

I need this:

         A                 B

I would suggest that the answer is in your question's title - use a pivot table.

The first step would be to create a new column (column C) which provides a counter for the duplicate values in column A.

Then generate a pivot table off columns A:C, with A as row headings, C as column headings and B as data.  Probably hide the 'grand totals' too - right click->table options-> uncheck "Grand totals for rows"/"columns"

The formula you use in C could take one of two forms, if the duplicate values in column A are always sequential as in your example, you could use:

C2:     =if($A2=$A1,C1+1,1)

which simply increments on the value in the above cell if A has the same value.

But if the duplicate values in Column A may be distributed amongst the whole list (i.e. unsorted) then a more complex (and computationally costly) expression like this can be used:

C2:     =countif($A$2:$A2,$A2)

which when filled down the list might make more sense.  It counts all occurences of value in column A on the current row above (and including) the current row. e.g. filled down to C10:

C10:     =countif($A$2:$A10,$A10)

Hi Rick

thanks a heap, I applied your recommendation and it worked.

I initially tried using the Pivot Table feature in Excel but couldn't get the rows/data to pivot correctly so I attempted to write my own code and that became even more daunting, keep it simple is always the best solution.

thanks again.