Generate cell pairs based on first row and column

microsoft excelspreadsheet

Given a spreadsheet like this:

x | pressure | distance | height
pressure
distance
height

I would like an output like this:

pressure pressure
pressure distance
pressure height
distance pressure
distance distance
distance height
height pressure
height distance
height height

Best Answer

Assuming your data is in A1,

Then in C1 enter the below as an array formula, hold CTRL + SHIFT + ENTER after entering

{=INDEX($A$1:$A$3,CEILING(ROWS($B$1:B1)/(SUM(1/COUNTIF($A$1:$A$3,$A$1:$A$3))),1),1)}

then in D1 enter the below:

=INDEX($A$1:$A$3,COUNTIF($C$1:C1,C1),1)

Then drag them both down until you get a REF error in column C.

Change $A$1:$A$3 in both formulas to suit your range of original data.

Related Question