Excel Transpose Column to Rows Based on Criteria

http://www.excelforum.com/excel-prog...-criteria.html

Originally Posted by rylo

Hi

See how this goes.

Please Login or Register  to view this content.

rylo

Originally Posted by lilianphoebs

Hi all, I am working on a report for a healthcare organization but could not get my data into the desired format. I'm proficient with Excel but have not worked with macros before, and was wondering if there'a a quick way to learn how to build a macro to transpose column data to rows, based on complete matches in all the other fields.

The current spreadsheet looks something like this:

........A .................B ......................C .......................D
1 ..Name ..........Provider ............ApptDate ........DiagnosisCode
2 Patient, A .....Doctor, B ..........01/01/10 .............123.45
3 Patient, A .....Doctor, B ..........01/01/10 .............125.11
4 Patient, A .....Doctor, B ..........02/12/10 .............125.11
5 Patient, A .....Nurse, C ...........03/22/10 .............145.6
6 Patient, D .....Doctor, F...........01/04/10 ..............325.1

I would like to transpose Column D when ALL the other fields ( A B C ) match.

In this example, the data should come out looking like this:

.........A ...............B ...............C ..............D .................E...................F.............G...
1 ...Name .......Provider .....ApptDate... Diagnosis1 ...Diagnosis2....Diagnosis3...
2 Patient, A ...Doctor, B ....01/01/10 ......123.45 ............125.11
4 Patient, A ...Doctor, B ....02/12/10 ......125.11...
5 Patient, A ...Nurse, C .....03/22/10 ......145.6....
6 Patient, D ...Doctor, F ....01/04/10.......325.1.....

A sample worksheet with actual header names is attached.

Thank you so much for all your help!

Turns out there is a problem... The trailing zeros in the Diagnostic Codes cannot be dropped. For example a code of 250.00 will only be accepted as 250.00 or 25000, but not as 250.

The codes are stored as texts originally but the transpose process is dropping the trailing zeros. Is there a way to correct this?