Paste Special Transpose

[mimih23]

In order to pull data from an internal source, I have to provide it in a manner where the items are listed horizontally. The original data source is vertical in excel. Usually I take 100 to 200 items at a time and paste special (transpose) them into another sheet.....paste them into a text file, replace all spaces with semicolons and wala it's ready to go. Given that I have a deadline less than a week is there any way to transpose a large quantity (say 1900) at one time??? Let me know!!

 

[GlennUK]

You could use a formula, like this, to do it:

=OFFSET(Sheet1!$A$2,1+(ROW()-1)*100+COLUMN()-1,0,1,1)

entered into cell A1 of a new sheet, changing Sheet1 to whatever sheet you are fetching from. Copied across to fill 100 cells, and then copy downwards as necessary.

 

[mimih23]

You could use a formula, like this, to do it:

=OFFSET(Sheet1!$A$2,1+(ROW()-1)*100+COLUMN()-1,0,1,1)

entered into cell A1 of a new sheet, changing Sheet1 to whatever sheet you are fetching from. Copied across to fill 100 cells, and then copy downwards as necessary.

I just have to say that I love you! That worked! Thanks so much!!!
That count down hours of unnecessary stress.

 

[GlennUK]

Wow, I'm blushing ... am so glad that it's made you so happy