#1
|
|||
|
|||
Converting data using medical staging tools that come in a table form
Morning all, I'm having trouble figuring out how to convert my data between two different cancer staging methods. I've included a picture of the tool below so you get an idea. http://patentimages.storage.googleap...00004_0001.png Basically, I have the T, N and M data all listed in separate columns and I've used the CONCAT function to combine them because they all affect each other. I'm trying to convert the combined TNM stage to the staging with roman numerals for example: T1 N0 M0 should become Stage I T3 N0 M0 should become Stage IIA T4 N2 M1 should become Stage IV and so on..... I've also attached an example set of data that I made I up that is similar to my original dataset: I've removed the wordy parts from the T, N and M categories and then combined them in the end column. It's fairly complicated question but I can't help but think there must be a simple solution to it all. Thank you! |
#2
|
|||
|
|||
Not sure if it’s any good but I have made a start. i am working in reverse order and if it matches exactly we can return the correct stage.
I think we need some kind of exception to pick up the last couple of stages where the 'any' come in to play Question - where do the Tx & N1a come into the calculation? Have a look at the attached and let me if you think it could work or not |
#3
|
|||
|
|||
Hi,
This is awesome thank you! I've been trying with the VLOOKUP function with little success. I'm having a look at the attachment you sent. For the first cell it seems to work well. When I try to fill series, the cells in the "Sheet2!$D$2:$D$12" part of the function change as well. Is there anyway of keeping these constant as I fill series? Because I'm effectively trying to match all my data to a single fixed table. At the minute the programme seems to think that my UICC table is moving down a cell as I use the fill series function... I'm a total dummy though so I may have missed a trick! As for the Tx and N1a - those are just little nuances in the staging, I've altered those in my worksheet to bring them in line with the rest of the data and it doesn't cause any problems with the UICC outcome. I've also extended the UICC index to include the possible TNM combinations that can be found in my full dataset. |
#4
|
|||
|
|||
If you are going to go with the one time pain of listing all the possible combo's in your lookup the formula becomes much eaiser
=INDEX(Sheet2!A:A,MATCH(Sheet1!H2,Sheet2!E:E,0)) If you get any #n/a's on the index and match formula you know that there is a new combination. Yoiu can then just add it at the bottom of the lookup table and it will show on the data sheet I use index and match as it is more felxible than vlookup (index/match can look left for a start). |
#5
|
|||
|
|||
It's worked! Thank you so much Purfleet! You're a life saver!!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Converting into a fixed column width table with data alignment&table length under 72 colums | samirahmed007 | Excel Programming | 0 | 01-18-2019 09:54 AM |
Change Drawing Tools to Text Box Tools to insert address in labels | Alecf | Drawing and Graphics | 5 | 09-01-2017 05:27 AM |
Is there an unlock and lock button in form tools for Word 2013? | rbush | Word | 7 | 12-09-2013 05:38 PM |
Converting data-table into coding text | niuri_cigarete | Excel | 2 | 12-10-2011 02:57 AM |
Tools for converting PowerPoint to screensaver. | on_thefly | Office | 0 | 10-28-2010 01:21 AM |