Excel tutorials

How to Pairing Rows and Columns Headers if there are Duplicates in a Range as Conditions

In this Excel tutorial I will show how we can rank the first ten pairs of row headers and column headers if the condition of the cell range at the intersection of the Row and the Column is met. Duplicate conditions may appear in the cell range. Notice the situation in the picture below. In the 'B2:F6' range, values are expressed in percentage. In this range, we are seeking our ranking requirement.

Our goal is to return the Row header and the Column header if the duplicate condition from the range matches. In the 'I' column, see the values/percentages of the cell ranges ranging from the largest to the smallest. In the 'J' column, note that the row header for a given value is displayed for the relevant ranges. In the 'K' column, note that the column header is displayed for a certain value from the range, for the relevant ranking.

 The ranking of the first ten pairs if the condition in the intersection of the row and the column is met

Ranking Row Headers and Column Headers if there is a Duplicate Values in the Range

We can solve this task with three formulas. In the 'H' column, numbers from the larger to the lower are manually entered. These numbers are not included in the formula calculation.

The formula in 'I2' cell is below. (This formula ranks all the values from 'B2: F6' from the largest to the smallest).

Also for ranking, we can use this formula below. Notice the difference for the second argument of the LARGE function in which we approximate the ROW(A1) formula. When we copy the formula below, then this nested formula changes the result 1,2,3 ...).

The ARRAY formula in 'J2' cell is below. (This CSE formula returns the Row Header a condition of 'I' columns. Thus formula finds a value in the range and returns the Row header).

Notice in the formula above the second argument IF function (green font). As a result, it returns the total number of rows in the 'A' column containing the data. If you look at the Evaluate formula or use the "F9" key then you can see the result of this nested formula {1;2;3;4;5}.

ARRAY formula in 'K2' cell is below. (This CSE formula returns the data from the Column Header to match the condition in the same column, copy the formula below. Note that the formula is based on data from 'I2' and 'J2' cells).

If you work with a larger range of data and copy the formulas to the right, then note that the absolute and relative addresses in the formulas should be correctly set.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.