Hello everybody,

I have a table looking like this:



1 Block1 Proc1 Done
2 Block1 Proc2 Done
3 Block1 Proc3
4 Block1 Proc4
5 Block2 Proc1 Done
6 Block2 Proc2
7 Block2 Proc3 Done
8 Block2 Proc4 Done
9 Block2 Proc5
10 Block2 Proc6
11 Block3 Proc1 Done
... ... ... ...


I want to automatically find what was the last process (highest number) marked as done for each block. In this case, that would be Proc2 for Block1 and Proc4 for Block2. Note that the fourth column might contain blank spaces.
How could I do that within a function or using VBA? Any idea?

Thanks

Hi
perhaps post a small sample sheet. I hate having to retype data - Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Funk, I have a notion about how to find out using formulae. But I'm a little hazy on how that information is best presented. That is, I can see a running column down the same page that says (for example) "Block1.Proc2", and then a bunch of blank cells and then "Block2.Proc4", and so on down the page, so that the eye will be caught by the cells that have writing in them and the rest of those cells are blank.

But if you want them in a separate worksheet as a consecutive list, I expect it's possible—I've done that sort of thing—but it's a little kludgy. VBA would be the more obvious choice, but then VBA does you no good unless you know how to write programs—and if you knew how to do that you wouldn't be how to do it in VBA. So I guess the next question (unless you want to learn VBA) is how you want this information presented....?

I know some basic VBA, but not enough to do what I want.
So far, all I could figure out was how to find the last cell on a row within a range of rows. But I don't know yet how to perform a loop detecting the first and last rows of every block, and to use that to find the last cell for each.
I thought there must be a way for a pro to do that within a few lines of code. But if it is going to get too complicated, then maybe I should just forget about it.

(Changing the distribution of the input data is not a possibility)

Attached Files

sample.xls (39.5 KB, 14 views)

A pro can do it in a few lines. But for me, at least, the whole point of a forum like this is to help you understand how you can do it yourself. Others may not feel the same way about it.

If you want to, I can teach you how to do that loop; I don't think it's too complicated and I don't think you will either. But I know some people want to learn that kind of thing, and others flee from the prospect in an extremity of boredom. If you're the latter type, let's talk about how to do it with formulae; I'm pretty sure we'll be able to figure out a way, with some thought. If you're the former, I grin and metaphorically roll up my sleeves. Which do you prefer?

Wait, I should have asked something before: Some tasks are better done with VBA because even though it takes longer, you're going to need to do it again and again, so it's worth taking the time up front. For example, you can take fifteen minutes to do a task with worksheet functions—or fifteen milliseconds to do it with VBA, but only after you've taken five hours to write the program. If you need to do it only once a year, the formulae are probably the way to go, unless you decide it's worthwhile to learn VBA anyway. But if you're going to do the task twice a day for the next few years, then the up-front investment is well paid back and VBA is the better choice.

On the other hand, some things are better done with worksheet functions because you need their output not repeatedly but continuously. If you want to enter "Done" somewhere on your worksheet and have the results listed on another sheet not at the end of the day when you run the program but immediately, then formulae begin to look better. Which situation is this?

The attached formula solution gives the Proc from each block that is following the last that has been done, according to your example.
I hope I understood correctly your intentions.

Attached Files

Copy of sample.xls (40.5 KB, 18 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

This thing I was trying to do is part of a more complex spreadsheet I am working on, but of course I wouldn't ask you guys to do the whole job for me. I just needed a little of help with this part.

Pecoflyer, thank you for that. Your workaround does what I was asking for. Now I need to check how well it fits my needs.

BobBridges, I agree with you. I am always keen to learn VBA. It sure pays off not only in terms of performance, but also with the experience you get to solve similar problems in the future without asking other people. I haven’t discarded the idea of using a little bit of code to do this thing, but I think I’m going to try with Pecoflyer’s function first. I’ll get back to you if need more help with that loop. And thanks anyway.

It's funny how people on forums try to reinvent the wheel .

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

When I do it because I didn't know the wheel had already been invented, it's funny (in the sense that others may laugh at the time I wasted. Sometimes I can laugh about it too; other times it takes me a while ). But there are other reasons to invent a new wheel. For instance, in writing a tool (even if it's been done before) I usually learn something I didn't know before. That's useful for the next time ... and there is always a next time. And then, too, one may feel that a wheel is all very well, but that wheel is green and I want a blue one, with a slightly elliptical shape and the axle 2cm off-center and a different tread pattern.

But the biggest reason, for me anyway, is that it's fun. If it isn't fun for you, well, a) I'm sorry, and b) there's not much more to be said—because the above reasons are true, but they probably wouldn't be sufficient (except in cases of special need) if I didn't enjoy coding in the first place. For someone who doesn't like it, the fact that a version already exists out there pretty much ends the debate.

As you probably know, this forum is dedicated to helping members and providing them with a solution. Although the OP might be interested in a VBA solution when a formula will do the job, most of the other visitors are just seeking a simple solution they can apply to their problem ( which might be a little different from OP's), and eventually to future problems they will encounter.
After many thousands of posts on different forums, I have also noticed that members and visitors tend to read the thread's last post containing a solution. ( even if the best answer to their problem is several posts higher in the thread)
So, having fun in a thread mimicking an existing formula with code not only will confuse the OP ( eventually) but also visitors seeking a similar solution.
Functions are relatively easy to understand and to explain, code takes a lot more knowledge. A VBA solution to a problem would usually need some adaptation to someone else's identical problem ( be it alone sheet names).
Now, if one wants to write code like " Hey guys look, I mimicked an IF function with 2000 lines of code ! " , perhaps the VBA forum would be a better place.

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Don't misunderstand me, Peco; I don't disagree. But in this case, the OP himself said he's still thinking about a VBA solution, you sneered mildly about reinventing the wheel and in defense of Funk I pointed out several reasons why people may want to do that. That's really all there was to it.

In Funk's case particularly, there may be good reasons why he'll need VBA in the long run. Your solution works well if he just needs to glance at the worksheet and see the latest "Done" proc identified for each block. But if he had needed (or if he ever needs) the summary of that information listed on a separate sheet, I think it can be done with formulae but it may be simpler to use VBA. He never said.

[Later:] I take back the second paragraph. I just looked at your solution (I only glanced at it before), and it deals with that question, better than my approach would have. I'll have to study it more closely to see how you did that.

(What is "OP", anyway?)

OP = Original Poster

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

this is for bone block u can generate it for the whole thing easy ozy
Sub y()
Dim i As Double
Dim count As Double
Dim rk As String
count = 0
For i = 1 To 7
If Range("a1").Offset(i - 1, 0) = Range("a1").Offset(i, 0) Then
If Range("c1").Offset(i - 1, 0).Value = "Done" Then count = i
rk = Range("b1").Offset(count, 0).Value
End If
Next i
MsgBox rk
End Sub