Posts by soberguy

General Layout of Problem: We get a large file of data (in excess of 150K rows, 35 columns) that needs to be split out into ~40 individual files. This isn't really a problem, as I can set those recordsets and transfer to individual files just fine. But those new files need to include a summary - by month - of the data.

How would one go about summing the recordset?

To complicate matters further, I need to sum by two separate criteria (WHERE FIELD=''Yes' - and FIELD!=''Yes') - and I have to do those sums by a varying number of criteria for each recordset. At minimum, I'd have 6 SUM queries for each month, representing up to 72 SUM queries for the end of the year file. At maximum, I'd have 30 SUM queries for each month, representing up to 360 SUM queries for the end of the year file.

I can set up those queries no problem, I'm just curious if there's a better way than to establish a brand new recordset for each SELECT SUM query.

I'm already creating the recordset to move the data to a new file. Can I simply sum the recordset I've already created? If so, what's the best way to do that? And yes, I'm open to worksheet formulas or any other solution that one can think of.

Re: How to skip the Update Values screen and just return "blank"?

=Iferror(formula,"")

Re: Nested lookup tables with string criteria

In C3, paste this, and enter it as an array formula by using Control+Shift+Enter:
=SUM(IF(--LEFT($E$9:$E$20,6)=IF($B$9:$B$20=B3,$C$9:$C$20),$F$9:$F$20))

Copy and paste into C4.

Re: combine lookup and count no of occurences of a string that relate to the lookup v

=COUNTIFS(Table2[Name],[@Person],Table2[Fruit],"Apples")

Re: Sumproduct with multiple criteria

And as soon as I posted here, there was a solution at the other site:

I had posted:
=SUMPRODUCT((--(A2:A31=F8))+(--(A2:A31=F9))+(--(A2:A31=F10)),C2:C31)

Solution is:
Here's another way to write that:

=SUMPRODUCT(SUMIF(A2:A31,F8:F10,C2:C31))

Trying to sum payment data based on two criteria: A doc ID and a Job Number. Trouble is I where I want the formula is essentially a "roll up" row for individual data sets, meaning I can have multiple Doc IDs and Job Numbers. Sample Data:
[TABLE="class: grid, width: 260"]

DO

Job Number

Amount

14014555

H117GG10

75000

14012563

H1211L30

5000

14012563

H1211L30

5000

14012563

H1211L30

5000

14014288

H1177SLZ

905

14014286

H1177SLZ

3250

14014289

H1177SLZ

2845

14014288

H1177SLZ

700

14014286

H1177SLZ

6310

14014289

H1177SLZ

3240

14014288

H1177SLZ

835

14014289

H1177SLZ

2375

14014286

H1177SLZ

4495

14014289

H1177SLZ

3375

14014286

H1177SLZ

5055

14014288

H1177SLZ

680

14014289

H1177SLZ

2450

14014288

H1177SLZ

810

14014286

H1177SLZ

4085

14014288

H1177SLZ

875

14014289

H1177SLZ

2735

14014286

H1177SLZ

5110

14014288

H1177SLZ

405

14014286

H1177SLZ

5220

14014288

H1177SLZ

490

14014286

H1177SLZ

4215

14014289

H1177SLZ

2740

14014289

H1177SLZ

3165

14014288

H1177SLZ

1225

14014286

H1177SLZ

4895

[/TABLE]

Roll Up Examples:
[TABLE="class: grid, width: 453"]

Ref Doc ID

Job Number

Invoices Paid

See Detail

See Detail

1 (formula to go here)

NA

H1211800

0

14014555

H117GG10

75000

14012563

H1211L30

15000

See Detail

See Detail

2 (formula to go here)

14014286

H1177SLZ

42635

14014289

H1177SLZ

22925

14014288

H1177SLZ

6925

[/TABLE]

