# Generate cell pairs based on first row and column

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
``````

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.

• In my use case A1 is empty and B1:D1 and A2:A4 are used. Tweaking your solution doesn't work for me. – anon8723879278 Apr 3 '18 at 16:18
• @anon8723879278 - In, say cell `A6`, use this: `=INDEX(\$A\$2:\$A\$4,CEILING(ROWS(\$B\$1:B1)/(SUM(1/COUNTIF(\$A\$2:\$A\$4,\$A\$2:\$A\$4))),1),1)` (enter with CTRL+SHIFT+ENTER, and drag down). Then in `B6` enter this and drag down: `=INDEX(\$A\$2:\$A\$4,COUNTIF(\$A\$6:A6,A6),1)` – BruceWayne Apr 3 '18 at 18:52