Forum Discussion
Solved
Returning value in new sheet based on multiple criteria
Hello!
I am trying to rearrange my Excel dataset (+/- 5000 patients). I'm hoping someone can help me with the following.
As an example, currently the dataset is arranged like this on Sheet 1:
The data is stored in long format and I want to reshape it to wide, based on certain criteria. I thought I should place the rearranged dataset on Sheet 2. The new format should look like this:
A measurement for 'FWD' returns in sheet 2 (for example, FWD_1) based on three criteria:
- Measurementnr = 1
- On time = 1
- Matching patient number (the unique patient numbers are already in Column A in Sheet 2)
The same should be done for FWD_2 (= measurementnr 2) and FWD_3 (=measurementnr 3). If a measurement was not performed on time, the value should not return in Sheet 2.
Any help would be much appreciated!
=IFERROR(INDEX($F$2:$F$13,MATCH(1,($A$2:$A$13=$H2)*($C$2:$C$13=I$1)*($E$2:$E$13=1),0)),"-")Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
=IFERROR(INDEX($F$2:$F$13,MATCH(1,($A$2:$A$13=$H2)*($C$2:$C$13=I$1)*($E$2:$E$13=1),0)),"-")Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- This works! Thank you very much!