Ideally, I can have a simple formula with =IF(A2="See Detail",Sumproduct(This is where I need help),Sumproduct(--(Payments!DO=A2),--(Payments!JobNum=B2),Payments!Amount))

For the TRUE portion of the IF, I can set the dynamic range to be A3:A5 without issue. I just can't figure out how to make SUMIFS or SUMPRODUCT work with Payments!DO=A3:A5. It always wants to sum A3:A5, and I'm not sure how to tell it those are separate values.

Yes, it's all a bit convoluted, but the spreadsheet design is one I'm forced to use without changes, and the data sets are large enough that a one size fits all solution would be much better for me.

Cross posted here: http://www.excelforum.com/exce…g-to-nest-sumproduct.html

Re: AverageIfs formula and #DIV/0!

if(iserror(average_formula),0,average_formula)

Re: Determining if a datetime falls within start and end date times

PCI, I tested it and worked well for me. It returns the row number, which I'm sure he can then modify from there if needed. (if it returns >0, "yes", "no")

Re: permutations without repetitions listing

=fact(9)

Re: Determining if a datetime falls within start and end date times

Yeah, definitely a way to do that. Gimme a few hours, got several meetings today. Do you just need one match? What if it matches several times in A and B?

EDIT: Actually looks like PCI probably nailed it. Don't have time to verify, but it looks good in theory.

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

I wondered why you were running the SUMIF twice instead of once with a *2. Guess that explains it.

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

To answer for Jindon, .Value = .Value is much faster than copy paste. Functions the same, essentially, just much quicker.

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

Weird. I don't have any issues with xlCalculation, and all values are unique.

It may be quicker to use worksheet formulas in the code, rather than putting the formulas into the cells.

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

Odd. Hit F9. It's back in automatic mode, so it should be updated.

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

Application.Calculation = xlCalculateManual
'do all your stuff
Application.Calculation = xlCalculateAutomatic

Re: Determining if a datetime falls within start and end date times

It seems you want to compare to the entire list of times in A and B, and not just the times in that same row. Is that right?

Re: VBA Code -- Time Optimization --- Calculations between worksheets --- Arrays

I'd try turning on manual calculation, running the script, then turning it off. Excel is trying to calculate all those formulas as they're pasted.

Re: Remove leading number (9) in a column of numbers

=right(A1,7)

Re: Auto Populate troubles

You're storing the invoice number on the CO2 Log (Column C) as text, instead of a number. You can't match a number to a text string, unless you convert the number to a text string, and subsequently match text to text.

Change C to numbers instead of text.

Re: Trouble Looping through list to generate individual worksheets

It isn't the prettiest. A few notes: Change your save path, and set up your save name. Formulas will still refer back to your master sheet, so I'd probably change that, either by a pastespecial for values, or by populating the sheet with static data (it isn't too hard to fill the copy data with .offset(0,1)). Should get you going in the right direction though.

Sub ExportAsFile()
Dim SetupSheet As Worksheet, HomeSheet As Worksheet, Card As Worksheet
Dim MyArray() As String, a As Integer
Dim SavePath As String
Dim HomeRange As Range, MyRange As Range
Set SetupSheet = Sheets("Setup")
Set HomeSheet = Sheets("Home")
Set Card = Sheets("Preview Card")
Set HomeRange = HomeSheet.Range("D6")
SavePath = "SET UP YOUR PATH HERE"
With SetupSheet
    Set MyRange = .Range(.Range("B17").Address, .Range("B17").End(xlDown).Address)
End With
a = 0
For Each fcell In MyRange
   If fcell = "x" Then
   ReDim Preserve MyArray(a)
   MyArray(a) = fcell.Offset(0, 1).Value
    a = a + 1
    End If
Next fcell
For a = 0 To UBound(MyArray)
    HomeRange.Value = MyArray(a)
    Set NewBook = Workbooks.Add
    Card.Copy
    Card.SaveAs (SavePath & "FILENAME HERE")
Next a
End Sub