Need help using VBA for report

[ClinicalResearchExcelDude]

Hello, long time lurker but created an account because I can't seem to find the solution.

I use a report tool from my clinical research work that I can download the Excel file. The problem is that I only want to see the most recent status for budget, contract and regulatory for every study we are working on, but our reporting feature doesn't allow this type of filter so I'm forced to use Excel and use VBA to solve this issue. Data starts from A7, and there are hundreds of rows of data to sort/filter. The main column of interest are A (study ID), F (study status to filter contract/budget/regulatory), G (status date, only need the newest for each status from column F), and the last column N (Notes for the status made by the person entering into system). I want, either in the same or new sheet, to create a final report that has the most recent status for budget, contract and regulatory for every unique study ID.

The way I manually have been doing it so far:
1. Hide the rows/columns I don't need
2. Add filter to row 7, which has column headers
3. Study ID - select one study at a time using the filter (column A), click on study status (column F) and type "budget", and sort status date (column G) by newest first. Copy the top row (if any exist) and paste it on a different sheet.
4. Now do the same for step 3 for "contract" and regulatory" in the study status (column F)
5. Now unselect the study ID from 3, and move onto 2nd study, and repeat steps 3 and 4.

As you can imagine, this takes a significant time to do manually. This type of report will be coming in weekly to track changes/updates so I would like to create a macro to expedite this. I'm not a beginner, but I'm definitely not a VBA expert. I only have used a macro recorder to do steps 1 and 2 so far. I have been saving the macro in my personal workbook because I'll be using it on different files every time.

Can a VBA expert guide me in creating such a macro? The file has restrictive data so I can't share it but I'm happy to provide and have discussion about finding an answer.

Thank you!

 

[kevin9999]

Can you share a file with dummy/bogus data in it? What you're looking for isn't that difficult, but I wouldn't even start to look at this without a sample showing the actual data structure/layout.

 

[ClinicalResearchExcelDude]

Can you share a file with dummy/bogus data in it? What you're looking for isn't that difficult, but I wouldn't even start to look at this without a sample showing the actual data structure/layout.

That's a great point. I just deleted the data and filled the top with one study in the study number column (column A). I can only provide screenshot of the file due to company restrictions. In the interest of time, I won't be making up bogus data for all rows but just imagine a whole bunch of the study status made for the same study number. I only put two study numbers in this screenshot, but rows can go in the hundreds with dozens or even hundreds of different study numbers, each with unique notes. Like I said previously but I'm only want to see the most recent (study status date column F) for budget/contract/IRB. I type those keywords in the filter because the status can be anywhere from initial, sent/received to approved. I hope this makes sense.

 

[kevin9999]

Thank

That's a great point. I just deleted the data and filled the top with one study in the study number column (column A). I can only provide screenshot of the file due to company restrictions. In the interest of time, I won't be making up bogus data for all rows but just imagine a whole bunch of the study status made for the same study number. I only put two study numbers in this screenshot, but rows can go in the hundreds with dozens or even hundreds of different study numbers, each with unique notes. Like I said previously but I'm only want to see the most recent (study status date column F) for budget/contract/IRB. I type those keywords in the filter because the status can be anywhere from initial, sent/received to approved. I hope this makes sense.

View attachment 56314

Thanks for that , there may be some more questions to come...

 

[kevin9999]

Your screenshot doesn't match your description of the structure you provided in your first post.
Example:
post#1 has study status in col F - screenshot in col E
post#1 has status date in col G - screenshot in col F
post#1 has Notes in col N - screenshot in col K
post#1 has header row as 7 - screenshot as 2

Which is correct?

Also, do you run the macro from the sheet with the data in it - if so, what is the sheet called? Do you have a sheet already where you want to copy the filtered data to - likewise what is it called?
Alternatively, do you want to create a new worksheet each time for the report, or if you want to use an existing sheet, do you want it to be cleared each time before running the macro?

 

[ClinicalResearchExcelDude]

Your screenshot doesn't match your description of the structure you provided in your first post.
Example:
post#1 has study status in col F - screenshot in col E
post#1 has status date in col G - screenshot in col F
post#1 has Notes in col N - screenshot in col K
post#1 has header row as 7 - screenshot as 2

Which is correct?

Also, do you run the macro from the sheet with the data in it - if so, what is the sheet called? Do you have a sheet already where you want to copy the filtered data to - likewise what is it called?
Alternatively, do you want to create a new worksheet each time for the report, or if you want to use an existing sheet, do you want it to be cleared each time before running the macro?

Right, sorry about that. The screenshot is more up to date because I deleted the columns from the raw file. The sheet you see in the screenshot is after the column were deleted.

I do not have a file that I'll be copying the filtered data to. I'll probably end up using a new file every time so the copied sheet can be called Sheet1 for now. I want to create the new report in a different sheet of the same file. I know that VBA can be used to create a new sheet (we can call this "Final Report" for now) and put all the filtered data there.

 

[kevin9999]

Right, sorry about that. The screenshot is more up to date because I deleted the columns from the raw file. The sheet you see in the screenshot is after the column were deleted.

I do not have a file that I'll be copying the filtered data to. I'll probably end up using a new file every time so the copied sheet can be called Sheet1 for now. I want to create the new report in a different sheet of the same file. I know that VBA can be used to create a new sheet (we can call this "Final Report" for now) and put all the filtered data there.

And the header row?

 

[ClinicalResearchExcelDude]

And the header row?

I would also like to keep the header in the new sheet

 

[kevin9999]

I would also like to keep the header in the new sheet

Is the current header row 2 or 7?

 

[ClinicalResearchExcelDude]

Is the current header row 2 or 7?

The header is ideally in row 1 - I just didn't see that I missed to delete that in the screenshot