Tranposed results based on A column.

[pauliej]

Hello,

I was recommended this site by a friend, so im hoping someone can help me!

Hoping my title was clear, not sure exactly how to solve my little problem..

Heres what im trying to achieve, I have the following template xsl...

A 1 2 3
B 1 2
C 1 2 3 4
D 1 2 3 4 5
E 1 2 3

I want to try to turn this data with the use of formulas/macros into....

A 1
A 2
A 3
B 1
B 2
C 1....etc...


Considering that the number of entries per letter is different and so are the number themselves, i dont know where to start!

Any ideas of which formula or even macro processes to use? Perhaps I am better of import the data into a database and creating a report?

Any help would be appreciated!

Thanks!

[tigeravatar]

pauliej,

Welcome to the forum. Attached is a sample workbook based on the example data you provided. There is a button named 'Transpose Data' which is assigned to the following macro:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[pauliej]

WOAH!

Not only is that exactly what I was after that was a super quick response!

At least I was kind of right with the use of transposing data.

I have a few more thing I want to the macro to do (e.g. times all values by 100 etc) so im gonna see if I can play around with your sample.

Thanks tigeravatar.

[martindwilson]

with a helper

[pauliej]

thanks martindwilson.

Whilst tigeravatar macro is very sophisticated (to me anyway), I think your formulas may be more useful for my needs as the data is a more manipulable (or so it seems for a newb)!

[pauliej]

tigeravatar,

soz to take up more of you time....

In application of your code, if the selected range of data to transpose is A2:T37, is there a way of having the results of the transposition outside of this range, e.g. A40 onwards or even in a separate workbook?

Thanks.

[tigeravatar]

pauliej,

is there a way of having the results of the transposition outside of this range ... or even in a separate workbook?

Yes, that can be arranged. What are you looking to have done?

~tigeravatar

[pauliej]

hi tigeravatar,

so taking your workbook example above - tigeravatar example workbook for pauliej.xls..

presuming the data will always be within a range of A2:F6, I wondered if it would be possible for the transposed data to perhaps appear in a seperate sheet, whilst maintaining the data as it is in A2:F6 ?

I know i could just get the macro to copy and paste the data into a new sheet and then to transpose that data, but wondered if there was a better way!

If this is possible, then i am hoping I can cut out the process of the user having to select the range to transpose manually each time...

Thanks.

[tigeravatar]

pauliej,

Updated code. This code assumes that the data has headers. It also assumes that everything in the sheet (other than the headers) needs to be transposed. This works fine for my sample workbook since those conditions are true. Let me know if that is not the case, however. It then leaves the original data alone, creates a new sheet named "Transposed Data Results", and outputs the transposed results to the new sheet.

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar