transpose X matching headers X duplicate tables (multiple problems)

Hi Guys,

For below PLEASE no macro's! I'm not allowed to use them at work

I got a very, very tricky (for me at least) problem.

I wrote it very detailed but its rly not complicated and from the excel its extremely easy to see what the problem/solution is!

There are 3 things I need to do:

1. Transpose 10+ columns
2. Take headers or 10+ columns and put beside values in #1
3. Copy down all rows/columns before #1 and #2

Essentially 18 columns will become 10.

The first tab of the attached is called "original" which is the problem.

Please note:

1. Yellow highlights will go in the same column (please disregard the header "values" I was just was showing where it goes)
2. Orange highlights will go in the same column(please disregard the header "attributes" I was just was showing where it goes)
3. Green highlights will just duplicate is direct relation with #1 and #2
4. Headers not highlighted and bolded do not move (with the inclusion of "values" and "attributes")

The second tab labelled "solution" shows this.

I was going to use =INDEX($B$8:$D$12,1+INT((ROW(A6)-1)/COLUMNS($B$8:$D$12)),MOD(ROW(A6)-1+COLUMNS($B$8:$D$12),COLUMNS($B$8:$D$12))+1) BUT that only takes care of the yellow and even if i copy the orange beside and do the formula twice is doesn't affect the green.

Plus the data attached is small, the real data is 1000's of lines so the above would still take an hour or hours!

Thank you!!

Try in I2:

Formula:

Please Login or Register  to view this content.

And in J2:

Formula:

Please Login or Register  to view this content.

Copy down.

Happy New Year!

NO WAY!

you are AMAZING!

Two Questions:

1. why is I2 "='Original Data'!K1" and the rest countif's?
2. V7 and V8 are getting their values from V1 & V2 from original data. Not sure why but it seems after V6 the data starts getting the values from the beg?

this is insane how good it is.

I just posted another problem on the thread if you want to take a look :D

Also is there a way I can click something and it does it AUTO, since if there are 50 rows and 20 columns it might be hard to drag down 50*20 times perfectly, just as an example

I cant get it to work past row 80 on larger data, also not sure how to ensure the entire data is completely transposed since there is ALOT of cells and values

See excel

Hi,
In I2 and down you can use this instead, so the formula will be uniform:
=INDEX('Original Data'!$K$1:$R$1,,ROUNDUP(MOD((ROW()-1.1)/COUNTA('Original Data'!$K$2:$K$7),COUNTA('Original Data'!$K$1:$R$1)),0))

2. which column are you referring to? not sure what you mean...

Originally Posted by JPRICH16

I cant get it to work past row 80 on larger data.....See excel

Oh I see you took Estevaoba's solution eventualy ,
I thought by your 1st question - that you referenced to my file ...so will let him answer your questions...

Originally Posted by belinda200

Hi,
In I2 and down you can use this instead, so the formula will be uniform:
=INDEX('Original Data'!$K$1:$R$1,,ROUNDUP(MOD((ROW()-1.1)/COUNTA('Original Data'!$K$2:$K$7),COUNTA('Original Data'!$K$1:$R$1)),0))

2. which column are you referring to? not sure what you mean...

Thanks!

Last question!

Since there is over 12000 data points which is multiplied by the # of columns, how can i do this and know i have all the data. i.e if i drag the forumlas from 12000 to row 40000 ill still miss alot?

Solution!A1:

Formula:

Please Login or Register  to view this content.

Fill right into B1:J1. Select A1:H1 and fill down into A2:H7.

Solution!A8:

Formula:

Please Login or Register  to view this content.

Fill right into B8:H8.

Solution!I2:

Formula:

Please Login or Register  to view this content.

Solution!J2:

Formula:

Please Login or Register  to view this content.

Select I2:J2 and fill down into I3:J8.

Select A8:J8, and fill down until formulas in column J produce errors.

This is specific to 8 columns of attributes and 6 rows of values. For varying number of attribute columns and value rows, BUT plain data still in 'Original Data'!A:H, better to use 1 additional cell.

Solution!AAB1:

Formula:

Please Login or Register  to view this content.

Solution!A1:

Formula:

Please Login or Register  to view this content.

Fill A1 right into B1:J1. Select A1:H1 and fill down into A2:H2.

Solution!I2:

Formula:

Please Login or Register  to view this content.

Solution!J2:

Formula:

Please Login or Register  to view this content.

Select A2:J2 and fill down until formulas in column J produce errors.

Originally Posted by JPRICH16

I cant get it to work past row 80 on larger data

See excel

Value 6 in my original formulas was for your model only, i.e. the number of rows.

Formula adjusted to your new file, in A7:

Formula:

Please Login or Register  to view this content.

And B7:

Formula:

Please Login or Register  to view this content.