Transposing Data in rows to columns (complex)

[Colin T]

I have an issue that I need to resolve.
I'm currently getting data in a row format, but I need to convert some of the data into columns, for importing into Access.
For example......

Item | Description | Jan 05 | Feb 05 | Mar 05 | Apr 05 | ...........
ABC123 | Widget A | 100 | 250 | 175 | 210 |............
XYZ123 | Widget B | 50 | 75 |100 | 76 |............

To convert to........


Column1 Column2 Column3 Column4
Item | Description | Date | Qty
ABC123 | Widget A | Jan 05 | 100
ABC123 | Widget A | Feb 05 | 250
ABC123 | Widget A | Mar 05 | 175
ABC123 | Widget A | Apr 05 | 210
: : : :
: : : :
XYZ123 | Widget B | Jan 05 | 50
XYZ123 | Widget B | Feb 05 | 75
XYZ123 | Widget B | Mar 05 | 100
XYZ123 | Widget B | Apr 05 | 76
: : : :
: : : :

All data is in individual cells (tried to show this with the pipe character).

I need to have this conversion automated as the files are pretty large.
There are several different files with different requirements for how many cells get transposed, therefore any solution needs to be flexible (easily rescripted by a novice to excel programming like myself).
Notice that I need to copy the item and description for each row element I transpose.

Can anyone help?

Thanks.

Colin.

[Vacation's Over]

Selection.Copy
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

"Colin T" wrote:

>
> I have an issue that I need to resolve.
> I'm currently getting data in a row format, but I need to convert some
> of the data into columns, for importing into Access.
> For example......
>
> Item | Description | Jan 05 | Feb 05 | Mar 05 | Apr 05 |
> ...........
> ABC123 | Widget A | 100 | 250 | 175 | 210
> |............
> XYZ123 | Widget B | 50 | 75 |100 | 76
> |............
>
> To convert to........
>
>
> Column1 Column2 Column3 Column4
> Item | Description | Date | Qty
> ABC123 | Widget A | Jan 05 | 100
> ABC123 | Widget A | Feb 05 | 250
> ABC123 | Widget A | Mar 05 | 175
> ABC123 | Widget A | Apr 05 | 210
> : : : :
> : : : :
> XYZ123 | Widget B | Jan 05 | 50
> XYZ123 | Widget B | Feb 05 | 75
> XYZ123 | Widget B | Mar 05 | 100
> XYZ123 | Widget B | Apr 05 | 76
> : : : :
> : : : :
>
> All data is in individual cells (tried to show this with the pipe
> character).
>
> I need to have this conversion automated as the files are pretty
> large.
> There are several different files with different requirements for how
> many cells get transposed, therefore any solution needs to be flexible
> (easily rescripted by a novice to excel programming like myself).
> Notice that I need to copy the item and description for each row
> element I transpose.
>
> Can anyone help?
>
> Thanks.
>
> Colin.
>
>
> --
> Colin T
> ------------------------------------------------------------------------
> Colin T's Profile: http://www.excelforum.com/member.php...o&userid=26294
> View this thread: http://www.excelforum.com/showthread...hreadid=395894
>
>