I need help MrExcel, I thank you in advance!!!!!

[UrHero]

I've been giving a task that I have not been able to complete due to the data being changing constantly.

This is what I need: A report is generated with the amount of products sold by the hospitals (17 hospitals total). the length of the product list for each hospitals sold will depend on the products they sell, that is why you can find a hospital with 10 type of products one month and the next one it is 25 ( meaning there is not the same amount of product all the time nor the same type of product. .


Type of products:

T Products
P Products
R Products: Among these we have "R Products-D, and R Products-A which can't be count, only the R products.
Z Products.

The idea is to locate a specific hospital along column A and add only the products on column F from that hospital that begins with R ( no T,P,Z, or R-D, R-A products), and onto the next one. For example

Column A Column F

Hospital 1 P Products
T Products
Z Products
R Products
R Products-A
R Products-D

Base on the example above. I'll need to find Hospital 1 among column A and add the products beginning with R except the ones ending on -A, or -D.

I hope I explained my self well enough for you to understand. I must say I recorded various macro and it worked but since the list of product sold vary in length, I will need to go and adjust manually in order to get the result.

Once again, Thank you in advance!!!!!.

 

[memar_one]

This isn't the most elegant code out there, but try:

Option Explicit
Sub hospital()

Dim r1 As Long
Dim r2 As Long
Dim rng As Range
Dim total As Double

r1 = 2

Do Until r1 >= Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row

    r2 = Range("C" & r1).End(xlDown).Row
    
    For Each rng In Range("C" & r1, "C" & r2)
        If Left(rng.Text, 1) = "R" And Right(rng.Text, 4) <> "AUTO" And _
            Right(rng.Text, 3) <> "DIR" Then total = total + rng.Offset(0, 3).Value
    Next
    
    Range("F" & r2 + 2).Value = total
    total = 0
    r1 = r2 + 10
    
Loop

End Sub

I assumed that the spacing between hospitals would be the same (next one starts 10 rows below the last product of the previous hospital)
I also assumed your first hospital's first product is in C2.

If the spacing is different, change this line: r1 = r2 + 10 to r1 = r2 + whatever spacing it actually is.
If your first hospital's first product doesn't start on C2, Change r1 = 2 to r1 = 10 or whatever row your first hospital starts on.

Again, not the most elegant code, but it should work.

Hope this helps!

 

[UrHero]

memar than you so much.

I run and it seems to do ok but still counting the AUTO and DIR for some reason.

Do you know why?

Edited comment below
memar I found why it is doing so. I found that next to the products with DIR and AUTO there are 8 blank space to the right, I delete the spaces and it did the trick, now I will modify the 4 to the right number and it should work.

 

[memar_one]

I believe that there is a trim function that will do this automatically...

Change:
If Left(rng.Text, 1) = "R" And Right(rng.Text, 4) <> "AUTO" And _
Right(rng.Text, 3) <> "DIR" Then total = total + rng.Offset(0, 3).Value

To:
If Left(Trim(rng.Text), 1) = "R" And Right(Trim(rng.Text), 4) <> "AUTO" And _
Right(Trim(rng.Text), 3) <> "DIR" Then total = total + rng.Offset(0, 3).Value

And then if the spaces aren't always 10, it won't matter. This will work for any number of spaces before or after each entry.

 

[UrHero]

memar after that last change is working beautifully. Thank you very much, much appreciated it.