Sumproduct Columns vs. Rows

[fred]

Hi, I was presented with a question with 2 database tables (which I think is poorly constructed).


Table 1

Rows 2 to 10 have some data reading horizontally. Cells A2:A10 are the headers. data going from B2 to U10


Table 2

Row 12: Headers A12 to F12

Data: A13 to F32


In the empty area of the worksheet they want to create a table 3 base on the sumproduct of table 1 and table 2.


Any idea I should approach this with verticle data computing against horizontal data?

 

[Luke M]

Possibly...but things could get hairy. If you use the TRANSPOSE function in an array formula (yes, even if it's in SUMPRODUCT, you'll need to use an array) you might be able to get what you what.


Easiest method is probably to copy and paste special - transpose one of the tables, though.

 

[SirJB7]

Hi, fred!

I've looking towards the same as Luke M. I wrote an example using the TRANSPOSE function, and it works only if both the original and transposed matrix have the same rows & columns.

That's to say (2 by 3) * tranpose (3 by 2), it works. In your case, (9 by 20) * transpose (20 by 6) doesn't work: it don't give a (9 by 6) output matrix as I think it's desired.

Trying VBA code will be the advisable path.

Regards!

 

[fred]

Thanks. I know transpose. I thought there was a more direct way to fix it. I'll probably use a helper worksheet hidden in the background to do the placement of data and calculation. Then link the answer back to the main page.


Why do people want to place their data in such an awkward way?

 

[SirJB7]

Hi, fred!

Because of the packing towards up and left directions, I think he wanted to have all (or most) data visible in the screen. I should have used one worksheet per table, and the results in another one. But what's out of discussion is the final result of the 9 by 6 matrix: if he has that data, he'll need that output.

Regards! And good luck with the strange data storing.