Hi everyone! I just started learning VBA excel coding. VBA excel for dummies and tutorials have helped a lot but I am still stumped on how to create a macro to do everything in one go. Right now, it takes me about 1+ hours to manually transfer data even with the few formulas and codes that I have been able to create to assist. I need to make it auto populate as much as possible to not only reduce the amount of time involved but also so that my co-worker who covers me when I am away can do it, as she is not as tech savvy.
We get data from one of our instruments that we manually transfer into a spread sheet (“Case Load Report”; Fig.1). I need to pull specific data for my techs (highlighted in green in Fig. 2) and paste into another spread sheet (“HZ report”; Fig.3). After the data is double checked (sometimes there is no case pathologist provided) I then take specific data from that spread sheet (highlighted in blue Fig.4) and paste it into a third spread sheet for my pathologists (“Path Case Distribution”; Fig.5)
The 1st transfer process gets even more complex as I also need to auto edit the ID# data when it is transferred to the tech sheet (“HZ report”; Fig.3). Each case ID (Ex: SP-20-12345) has one or more parts to it represented after the “.” (Ex: part one = SP-20-12345.1; part two = SP-20-12345.2, etc.) with samples of each part represented by letters (Ex: SP-20-12345.1A; SP-20-33456.11.BC). I need to extract just the alpha characters after the “.” for each ID # (Ex: changing SP-20-3945.1A to SP-20-3945.1; SP-20-4008.1AB to Sp-20-4008.1) and then get a count of each duplicate in order to get a total block count for each part of the ID# to reduce the amount of paperwork as the original way is too much (Example of completed sheet Fig.6).
I have only been able to figure out how to remove everything after the “.” with the below formula.
=IF(LEFT('Case Load Report'!A8,10)="","",CONCAT(LEFT('Case Load Report'!A8,IFERROR(FIND(".",'Case Load Report'!A8,1)-1,""))))
I have tried the Pivot Table but I could not get that information to work with my next transfer process.
Once everything has been double checked in the “HZ Report” spread sheet, then it gets even more confusing. I wanted to create a button that would start the next transfer process. It needs to auto edit the ID# data again to only the case ID# (EX: from SP-20-3945.1 to SP-20-3945) and also give me an overall total block count for that case by counting the duplicates, but then also do a search to locate the column for a specific pathologist and then paste the edited information in their section (Ex: Fig.7).
Besides removing all of the cassette information I have no clue on how to go about doing this part, or if it is even possible with VBA.
I defiantly picked a big project to start off with as a beginner so any suggestions would be great or if you know of any tutorials that would help I would greatly appreciate it!
We get data from one of our instruments that we manually transfer into a spread sheet (“Case Load Report”; Fig.1). I need to pull specific data for my techs (highlighted in green in Fig. 2) and paste into another spread sheet (“HZ report”; Fig.3). After the data is double checked (sometimes there is no case pathologist provided) I then take specific data from that spread sheet (highlighted in blue Fig.4) and paste it into a third spread sheet for my pathologists (“Path Case Distribution”; Fig.5)
The 1st transfer process gets even more complex as I also need to auto edit the ID# data when it is transferred to the tech sheet (“HZ report”; Fig.3). Each case ID (Ex: SP-20-12345) has one or more parts to it represented after the “.” (Ex: part one = SP-20-12345.1; part two = SP-20-12345.2, etc.) with samples of each part represented by letters (Ex: SP-20-12345.1A; SP-20-33456.11.BC). I need to extract just the alpha characters after the “.” for each ID # (Ex: changing SP-20-3945.1A to SP-20-3945.1; SP-20-4008.1AB to Sp-20-4008.1) and then get a count of each duplicate in order to get a total block count for each part of the ID# to reduce the amount of paperwork as the original way is too much (Example of completed sheet Fig.6).
I have only been able to figure out how to remove everything after the “.” with the below formula.
=IF(LEFT('Case Load Report'!A8,10)="","",CONCAT(LEFT('Case Load Report'!A8,IFERROR(FIND(".",'Case Load Report'!A8,1)-1,""))))
I have tried the Pivot Table but I could not get that information to work with my next transfer process.
Once everything has been double checked in the “HZ Report” spread sheet, then it gets even more confusing. I wanted to create a button that would start the next transfer process. It needs to auto edit the ID# data again to only the case ID# (EX: from SP-20-3945.1 to SP-20-3945) and also give me an overall total block count for that case by counting the duplicates, but then also do a search to locate the column for a specific pathologist and then paste the edited information in their section (Ex: Fig.7).
Besides removing all of the cassette information I have no clue on how to go about doing this part, or if it is even possible with VBA.
I defiantly picked a big project to start off with as a beginner so any suggestions would be great or if you know of any tutorials that would help I would greatly appreciate it!
