Returning value in new sheet based on multiple criteria

New Contributor



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! 


2 Replies
best response confirmed by Laura_1993 (New Contributor)



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!