Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 8th 18, 12:04 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 146
Default Counting & Summing Multiple criteria over variable row range

' Hi Team

' Have I got a challenge for you'll.. LOL.

' If it is at all acheivable, I need to group blocks of time, then
' with each block there is multiple myUnitRng which could have 2 drop point at the same myDestRng.

' The range can vary anything from 100 to 250 rows and the time will vary from 00:00 up to 23:30 ( increments of 30 minutes )

' What I would like if possible:
' 1. Match myUnit where myDestRng = "HDC" within myTimeRng time bracket:
' 2. Match myUnit where myDestRng = "RDC" within myTimeRng time bracket:

' < 04:00:00
' 04:01:00 < 09:00:00
' 09:01:00 < 12:00:00
' 12:01:00 < 15:00:00
' 15:01:00 < 23:59:00

' Once this match has been made:
' 1. myPreloads.value = Row.Count(myUnit, myDestRng, myTimeRng) along with the other myTimeRng for: my0409Loads, my0912Loads Etc...
' 2. myPreWoods.value = Sum(Row.Count(myUnit, myDestRng, myTimeRng, myPreWoods)) along with the other myTimeRng for: : my0409Loads, my0912Loads Etc...

' And this is where it gets just a tad more challenging:

' I need to also, whilst matching each calculation within each time bracket SUM(myWoodsRng) any matching myDest = "RDC"
' it will be myPreWoods.offset(0, 5).value = Sum(Row.Count(myUnit, myDestRng, myTimeRng, myPreWoods))

' I look forward to your thoughts and suggestions

' As always, many thanks in advanced

' Cheer
' Mark.

Here is a snippet of the pseudo code I started, as I, and not surprising, you can imagine it requires a ton of reworking to get it to do it's thing:

Sub Convert_MRDC_1()

Dim sMain As Worksheet
Dim myPreLoads As Range, my0409Loads As Range, my0912Loads As Range, my1215Loads As Range, my1500Loads As Range, myTotalLoads As Range
Dim myPreWoods As Range, my0409Woods As Range, my0912Woods As Range, my1215Woods As Range, my1500Woods As Range, myTotalWoods As Range
Dim myTimeRng As Range, myDestRng As Range, myWoodSumRng As Range, myUnitRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

Set sMain = Sheets("Main")
Set myTimeRng = sMain.Range("N2:N250" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).S elect
Set myDestRng = sMain.Range("F2:F250" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).S elect
Set myWoodSumRng = sMain.Range("I2:I250" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).S elect
Set myUnitRng = sMain.Range("I2:I250" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).S elect

NextFreeLoads = Range("G2:G" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).R ow
NextFreeWoods = Range("I2:I" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).R ow

Set myPreLoads = Range("G" & NextFreeLoads + 3)
Set my0409Loads = Range("G" & NextFreeLoads + 4)
Set my0912Loads = Range("G" & NextFreeLoads + 5)
Set my1215Loads = Range("G" & NextFreeLoads + 6)
Set my1500Loads = Range("G" & NextFreeLoads + 7)
Set myTotalLoads = Range("G" & NextFreeLoads + 9)

Set myPreWoods = Range("I" & NextFreeWoods + 3)
Set my0409Woods = Range("I" & NextFreeWoods + 4)
Set my0912Woods = Range("I" & NextFreeWoods + 5)
Set my1215Woods = Range("I" & NextFreeWoods + 6)
Set my1500Woods = Range("I" & NextFreeWoods + 7)
Set myTotalWoods = Range("I" & NextFreeWoods + 9)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Cells with Multiple Range Criteria (Excel 2003) Jennifer Excel Discussion (Misc queries) 7 November 30th 09 05:10 PM
Summing/Counting Data in One Column Based on Criteria Another Col agilek9 Excel Worksheet Functions 2 November 6th 08 10:24 PM
Counting cells in a range per multiple criteria . . . Dano Excel Worksheet Functions 9 May 19th 08 05:28 PM
Counting from one range to another range, multiple criteria macamarr Excel Discussion (Misc queries) 3 June 10th 06 11:02 AM
Counting & Summing based on criteria on another column Chicago D Excel Discussion (Misc queries) 2 August 25th 05 06:58 PM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017