Counting and summarising data

gh

New Member
Joined
Oct 22, 2002
Messages
5
I have a workbook which records the results of test cases across various lines of business. Each line of business has it's own worksheet with test case numbers, description, results etc.

As each tester completes a test, they put their initials against a test case. It's these initials that I'm trying to count. I need to find the total number of test cases that tester has completed over all lines of business (a range of worksheets) and summarise.

I've written a small macro which determines the individual testers in a worksheet (for example, there were 3 testers who worked on a particular line of business, their initals being ab, cd and ef) but I can't figure out how to do this across multiple sheets (determine how many testers worked on the entire project and what their initials were) and then produce a count of all the test cases they initialed over all sheets.

All initials are in column C of each worksheet. As more testers may be brought in to test over the length of the project, I need to keep it dynamic, rather than hard coding the tester intials, which is why my initial macro looked promising.

I'd be grateful for any advice you may have.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sounds to me as though you will need a For Each loop.

First step will be to take you macro and when it finds a tester's initials add them to a list (if not already on there).

Then your for each loop will go through each tester in that list and collate the data from the various sheets.

If you mail me a small sample I can set something up which you can then use to write the full macro.

Thanks
Luke
 
Upvote 0
Hello: gh
Welcome to the Board

A short sample of your data and your expected result would be helpful to be posted on this forum as it is a learning experience who monitor this forum.

I am sure you will get a variety of possile answers.

You can use the utility shown under this message box.

pll
 
Upvote 0
The morefunc add-in has a 3D COUNTIF which can be used like this...

=COUNTIF.3D(Sheet1:Sheet10!A2:H80,"ab")

Might be useful in tackling your problem.
 
Upvote 0
Thanks for all the responses so far. You've been most helpful.

Luke: your idea for a For-Each loop based on the contents of the array was just the ticket. I've got a working version which summarises a single worksheet quite nicely... more on this in a bit.

pll: I tried to use Colo's utility, but couldn't get it to work. If at first you don't succeed, and all that. :)

Aladin: The COUNTIF.3D solution would be quite elegant if it was just me that was collating this information. As it is, a number of team leads are also looking at this macro with interest and I'd rather provide them with a solution that doesn't require third-party plug-ins. Good suggestion though.

I have one more question. As I mentioned above, I got the macro to work for a single worksheet using a named range. The requirements for the macro have changed slightly, and now I have to report totals for each line of business (worksheet). If the workbook has 12 sheets, is it possible to use a for-each loop to step through a subset of the sheets, eg. 5-10? I noticed that COUNTIF.3D could specify a 'range' of worksheets; is there some other way to do this?

I'm not one to wait for a solution to land in my lap as I see this as a valuable learning experience. It's been a while since I did any programming and I'm finding this quite fun. :)

Thanks in advance.
 
Upvote 0
You can certainly run a For Each Loop for the sheets - again you need to set up list of sheets so it knows where to go...

Then embed the other Loop within this loop

So

For Each Sheet

Tester Loop

End Tester Loop

Next Sheet

etc...
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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
Back
Top