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

UrHero

New Member
Joined
Jul 20, 2012
Messages
8
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. :confused:.


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!!!!!. ;)
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Is is possible for you to paste your code here for your old recorded macros? Also, any example data (and/or examples of the result) you could provide would be helpful too.

I'm thinking that you are provided a list of hospitals in column A, and a list of products each sells in column B, and you want to add up the total sales of products A through Z and put that information in columns F and G. Is this correct so far?

It seems to me that this could be completed easily if I had a little more background information.
 
Upvote 0
from FAQ
Don't use a subject line like HELP ME! or EXCEL question. Instead, say something like Trouble With Pivot Tables In XL97. And remember, saying URGENT doesn't make it so to anyone but you.
 
Upvote 0
Below is a picture of the actual sheet:

SAMPLE.png


what I need is:

Find the hospital name in question within column A, filter only the products beginning with R that doesnt end with DIR or AUTO and once you got them filtered then sum column F which is the net.

My problem is the macro I used doesn't fix the problem because the length of column C (products) change all the time it could be 10 products or 100 so I will need to be able to find the last row for the hospital in question and then add only those. After that onto the next hospital, I got 17 I need to calculate.

Let me know if you are able to understand now.

Thanks in advance.;)
 
Upvote 0
If Product names will never be blank but there is an empty cell when the hospital's products are done being listed, you could use Range().End(xlDown) which goes to just before the next empty cell in the column.

You can try other things with End(xlDown) if you think it'll help. It's the same as using Ctrl+down button in the worksheet. Test it out.


Also, you can locate the right end of a string with the Right() function. Ex: Right("R2LB AUTO", 4) would return the last 4 characters in the string, "AUTO", so you might be able to build a conditional using that. There's probably an easier way, but that would be my idea.

If Right(Range("C" & nRow).Value, 4) = "AUTO" Or Right(Range("C" & nRow).Value, 3) = "DIR" Then don't count it.

You could also write a conditional that would test if the string contains a space. What do you think?


Another question: So are you generating the "totals" row as part of the macro, and have one hospital per worksheet? Or are the hospitals listed one after another on the same sheet?
 
Upvote 0
Thank you for your answer I am going to try that right away.

Yes, all the hospitals are on the same sheet, one after the other.
 
Upvote 0
So what would be best:

Testing for a space, or testing for a last set of characters, or neither (there are other options)?

Using Range("C" & nRow).End(xlDown) to select the last product, and nRow = Range("C" & nRow).End(xlDown).Row + 4 to mark the row of the next hospital, or something else?

What do you think?
 
Upvote 0
So what would be best:

Testing for a space, or testing for a last set of characters, or neither (there are other options)?

Using Range("C" & nRow).End(xlDown) to select the last product, and nRow = Range("C" & nRow).End(xlDown).Row + 4 to mark the row of the next hospital, or something else?

What do you think?

memar I am sorry for not getting back to you sooner, but I am trying not to sound ignorant, I am trying to do what you have said but I dont know how. can you help me out?
 
Upvote 0
Can you give me an example of what your sheet looks like with 2 hospitals, not just one?
Also, an example of when you have more than one date would be helpful.
Anyway, using the example above, this is what you could do (and this only takes into account products that begin with R and don't end in DIR or AUTO):

in F359, put:
=SUMPRODUCT(--(LEFT(C327:C357, 1)="R"), --(RIGHT(C327:C357, 4)<>"AUTO"), --(RIGHT(C327:C357, 3)<>"DIR"), F327:F357)

And this should get you the correct sum... but then again, this only sums products that start with R and don't have AUTO or DIR at the end of their name.
I assume that you'll be looking for products that start with letters other than R, and also excluding more products than just the ones that end with AUTO or DIR.

You could establish the row numbers and product letters with vba code, but I'll need more information on the complete layout of your sheet.
Also, you should tell me the complete rules for deciding if a product should be counted or not.
 
Upvote 0
Thank you very much. these are the rules.

Only products that start with R not ending on DIR or AUTO. I have other products starting with P,T, they r not to be count only R products, I will take a pic of two hospitals for you to see. Below is a pic of the actual spreadsheet. once I got all the R products, I will need to SUM the Column F only. Hope it helps. and thank you!!!!!!

2ND%20PIC.png
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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