• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare sums in a variable range based on a reference

J

JTsoldier

New Member
I consolidate 2 reports into one workbook and have a 3rd sheet setup to hold all my functions. I am sure there is a macro that would do this all for me, but I don't know VBA.

The file uploaded has 3 sheets. The first 2 sheets are fairly accurate sample segments of the data I work with (minus sensitive details). If you look at sheet3, I've set it up to copy everything from sheet2 and the function I need help with is in column I. The function I was trying to create seems like it may not work and is not very efficient, so I am trying to go about it a different way.

I'm trying to find out if each vehicle is waiting for parts ("S") or waiting for maintenance ("M") by comparing the due in parts (column Q) with the ordered parts (column k:n). If the number in column Q is less than the number in column K:N, then it is waiting parts.

I think a better solution would be to sum up all the due in parts for one vehicle and match it to the sum of parts ordered and if there is a difference, then it is waiting parts. I think it would be some variation of:

=if(sum(offset( [sheet1 serial number reference], [range of cells blank between reference and next serial number in column A], [column Q between current vehicle and next vehicle] ))=sum(offset( [sheet1 serial number reference], [range of cells blank between reference and next serial number in column A],[column Q between current vehicle and next vehicle] )), "M", "S")

I'm thinking that looking for data in column A is the key here. I just don't know how to have excel start/stop an array between non-empty cells in column A and select only the range of numbers pertaining to that vehicle.
 

Attachments

  • Sample2.xlsx
    76.3 KB · Views: 5
@JTsoldier

Wow, that is one monster formula you got there. I am not really sure if I understood what the exact purpose of the formula you wrote is. Also, I couldn't find any references to Sheet2. What is the purpose of that data?

Assuming you just want to add up column P:Q and compare that with K:N for each serial number and then decide whether it is "S" or "M", the attached workbook should help. Please note that my answers do not match with your solution. I guess this is because either I do not understand the logic 100% or you got the formula wrong. Let me know.
 

Attachments

  • lookup-grouped-data-JTS.xlsx
    77 KB · Views: 4
Sheet 1 of your workbook is output from a data source that is "Excel Ready", although I find that description particularly misleading. In truth, it's a report that is spit out into an Excel Worksheet merely for printing purposes - manipulating the data in Excel is a chore.
Can you get the source program to give you more of a dataset? Then you can truly use Excel's strengths to the max. Another thought is to use PowerPivot to access the source data ....
 
I can run the report with data only but it still look the same but shows the grid lines and doesn't have any merged cells. I will still have the problem of trying to only count up the sums between vehicles.

I've never heard of power pivot. I will check it out and see if I can make it apply.
 
Last edited:
JTsoldier said:
I can run the report with data only but it still look the same but shows the grid lines and doesn't have any merged cells. I will still have the problem of trying to only count up the sums between vehicles.

I've never heard of power pivot. I will check it out and see if I can make it apply.
Click to expand...

I suspect you'll find a relational database behind the report you're exporting - ideally if you can connect PowerPiovt to some of the tables you will be well on your way to solving your issue. When you link PowerPivot to the individual tables you will be able to create a true Excel output that you can manipulate with formulas, functions and possibly use Pivot Tables to solve your challenge. You've just made it infinitely more complex by having this intermediate "vehicle" of an Excel Report be you starting point.

FYI PowerPivot allows you to link to various Data sources and combine them behind the scenes to feed your data as you require. Once you've identified the individual tables in your source data, bring some cleansed examples back here and I've no doubt that you'll get some assistance from the Ninjas and their devotees!
 
You must log in or register to reply here.
Back
Top