Help transforming N Columns to M with a twist...

Hi,

For the past few days I've been trying to solve a problem that, now, I recognize to be well beyond my ability....
I've searched the forum for solutions...and I couldn't find anything that solved my problem.

So, I have a bunch of data data organized in N columns. The first few columns (call it X columns), contain reference data and the last few (call it Y columns) have answers to a survey (they are all numeric scores).

If I identifiy the Y columns as Y(1), Y(2), Y(3)...Y(n)
I need to reorganize this table to get:
The first X columns (reference data)+ 1 column containing the label of Y(0) in all rows + 1 column containg the values of Y(0)
Repeat and append data in the same format for Y(1)...Y(N).

I'm attaching an example to make this clear.
example.xlsx

If anyone could help me with a macro that does this or point to somewhere where I can find it, I would be forever grateful as I'm facing quite a daunting task if I'm going to do this by hand...

Thanks a lot for your time and attention.
miguel

option, press "Run" button

Originally Posted by bonny24tycoon

Hi there,

So would the real data also have 4 X's and 4 Y's?? Also would it always have 6 lines of survey for ID1's??

Hi,

Thanks for your reply...This was just an example.
I hope then I'm able to change the code to the real number..

cheers,
Miguel

Originally Posted by Richard Buttrey

Hi,

See the attached which uses the following macro.

Hi Richard,

Thanks a lot!
This is exctly what i need!

I've tested it with more "Score" columns and it also works!

I would only ask you where do I need to change if I want to add more "X columns" (reference data).
I'm trying to figure it out...but my programming abilities are low and I don't want to break it.

thanks a lot!

miguel

Hi Watersev,

Thanks a lot!

it also works great.

I would also have the same question as for richard, where can I change it when I have mopre columns "X" (reference data)?

thanks again!

miguel

Hi Watersev,

Thanks a lot!

it also works great.

I would also have the same question as for richard, where can I change it when I have mopre columns "X" (reference data)?

thanks again!

miguel

Originally Posted by tonelot

Hi Richard,

Thanks a lot!
This is exctly what i need!

I've tested it with more "Score" columns and it also works!

I would only ask you where do I need to change if I want to add more "X columns" (reference data).
I'm trying to figure it out...but my programming abilities are low and I don't want to break it.

thanks a lot!

miguel

Hi Richard,

I Was trying to change the number of "X" columns...but it seems that that number is hardcoded in a few places and it's breaking.
Wouldn't it be possible to define a variable in the beginning of the macro that would hold the number of columns with reference data an then this would be used in the code?

cheers
miguel

Originally Posted by watersev

option, press "Run" button

Hi watersev,

Actually I've been able to modify your macro to have a variable handling a differente number of coumns.
and it seems to work!

Now I just have a problem with the column headers being harcoded in the macro, as I have several files to treat and they don't hold all the same columns. (this data from an internal survey).

is tehere a way to make the macro grab the column headers dynamic?
My alternative is to copy the macro for as many versions as I have to treat...which is kind of risky.

Thanks a lot for your help.

cheers,
miguel