I know this is a SQL forum, but for crazy Excel imports, I've found creating macros in VBA to be very helpful. One can move the data to a new sheet and in the format that you wish. You could start by recording a macro as you perform this manually, and looking at the VBA code generated and try hacking it from there (on a test workbook).