Forum Discussion
Help! Complex formula for large excel spreadsheet
I am an excel novice. I have a complex formula to try and write. Here's the concept:
I have a large table that contains just two columns. The first column has a number ID which represents a volunteer. The second column has a number code that represents a disease. See below:
ID Disease
1621 407
1621 405
1621 621
1622 637
The code I am interested in is 405. So every cell that contains a 405, I want to extract that row to a new spreadsheet. This part is easy. However, I am also interested in every volunteer that has the 405 disease, all the other diseases associated with that volunteer's ID. So, in the example above, ID 1621 had disease 405 and also has 407 and 621. So, I need to write a formula, that extracts all rows associated with a 405 disease and the other diseases with that ID number. In this case, I need the row above and below. However, there may also be more diseases in rows above and below so this is dependent on when the ID changes to move onto to the next 405-associated ID. Then in this example, if ID 1622 does not contain 405, it is not included. So, it needs to recognize the change in ID number for the values associated with 405. help? Thanks.
2 Replies
- OliverScheurichGold Contributor
=IFNA(IF(MATCH(1,($A$2:$A$15=$A2)*($B$2:$B$15=$G$2),0),A2,""),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell C2 and filled across range C2:D15.
- LisaM5000Copper Contributor
Thanks so much! That works beautifully! I will have to study it to understand how it works. Thanks again!