Extracting specific data from one spread sheet, but editing the data at the same time, then repeating the process to a third one


New Member
Jul 2, 2020
Office Version
  1. 2010
  1. Windows
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!


  • FIG.1.png
    85.2 KB · Views: 11
  • FIG.2.png
    107.1 KB · Views: 9
  • FIG.3.png
    24.8 KB · Views: 8
  • FIG.4.png
    21.6 KB · Views: 8
  • FIG.5.png
    24.7 KB · Views: 6
  • FIG.6.png
    11.3 KB · Views: 6
  • FIG.7.png
    19.3 KB · Views: 6

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number


MrExcel MVP, Moderator
Jun 12, 2014
Office Version
  1. 365
  1. Windows
Glad we could help & thanks for the feedback.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back