Moving Rows to Columns

[rhondaly]

I created a report that has all of my deduction codes listed in rows by employee ID. I would like to move each deduction code to its own column at the top. Is there anyway this can be done without manually moving each row to the top and inserting the amounts under the column? I have attached a sample.

[judgeh59]

Does this get you close? I had to delete the 1st sheet because of file size

[Pete_UK]

Insert a new column C and put this formula in C2:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

Then copy it down to the bottom of your list by double-clicking the fill handle (the small black square in the bottom right corner of the cursor, with C2 selected).

Then you need to obtain a unique list of your codes, so highlight all the entries in column A (including the header), then use Advanced Filter - ensure that the range shown in the dialogue box is correct (A1:A3647), click against Copy to Another Location, put J1 in the Copy To box, then click Unique Records only, then click OK. You should see your codes listed in column J, down to J22. Copy the range J2:J22, move the cursor to K1, then right-click and choose Paste Special | Values | Transpose | OK then press the <Esc> key - this should have put your unique codes on the top row, starting in K1.

Then put this formula in K2:

=IF(ISNA(MATCH(K$1&"_"&ROWS($1:1),$C:$C,0)),"",INDEX($B:$B,MATCH(K$1&"_"&ROWS($1:1),$C:$C,0)))

and copy this across to AE2 (i.e. under the W). Then you can copy K2:AE2 down as far as you need to. You could put this formula in I2:

=COUNTIF(A:A,J2)

and copy it down, to tell you how many entries you need for each code.

Then you can delete columns D to J.

Hope this helps - I've assumed that your profile entry showing XL 2003 is correct.

Pete

[rhondaly]

Thank you so much judgeH!! The formual works but only for the first cell of each column. When I try to copy down it gives me the green triangle in the corner but no number prefills in the space? What am I doing wrong?

[judgeh59]

more than likely it's a cell referencing issue....looking at the $ and where they are placed....it worked in the file I uploaded....you may have to really check the cell references and make sure they are correct when you copied the formulas....

[rhondaly]

I got it. It was definitely me :-). Thank you so much.

***************

[judgeh59]

Please remember to set the thread to Solved...If you feel I deserve an increase in my reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be me...just a thought...