0

My current table:

Item 1 Item 2 Similarity (%)
A A 100
A B 90
A C 85
B B 100
B C 70

What I want to get:

A B C
A 100 90 85
B 90 100 70
C 85 70 100

Is there a way to get this in Excel? Thank you for your time!

Laura
  • 1

2 Answers2

0

Can you explain why is the value 100 when both row and column are C according to your current table?

Please check whether the following steps are helpful.

  • Copy the Item 1 and Item 2 into a new column, and go to Data > Remove Duplicates.

Then we can get the list of "A,B,C".

  • Cop the list, and go to Home tab > Clipboard group > Paste > Paste Special > Reverse.

Then we can get the row and column names of the matrix．

  • Enter =SUMPRODUCT(($A$2:$A$6=$E5)*($B$2:$B$6=F$4),($C$2:$C$6)) in F5 in following image. And pull down, pull right.

enter image description here

  • Copy the ranges from E4 to H6, choose a blank cell and right click it > Paste Special. On my samle, I choose cell E9.

Choose Values and Transpose.

enter image description here

enter image description here

  • Choose the range from E9 to H12, press Ctrl + H, replace value 0 to empty.

enter image description here

  • Choose the rang from F10 to H12, click copy, right click F5, choose Paste Special, tick box of "Skip blanks".

enter image description here

enter image description here

Emily
  • 4,035
0

If your input table's upper left corner is A1 and your output table's upper left corner is E1, then the given data table will give the desired output using the following formula in cell F2: (it SPILLS... for earlier Excel, simply put the formula there, then copy and paste to fill your output table, though for slightly more work, the range references can be made into cell references to fully control just which cells are evaluated)

=XLOOKUP( IF(E2:E4<F1:H1, E2:E4&"|"&F1:H1, F1:H1&"|"&E2:E4), A2:A6&"|"&B2:B6, C2:C6, "—")

Well, actually, it gives "—" for non-found pairs such as the "CC" pair in the bottom right corner (same one Emily's answer asks about).

The trickiest part is the required ordering of the row-column labels, when combined, in order to easily search the input table. That is done with the IF() in the lookup value parameter (1st parameter) of the XLOOKUP(). The rest is easily understood.

One could fill the output table's row and column headers with a SORT(UNIQUE()) of the leftmost column for the row headers in the output table and TRANSPOSE(SORT(UNIQUE())) for the column headers. And with a wee bit of effort, one could place those two formulas into the table filling formula above in place of E2:E4 and F1:H1 to build the whole thing tighter (as in each part fully matching what the other parts are doing).

However, just the above in cell F2 will nicely fill any table with proper editing of the ranges.

Of course, pivot tables (cross tab(ulation)s) and related functionality and techniques have been enormously popular for 37+ years for good reasons so taking a non-formula approach is surely not taking a step back in quality of output.

Jeorje
  • 1