Need an Excel genius!

Maccy · 21 Aug 2013 at 15:15

I'm not sure what I'm asking for is possible, but will try and ask anyway!

Basically, we have a list of test results in the following format -

Business Process - Response Time

BP1 - 2.34
BP1 - 4.32
BP1 - 3.21
BP1 - 1.27
BP1 - 2.95

It's on a larger scale, but let's keep it small for now.

The number of business processes is likely to change each time we run a test; the first test might have 50 BP1 results, but the second test might have 52, etc.

What I want to do is use a formula to count how many BP1's there will be, and then print the cell ranges. Is this possible?
For example, in the values above, they could be A1:A5 so the results would be B1:B5 - I need the B1:B5 values printed somewhere I can use them in another formula.

Please help!

Judgeneo · 21 Aug 2013 at 15:19

=count(A1:A10) would tell you how many BP1's there are.

As long as you have an idea of the variance, it will work fine. I.e. if you know you're only going to be counting a max of 10 at one time. Not sure how you would create a solution without an upper bound on excel.

wesimmo · 21 Aug 2013 at 15:27

=ADDRESS(ROW(B1),COLUMN(B1)) & ":" & ADDRESS(ROW(B1)+E4-1,COLUMN(B1))

The above should do it for you, note that "E4" is the cell where you use =COUNT to work out how many rows are populated.

edit:

If you want the cell range to be relative (i.e. no $ in it) then

=ADDRESS(ROW(B1),COLUMN(B1),4) & ":" & ADDRESS(ROW(B1)+E4-1,COLUMN(B1),4)

edit 2:

All contained in one formula, B1 must be the first cell with data in.

=ADDRESS(ROW(B1),COLUMN(B1),4) & ":" & ADDRESS(ROW(B1)+COUNT(B1:B62)-1,COLUMN(B1),4)

Maccy · 21 Aug 2013 at 15:52

Forgot to say, there will be other business processes in the first column, BP2, BP3 etc.

wesimmo · 21 Aug 2013 at 15:54

Will they be in order? i.e. will all BP1 be listed , then BP2, etc?

edit:

If so then

=ADDRESS(ROW(B1),COLUMN(B1),4) & ":" & ADDRESS(ROW(B1)+COUNTIF(A1:A500,"BP1")-1,COLUMN(B1),4)

You could also remove the "BP1" bit and refer to a cell with "BP1" typed in it.

Maccy · 21 Aug 2013 at 16:05

Yeh they'll be re-ordered so all of them will be together.

wesimmo · 21 Aug 2013 at 16:08

Ok, in cells D1 : D3 i had typed in "BP1", "BP2" and "BP3".

Then paste the formula below into cell E1 and drag it down.

=ADDRESS(ROW($B$1)+MATCH(D1,$A$1:$A$31,0)-1,COLUMN($B$1),4)&":"&ADDRESS(ROW($B$1)+MATCH(D1,$A$1:$A$31,0)+COUNTIF($A$1:$A$500,D1)-2,COLUMN($B$1),4)

It'll now give you the range for each of the BP#

httk · 21 Aug 2013 at 17:38

I think he needs a COUNTIF for the BP1 criteria, no? And for the second part you might need an INDIRECT.

wesimmo · 21 Aug 2013 at 18:18

Its not pretty. I could make it a bit simpler having thought about it but I'm not near a PC at the moment. But the above worked for me when I tested it. I also realised the ranges will need amending, I had 31 rows of test data which I inadvertently left as it was before posting.