0

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
1

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.