SOLVED

Returning value in new sheet based on multiple criteria

New Contributor

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:

Laura_1993_2-1654034493226.png

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:

Laura_1993_3-1654034521905.png

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! 

 

2 Replies
best response confirmed by Laura_1993 (New Contributor)
Solution

@Laura_1993 

=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!