EXCEL VBA to find duplicate rows and sum rows

Question

enter image description here

There are 10,000+ Rows with Vendor and Vendor payment information. If there are duplicates based on fields in column A, C, D, F, G, H, then I want to sum the amounts in column K and L and just keep 1 entry/row for it.

Bottom picture is the result.

I want to use the dictionary script if possible but i don't know how to use it. It just seems efficient, if not, different method is fine too. Can anyone help????? I would greatly appreciate it!!

This isn't much of an answer, but... This is what a database is designed to do. Do you have Access available that you can import this and do SELECT Number,UpDown,FYE,GL_SEG_CD,ACCTNUMBER,CODE,sum(Amount1), sum(amount2) FROM table GROUP BY Number,UpDown,FYE,GL_SEG_CD,ACCTNUMBER,CODE? Doing this with VBA is possible, but ugh... the writing and debugging and slowness and unexpected wonkiness... it's a recipe for hairloss and heartburn. — JNevill
– JNevill, Commented Oct 8, 2014 at 21:00
I want to keep it in EXCEL format because if I use Access, there's a lot of data manipulation involved. If I do EXCEL, I can just automate everything. :\ — Flyhigh
– Flyhigh, Commented Oct 8, 2014 at 21:03
See the PIVOT TABLE answer below. You don't want to overwrite your data in the sheet. A pivot table will draw from that table as a source and can be refreshed. SQL works great, but it is challenging to implement in Excel. Pivot tables are built in. — SandPiper
– SandPiper, Commented Dec 21, 2018 at 12:51

JNevill · Accepted Answer · 2014-10-10 17:48:31Z

1

This one is fun, so I wrote it out. Essentially this script will treat your wsFROM as if it were a database table, then it will apply the SQL statement to it to do what you need and return the results to wsTO. It should run pretty quickly even though it's 20000 records. The alternative would be to loop through every row in the sheet and then look to see if there's others like it and add the results. It would be one of those 20 minute running nightmare VBA routines, where this should run in a few dozen seconds.

Make sure you change the SET wsFROM... and Set wsTO... lines to be your worksheets. Other than that, I believe it shouldT work with your data as is.

Sub excelSQL()

    'Variables
    Dim objConn As Object
    Dim rs As Object
    Dim strFileName As String, strSQL As String, strConn As String
    Dim wsFrom As Worksheet, wsTo As Worksheet

    'set the worksheet with the data and the worksheet to dump the results
    Set wsFrom = Worksheets("Sheet1")
    Set wsTo = Worksheets("Sheet2")

    'Make a new file with _tmp appended to it in same folder
    strFileName = ThisWorkbook.Path & "/" & ThisWorkbook.Name
    strFileName = Replace(strFileName, ".xls", "_tmp.xls")        
    ThisWorkbook.SaveCopyAs strFileName

    'Fancy ADODB stuff.. essentially open up that copy we just saves as if it were a database
    Set objConn = CreateObject("ADODB.Connection")
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
    objConn.Open strConn        

    'This is the sql string we will use to query the excel/database. The field names are the names in Row 1 of the sheet
    strSQL = "SELECT [Number],[NAME],[Up/Down],[FYE],[GL_ACCT_GRP],[GL_SEG_CD],[ACCT#],[CODE],[REGION],Sum(Amount1), Sum(Amount2) FROM [" & wsFrom.Name & "$] GROUP BY [Number],[NAME],[Up/Down],[FYE],[GL_ACCT_GRP],[GL_SEG_CD],[ACCT#],[CODE],[REGION];"

    'Fancy ADODB stuff to apply that SQL statement to the excel/database
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, objConn

    'get headers
    Dim header as object
    Dim headerCol as integer
    headerCol = 1        
    'Loop through fields in the recordset
    For each header in rs.fields
        'Stick them in Row 1
        Cells(1, headerCol).value = header.name
        'Next Column
        headerCol = headerCol + 1
    next header

    'Copy the results of the sql statement, stored in the recordset, into the To sheet
    wsTo.Range("A2").CopyFromRecordset rs

    'close it up
    rs.Close
    objConn.Close

    'Remove that tmp workbook 
    Kill strFileName

End Sub

edited Oct 10, 2014 at 17:48

answered Oct 8, 2014 at 21:31

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Flyhigh Over a year ago

I think this worked. I will let you know when I run the actual file. I have another question, the amount totals goes all the way to column 'BF'. So I have column 'K' thru column 'BF' which is 48 columns I need to the code to sum based on the criteria. I was going to mod it but I've never done SQL for EXCEL lol. Impressive stuff. Thank you in advance.

JNevill Over a year ago

Each header cell (in Row 1) of your sheet acts as the field in your Excel/Database table. The SQL here has three clauses: SELECT where you list the fields that you wish to have returned (and sum the ones you wish to sum), FROM where you just name the sheet that is acting as the table, and GROUP BY where you list the same fields/headers that you had in the SELECT except for the ones you are summing (because the SUM() essentially acts as the grouping. If you get stuck just toss the code in a new question and a list of your headers with the SQL and folks should be able to help out.

JNevill Over a year ago

As an aside, the fact that you can apply SQL against your data in excel is stupid powerful. For instance, if you wanted to not only sum this stuff up but you also only wanted rows where the Acct# was between 5000 and 5050 you could add a WHERE clause after the FROM clause and before the GROUP BY that looks like WHERE [ACCT#] BETWEEN 5000 AND 5050

JNevill Over a year ago

It will stick a number after the second one. So the first Amount1 column will be [Amount1] and the second will be [Amount11]... although that could get confusing if there is already an Amount11 column which would probably make the second Amount1 column be [Amount12] and so on... Might be better off making sure the names are unique.

Flyhigh Over a year ago

So I gave a unique name for each of the columns - (there are 48) it does not let me add them in the coding – I don't think I can add all 48 names in the strSQL string? Is there another way to do this? I have to sum up all 48 columns for each row. Sorry for keep bugging you.

|

steegness · Accepted Answer · 2014-10-08 21:47:06Z

1

A pivot table will get you pretty close. With the old-style layout ("Show In Tabular Form") you can set A, C, D, F, G, H as rows and then sum K and L.

A copy-paste values later, and you've got your skeleton: you'd need to duplicate values into the blank spaces below them to fill it out (which might be a manual pain, or an easier lift in VBA at least).

answered Oct 8, 2014 at 21:47

steegness

4591 gold badge4 silver badges10 bronze badges

Comments

pnuts · Accepted Answer · 2014-10-09 00:13:48Z

0

Create a key from A&C&D&F&G&H, sort on it and subtotal (sum) K & L for each change in the key.

answered Oct 9, 2014 at 0:13

pnuts

59.8k11 gold badges93 silver badges141 bronze badges

Collectives™ on Stack Overflow

EXCEL VBA to find duplicate rows and sum rows

3 Answers 3

14 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related