Custom formatting using either Macro or VB script

Hi,

Ok, what I am trying to do is this. I have a report at that I am trying to extract information from that can be copied and pasted into another which already has a VLOOKUP function implemented to reference data within a certain format.

The report that has the source information is in a different format going down columns with multiple duplicate entries in various cells down the columns.

I have a macro that seperates out the data from the report that I ened but I am having trouble getting the data to format the way I need automatically. I have tried using a Copy>Paste Special> Transpose but it causes the data to not format correctly. Transpose is the function that I believe I need because the format goes from left to right instead of down the page on the report that needs the ference data.

I believe I could do this in VB by setting up a script that would say something a long the lines of this psuedo code:

Index Column (select column)
If Value, transpose to cell to right
Else, leave blank

Any ideas on how I could accomplish this?