• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transpose 2 columns to symmetric matrix in Excel

T

Tom2016

New Member
Dear Experts,

I have a two columns table and want to transpose to a symmetric matrix. Thank you very much for your help.

- The diagonal in the transposed symmetric matrix is the total number of products for each product purchased by all customers;

- The other part is the sum of minimum value cross all customers when customers purchase both products. For example: purchase both product#1 and product#2, 2 is minimum value for customer#1; 0 is minimum value for customer#2;1 is minimum value for customer#3, so the total is 3=2+0+1 in the symmetric matrix for both product#1 and product#2.

Original table:

Customer#1 Customer#2 Customer#3
Product#1 5 0 1

Product#2 2 1 2

Product#3 1 3 0

Product#4 0 2 1

Transposed symmetric matrix:

Product#1 Product#2 Product#3 Product#4


Product#1 =5+0+1 =2+0+1 =1+0+0 =0+0+1

Product#2 =2+0+1 =2+1+2 =1+1+0 =0+1+1

Product#3 =1+0+0 =1+1+0 =1+3+0 =0+2+0

Product#4 =0+0+1 =0+1+1 =0+2+0 =0+2+1

upload_2016-9-19_19-45-51.png
 
In cell C17 insert following formula,
=SUM(MIN(VLOOKUP(C$16,$B$5:$E$8,2,0),VLOOKUP($B17,$B$5:$E$8,2,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,3,0),VLOOKUP($B17,$B$5:$E$8,3,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,4,0),VLOOKUP($B17,$B$5:$E$8,4,0)))
and then copy down and across.
 
shrivallabha said:
In cell C17 insert following formula,
=SUM(MIN(VLOOKUP(C$16,$B$5:$E$8,2,0),VLOOKUP($B17,$B$5:$E$8,2,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,3,0),VLOOKUP($B17,$B$5:$E$8,3,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,4,0),VLOOKUP($B17,$B$5:$E$8,4,0)))
and then copy down and across.
Click to expand...

Thank you very much! it works greatly.

If there are more columns, my understanding is just to add more combination of "MIN" and "Vlookup" within "SUM" function. I wonder whether there are any general methods to generate the symmetric matrix regardless of the number of columns or rows?

Thank you so much for your help.
 
shrivallabha said:
In cell C17 insert following formula,
=SUM(MIN(VLOOKUP(C$16,$B$5:$E$8,2,0),VLOOKUP($B17,$B$5:$E$8,2,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,3,0),VLOOKUP($B17,$B$5:$E$8,3,0)),MIN(VLOOKUP(C$16,$B$5:$E$8,4,0),VLOOKUP($B17,$B$5:$E$8,4,0)))
and then copy down and across.
Click to expand...

I tried to build an array formula in cell C17 as below but failed:

{=SUM(MIN(VLOOKUP(C$24,$B$5:$F$8,{2,3,4},0),VLOOKUP($B25,$B$5:$F$8,{2,3,4},0)))}

Can you please teach me where is the array formula I use wrong?

Thanks a lot!
 
Tom2016 said:
I tried to build an array formula in cell C17 as below but failed:

{=SUM(MIN(VLOOKUP(C$24,$B$5:$F$8,{2,3,4},0),VLOOKUP($B25,$B$5:$F$8,{2,3,4},0)))}

Can you please teach me where is the array formula I use wrong?

Thanks a lot!
Click to expand...
Idea is right but MIN function is designed to return single result so it returns 0 which is the MIN and SUM function just sits there.

This evening, I will try to see if there's a way to make it scalable. How many more combinations are you likely to come across?
 
NARAYANK991 said:
Hi ,

See if this works.

Narayan
Click to expand...

Thanks Narayan. It works!

Could you please give me a little bit more explanation about the functions you use? I checked INDEX and MATCH but not quite understand why the combination can scan row by row from first customer to last one? Thanks a lot!

{=SUM(IF(INDEX(OriginalData, MATCH($B17, Products, 0),) < INDEX(OriginalData, MATCH(C$16, Products, 0),), INDEX(OriginalData, MATCH($B17, Products, 0),), INDEX(OriginalData, MATCH(C$16, Products, 0),)))}
 
Hi ,

The INDEX function takes in 3 parameters.

The first parameter is a range / array.

The second parameter is a row index.

The third parameter is a column index.

If the first parameter is a single row / single column range / array , the INDEX function will work correctly with just two parameters , where the second parameter is used appropriately as a row index or a column index , depending on whether the first parameter is a single column or a single row.

If the first parameter is a multiple row , multiple column range / array , then in general both parameters may be required. However , if any one parameter is omitted , Excel uses the entire set of rows or the entire set of columns for the missing parameter.

For example , suppose we have the following formula :

=INDEX({1,2,3,4;5,6,8,7;9,10,11,12}, 3)

The first parameter is an array with 3 rows and 4 columns. The comma is used to separate the columns , while the semi-colon is used to separate the rows.

The second parameter is taken as the row index , which means the third row is being referenced.

Since the third parameter , the column index , is not used , Excel uses the entire set of columns ; in this case , all 4 columns will be used.

Thus , given the above formula , Excel will return the following array :

{9,10,11,12}

Now , suppose we use the following formula :

=INDEX({1,2,3,4;5,6,8,7;9,10,11,12},, 3)

where an additional comma has been inserted , so that the 3 is now the third parameter , the column index.

Since the second parameter , the row index , has been omitted , Excel uses all the rows ; since the third parameter is 3 , Excel uses the 3rd column of the array. Thus , with this revised formula , Excel will return the following array :

{3;8;11}

Narayan
 
You must log in or register to reply here.
Back
Top