Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-18-2020, 02:02 AM
Ch0pe5 Ch0pe5 is offline Converting data using medical staging tools that come in a table form Mac OS X Converting data using medical staging tools that come in a table form Office 2016 for Mac
Novice
Converting data using medical staging tools that come in a table form
 
Join Date: May 2020
Posts: 3
Ch0pe5 is on a distinguished road
Default 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!
Attached Files
File Type: xlsx Example.xlsx (9.7 KB, 8 views)
Reply With Quote
  #2  
Old 06-06-2020, 03:43 PM
Purfleet Purfleet is offline Converting data using medical staging tools that come in a table form Windows 10 Converting data using medical staging tools that come in a table form Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Attached Files
File Type: xlsx Example(1)_PURFLEET.xlsx (11.9 KB, 8 views)
Reply With Quote
  #3  
Old 06-11-2020, 04:07 AM
Ch0pe5 Ch0pe5 is offline Converting data using medical staging tools that come in a table form Mac OS X Converting data using medical staging tools that come in a table form Office 2016 for Mac
Novice
Converting data using medical staging tools that come in a table form
 
Join Date: May 2020
Posts: 3
Ch0pe5 is on a distinguished road
Default

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.

Attached Files
File Type: xlsx Second attempt.xlsx (14.7 KB, 6 views)
Reply With Quote
  #4  
Old 06-11-2020, 11:39 AM
Purfleet Purfleet is offline Converting data using medical staging tools that come in a table form Windows 10 Converting data using medical staging tools that come in a table form Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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).
Attached Files
File Type: xlsx Second attempt(1)_Purfleet.xlsx (13.1 KB, 9 views)
Reply With Quote
  #5  
Old 06-12-2020, 02:20 AM
Ch0pe5 Ch0pe5 is offline Converting data using medical staging tools that come in a table form Mac OS X Converting data using medical staging tools that come in a table form Office 2016 for Mac
Novice
Converting data using medical staging tools that come in a table form
 
Join Date: May 2020
Posts: 3
Ch0pe5 is on a distinguished road
Default

It's worked! Thank you so much Purfleet! You're a life saver!!
Reply With Quote
Reply

Thread Tools
Display Modes


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
Converting data using medical staging tools that come in a table form Change Drawing Tools to Text Box Tools to insert address in labels Alecf Drawing and Graphics 5 09-01-2017 05:27 AM
Converting data using medical staging tools that come in a table form Is there an unlock and lock button in form tools for Word 2013? rbush Word 7 12-09-2013 05:38 PM
Converting data using medical staging tools that come in a table form 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft