Sum if criteria is the same in 2D Array

[Andrei]

Hi,

I have a VBA problem which i cannot solve: I have a 2D array with values and the values from some columns are the same while the amounts are different. In this case i want to sumif the amounts and display the rest of values once (as a single array element with the whole amount).

I attached the file and also posted my code bellow:

 Sub Macro1()

lastrow = Sheet1.Range("E3").End(xlDown).Row

Dim array1() As Variant
Dim optiune As String

If Sheet1.OptionButton1.Value = True Then
      optiune = "yes"
Else: optiune = "no"
End If

arrlen = Application.WorksheetFunction.CountIf(Sheet1.Range("H2:H" & lastrow), optiune)

ReDim array1(arrlen - 1, 3)

Sheet1.Range("K5:N100000").ClearContents

r = 0
For i = 1 To lastrow
    If Sheet1.Cells(i, 8) = optiune Then
    array1(r, 0) = Sheet1.Cells(i, 5)         'nr factura
    array1(r, 1) = Sheet1.Cells(i, 6)         'client
    array1(r, 2) = Sheet1.Cells(i, 4)         'intarziere
    array1(r, 3) = Sheet1.Cells(i, 7)         'suma
    r = r + 1
    End If
Next i

Sheet1.Range("K5" & ":" & "N" & arrlen + 4) = array1

Thanks a lot !



End Sub

 

[Hui]

Have you considered doing this with Formula instead of VBA?
Luke published a sample of how to achieve this here:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[Luke M]

Hi Andrei,

Take a look at the attached. I ended up abandoning the macros and going with just a formula approach.

 

[Andrei]

Hello,

Thanks a lot, but i would prefer if it's possible to do this with VBA (to upgrade my code) because:

- Array formulas (especially for index and match) are very slow if i have for example 50000 rows. In the sheet it is just a sample of my data, in reality it can be 50000 rows and 30 columns to right.

- I want to put multiple sorting options like user will input start date and end date in some Activex Controls (textbox) and he will get only the invoices with those criterias (and i don't want to use excel filter). He will also have a check box and if that will be checked he will get the paid invoices only from the chooses interval for pay date. (so the Activex Textboxes will be in some cases the issue date interval and in other cases the payment date interval)..

So, could you please help with upgrading the VBA code ?

PS: please note that the "arrlen" variable from my code is not needed. We can use the "r" from the loop instead of it to get the array rows size.. But how can we sumif the duplicates and remove from the array the items with 0 values.. and then get the new correct array size ? this is the problem

Thanks a lot !
Andrei

 

[Luke M]

One more idea then, before tackling VB (which will take a bit). If you're doing intensive data summation, this is easily done with a PivotTable, as shown in attached. You can even use slicers to still give users a nice "feel" of selecting buttons, dashboard style.

We are not meaning to be obstinate, just want to present alternative ideas that may work better in the long run.

 

[Andrei]

Hello,

It can be a solution, but there are a few disadvantages:
- i cannot add new sorting criterias like the user to get only the invoices from desired date interval. He cannot sort by amount or by "intarziere" column (that means: payment delay)

And sincearly, it is also a VBA challenge. I can't work with arrays in a good manner and i want to learn more about this stuff.

Thanks

 

[Luke M]

Actually, you can add both of those things to a PivotTable. You can use the filter dropdowns to sort on columns as you stated.

If you still want to stick with VB, I would suggest building a dictionary/collection of invoice numbers. Dictionary's would let you check if a key (invoice number) already exists in the list, and you could then add the value to the key's item (the sum). Once you have dictionary built, you can transfer the keys and items into arrays, and paste that into an XL sheet.
http://www.cpearson.com/excel/CollectionsAndDictionaries.htm

 

[jeffreyweir]

Adding to what Luke has said - but without actually looking at your sample data or analysing your problem in great detail - I'd add that while I use Dictionaries all the time, I go with a PivotTable any time that PivotTable can do what the Dictionary is doing.

Getting dictionaries to do stuff gets damn difficult...especially when your dictionary must contain another dictionary. It's really tricky to get your head around...and damn hard for others who might inherit your code to understand what's going on.

Plus there are limitations in regards to how much data you can quickly transfer out of a Dictionary.

See my post at http://dailydoseofexcel.com/archives/2013/10/23/dictionaries-can-be-rude/ for more on this, as well as to see some great links to other articles.

 

[Marc L]

Hi !

Excel Transpose function is limited to 65 536 rows
but can be overpassed by using an external object,
tip already posted in this thread !

Other tip to speed up Index function : give it only a pair of rows !
​