Sorting by method into individual sheets

[RaydenUK]

I am stumped on this problem and would really appreciate some help. I have a list of Quality Controls and Patient Data. I need to separate this data into individual sheets within the workbook using a code. I have attached an example of the list in the "Accession" sheet. Originally, this workbook would only contain this tab with only information in the first three columns. I have added the table on this sheet to show how the data needs to be split. Essentially, the data needs to be split based on the Method Letter that is attached to the end of the long accession numbers, like 1532105R_O. This would be an O method. Some have R's after the accession # but those can be ignored. Only the letters preceded by an underscore denotes the method, and some have multiple methods like 1532105R_O_B_G. Tabs would need to be added with the method type as the name of each sheet. To show how they will need to be split, this workbook already has those tabs made and named appropriately. If a method type is not used in the list, as with the "A" method, then a tab will not need to be made and it can be ignored. If I had a code that could automatically do this for me then it would save a massive amount of time and prevent several human errors. Thank you in advance for the help.


Sorting methods Into indiv sheets.xlsx

[watersev]

hi RaydenUK,

can you go in detail for B and D methods for getting provided result on respective sheets.
I do not understand why 15029859RX5_L goes to B sheet. Check Tray looks to go to any method by default. I do not understand why HYDRO goes to D sheet.

[RaydenUK]

I added those individual method sheets on fairly quickly, so I may have put some of the wrong ones in each sheet. The table on the first sheet has the exact layout though. Sorry about that. Yes they all have a blank, negative, and check tray. The B should not have an L with it and D should not have a Hydro. Like I said I must have let those slip by me. Thanks for catching that.

[jindon]

Let's see if this is what you wanted.

Please Login or Register  to view this content.

[RaydenUK]

Wow that is very impressive, thank you jindon! If a method is not present in the accession numbers, like A, then how could it be changed to not include it?

Again thank you very much for the help!

[RaydenUK]

Also the table would need to be put on its own sheet that is hidden, possibly named "Master", because I have other code that uses that space in the "Accession" Sheet.

[jindon]

If a method is not present in the accession numbers, like A, then don't include it.

Also the table would need to be put on its own sheet that is hidden, possibly named "Master", because I have other code that uses that space in the "Accession" Sheet.

What do you mean?

This includes "Check_Tray"

Please Login or Register  to view this content.

[RaydenUK]

If you look at method "A" then you can see that there are no patient accession numbers, like 1543215. So if a method doesn't include a sequence of numbers then it doesn't need to make a sheet for it. Also is there any way that the table that is used to show what methods get what information can be moved? Preferably to its own sheet? Thank you for the help, I greatly appreciate it!

[jindon]

Not sure about "Table"....
If you upload a sample with the result that you want, it would help to understand.

Please Login or Register  to view this content.

Edit:

[RaydenUK]

Sorting methods Into indiv sheets2.xlsm

This will hopefully illustrate what I am talking about. Sorry for being so confusing, but the table I am referring to is the one that was originally on the "Accession" sheet in cell G1. I just need to move it to a different sheet. In this workbook I have moved it to the sheet named "Master" in cell A1.

[jindon]

OK then

Please Login or Register  to view this content.

[watersev]

option, please check attachment, press Run button or run code "test" (ALT+F8, select "test", press Run)

[RaydenUK]

WOW! Sir you are truly a master at your craft. This will save my company Hundreds of hours and prevent many mistakes. I cannot thank you enough!

Watersev that works perfectly too!! You guys are unreal!!! Thank you all very much!

[RaydenUK]

I just noticed one more thing with these codes. Is there anyway to keep any sheets that may already exist in the workbook. I have a sheet labeled "Map" that needs to be there but everytime I run these codes it deletes it out. Any way around that?

[watersev]

this line identifies sheets that are not subject for deletion:

Please Login or Register  to view this content.

Change it to:

Please Login or Register  to view this content.

[RaydenUK]

Great! Thank you.

[jindon]

[code]
If (ws.Name <> "Accession") * (ws.Name <> "Master") * (ws.Name <> "Map") Then ws.Delete[code]

[RaydenUK]

After using both of these codes i discovered one small problem. They do not create new tabs for "methods" with more than one method tagged onto the patients accession number. For instance, if patient 150938290_G_L_F was the only patient then it would create only one method tab, in this case "G" since it is the first one. Now if the L and F tabs are being made from another patient who has those methods as their first, then it would sort the fore-mentioned patient just fine. In other words, it only makes tabs for methods that are listed first in a string rather than all of the methods. Help would be greatly appreciated.

[jindon]

Not really understandable....

Can you post a small sample with

1) Data that doesn't work as expected.
2) Code that you are currently using.