SUMPRODUCT help

[JamesonMH]

Hi there,

I've exhausted my options and really need some help with this.

I'm dealing with a fixed payroll table where the column headers duplicate across (but the data is different). Given a bi-weekly income, I want to calculate what the CPP amount is. For example, if income was $2,011.45, then CPP should be $92.90 since it falls between $2,011.28-2,011.47. Obviously if it were just the 3 columns it would be easy, but that's not how the payroll table is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bi-weekly income[/TD]
[TD][/TD]
[TD][/TD]
[TD]Bi-weekly income[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]To[/TD]
[TD]CPP[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]CPP[/TD]
[/TR]
[TR]
[TD]$1,996.33[/TD]
[TD]$1,996.52[/TD]
[TD]$92.16[/TD]
[TD]$2,010.88[/TD]
[TD]$2,011.07[/TD]
[TD]$92.88[/TD]
[/TR]
[TR]
[TD]1,996.53[/TD]
[TD]1,996.73[/TD]
[TD]92.17[/TD]
[TD]2,011.08[/TD]
[TD]2,011.27[/TD]
[TD]92.89[/TD]
[/TR]
[TR]
[TD]1,996.74[/TD]
[TD]1,996.93[/TD]
[TD]92.18[/TD]
[TD]2,011.28[/TD]
[TD]2,011.47[/TD]
[TD]92.90[/TD]
[/TR]
[TR]
[TD]1,996.94[/TD]
[TD]1,997.13[/TD]
[TD]92.19[/TD]
[TD]2,011.48[/TD]
[TD]2,011.68[/TD]
[TD]92.91[/TD]
[/TR]
</tbody>[/TABLE]

I've tried some INDEX/SUMPRODUCT/MIN/IF/ROW arrays but no luck so far.

Please help!

James

 

[JamesonMH]

Well,

With 35 individual worksheets showing 12 Columns each ... you are talking about an extremely sophisticated ' Payroll ' system ...!!!

For sure, it will be much safer to build a reference database ...!!!

I don't disagree one bit. The source data tables were just so structured (and Excel's admittedly my main weapon) that I've been determined to find an Excel solution.

But I'm now intrigued on a more permanent/LT solution. Note, I'm not attempting to build a payroll system out of Excel. Luckily, that's already established. But I'm doing employee cost analysis where quick retrieval of this (CPP) and other gov't rates are needed (all via similar tables/structured sources).

Would Access be sufficient for housing tables of this size or something more adv? And would retrieval mean I could keep the source data as is (i.e. copy/paste into Access in the 12 column format) or would I have to clean them up beforehand?

Thanks for your time/help James!

 

[JamesonMH]

Let A1:F5 house the data with headers in A1:F1.

A8: $2,011.45

In B8 control+shift+enter, not just enter:

=MIN(IF(MOD(COLUMN($A$2:$E$5)-COLUMN($A$2:$A$5),3)=0,IF(A8>=$A$2:$E$5,IF(A8<=$B$2:$F$5,COLUMN($A$2:$E$5)))))

In C8 control+shift+enter, not just enter:

=VLOOKUP(A8,CHOOSE({1,2},INDEX($A$2:$F$5,0,B8),INDEX($A$2:$F$5,0,B8+2)),2,1)

Thank you Aladin - as always!

 

[James006]

Re,

Hopefully your Payroll table located in 35 different worksheets is not modified every other week ... :wink:

So, you could easily rely on a macro which would generate ONE Single Reference Table ... and then perform your employee cost basis from that starting point ...

 

[JamesonMH]

Re,

Hopefully your Payroll table located in 35 different worksheets is not modified every other week ... :wink:

So, you could easily rely on a macro which would generate ONE Single Reference Table ... and then perform your employee cost basis from that starting point ...

No reference tables wouldn't change. So macro and not an external database...I'll have to look into how to do that. Thanks

 

[James006]

Re,

Let's assume that on top of your 35 worksheets, you are adding a sheet and name it : Reference Table

If all your 35 worksheets are structured identically : 12 Columns starting in cell A1 ...

You could be testing the following macro :

Sub GenerateTable()Dim ws As Worksheet
Dim i As Long
Dim last As Long


  Application.ScreenUpdating = False
 ' Copy Titles once 
  Sheet1.Range("A1:C2").Copy Destination:=Sheets("Reference Table").Range("A1")
  ' Loop all 35 worksheets to build the reference table 
 For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "Reference Table" Then
      For i = 1 To 10 Step 3
         last = Sheets("Reference table").Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
         ws.Cells(3, i).Resize(4, 3).Copy Destination:=Sheets("Reference Table").Range("A" & last)
      Next i
    End If
  Next ws
  Application.ScreenUpdating = True
End Sub

Hope this will help

 

[JamesonMH]

Re,

Let's assume that on top of your 35 worksheets, you are adding a sheet and name it : Reference Table

If all your 35 worksheets are structured identically : 12 Columns starting in cell A1 ...

You could be testing the following macro :

Sub GenerateTable()Dim ws As Worksheet
Dim i As Long
Dim last As Long


  Application.ScreenUpdating = False
 ' Copy Titles once 
  Sheet1.Range("A1:C2").Copy Destination:=Sheets("Reference Table").Range("A1")
  ' Loop all 35 worksheets to build the reference table 
 For Each ws In ThisWorkbook.Sheets
    If ws.Name <> "Reference Table" Then
      For i = 1 To 10 Step 3
         last = Sheets("Reference table").Cells(Application.Rows.Count, "A").End(xlUp).Row + 1
         ws.Cells(3, i).Resize(4, 3).Copy Destination:=Sheets("Reference Table").Range("A" & last)
      Next i
    End If
  Next ws
  Application.ScreenUpdating = True
End Sub

Hope this will help

Wow - thanks so much for giving me this code James! I'm looking forward to trying this out. The only thing I wasn't clear on communicating is it's 35 pages yes (but of a single PDF publication). I was thinking of just copying the entire document into one worksheet and then pulling it into my new reference worksheet (so just 2 worksheets total). Would your code still work? I assume the looping portion would need to be tweaked? Also, what does the "Step 3" mean? (sorry VBA is a WIP for me!)

 

[James006]

Sorry ...

But my understanding was that you had a workbook ... with 35 Worksheets ... !!!

So the macro is probably totally useless ...

Unless your PDF conversion to Excel generates these 35 individual sheets ... :wink:

 

[JamesonMH]

Sorry ...

But my understanding was that you had a workbook ... with 35 Worksheets ... !!!

So the macro is probably totally useless ...

Unless your PDF conversion to Excel generates these 35 individual sheets ... :wink:

No, no it won't be useless at all. I'll do exactly that - throw them each into their own worksheet. It won't take long. I'm indifferent to which way the tables are stored, so that way I can use your code.

But what is "Step 3"?

 

[James006]

My recommendation would be to start by Step 1 ... :wink:

Below is a link to a macro which imports pdf to Excel :

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1101

Hope this will help

 

[JamesonMH]

My recommendation would be to start by Step 1 ... :wink:

Below is a link to a macro which imports pdf to Excel :

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1101

Hope this will help

Awesome - thanks again James, really appreciate it!