Selective transpose in Excel through VBA

**cbase** · 11-04-2007, 04:19 PM

XXXX 0.00 0.25 0.50 0.75 1.00 1.25 1.50
10.00 65 101 102 103 104 105 106
9.75 65 65
9.50 65 65 65
9.25 65 65 65 65
9.00 65 65 65 65 65
8.75 65 65 65 65 65 65
8.50 65 65 65 65 65 65 65
8.25 65 65 65 65 65 65 65
8.00 65 65 65 65 65 65 65

Above is a sample of data and I need to get it into the fromat below, including blank spaces.
The left column are the contents of a field names Sphere, the top row are names not values and the intersect is the field named Blank.

Although it is possible to do this manually I will have around 3 million items of data over several hundred excel sheets.

One other complication is that the left column may be up to a number from +16 to -16 in .25 steps and the top row will go from 0.00 to 32 in .25 steps.
Hence the sheets are of a variable size but will always start in A1

Sphere Cyl Blank
10 0 65
10 0.25
10 0.5
9.75 0 65
9.75 0.25 65
9.75 0.5
9.5 0 65
9.5 0.25 65
9.5 0.5 65

I would appreciate any help on this as my vba is limited.
Thanks in advance
Don

**Tarball** · 11-04-2007, 04:34 PM

I dont quite follow how you are transposing your data. Would you elaborate on the rules for consolidation of your data?

**shg** · 11-04-2007, 05:47 PM

I think the attachment may do as you wish.

Sample input:

Please Login or Register  to view this content.

Sample output:

Please Login or Register  to view this content.

**cbase** · 11-05-2007, 02:16 AM

My heartfelt thanks for this excellent example.
It will do exacly what is required and will save many hundreds of hours of work.
Just to elaborate a little on the further use of the data, it is the core information of spectacle lenses from any manufacturer. Sphere, Cyl are measurments from lens charts and Blank is the size that the lens is available in (for that specific lens). The data is derived from paper charts and after excel conversion and other details added such as lens name, id etc the data is imported to an Access file format whereupon it is used in conjunction with a patient file, frames file and many others.

A cursory glance at the code indicates a number of techniques I am not famiar with but I am sure that stepping through will throw enlightenment upon this.
Many thanks for all of your help.

Don

**shg** · 11-05-2007, 10:58 AM

You're welcome, Don.

Your response also answers my unasked question--why go from the dense representation of a table to the less efficient storage of a list: Access, of course.

Selective transpose in Excel through VBA

LinkBack

Thread Tools

Rate This Thread

Display

Selective transpose in Excel through VBA

Many thanks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions