How to consolidate this?

[Romano_odK]

Good morning,
Can you help me with the following please. I Have a few thousand lines like these and would like to consolidate the amounts so I get only one line. The best thing would be to sum the amounts and then delete the rest of the lines, but I do not know what would be possible. Thank you for your help and time.

artcode	ItemDescription	datum	oms25	aantal	Created	bkstnr
133141	Kunststof Rasterblok 32x2 mm blauw - 100 stuks	07/08/2023 00:00	Telling 10/08/2023, difference of: 300,0000 for item: 19499	300	07/08/2023	23359157
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -208,0000 for item: 19500	-208	10/08/2023	23360632
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 7,0000 for item: 195009	7	10/08/2023	23360672
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 100,0000 for item: 195009	100	10/08/2023	23360670
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 100,0000 for item: 195009	100	10/08/2023	23360671
195003	Beschermfolie 100x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -283,0000 for item: 19500	-283	10/08/2023	23360657
195003	Beschermfolie 100x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 284,0000 for item: 195003	284	10/08/2023	23360676
195004	Beschermfolie 125x0,05 mm - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -13,0000 for item: 195004	-13	10/08/2023	23360643
195004	Beschermfolie 125x0,05 mm - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 13,0000 for item: 195004	13	10/08/2023	23360677

 

[Larry Haydn]

Good morning,
Can you help me with the following please. I Have a few thousand lines like these and would like to consolidate the amounts so I get only one line. The best thing would be to sum the amounts and then delete the rest of the lines, but I do not know what would be possible. Thank you for your help and time.

artcode	ItemDescription	datum	oms25	aantal	Created	bkstnr
133141	Kunststof Rasterblok 32x2 mm blauw - 100 stuks	07/08/2023 00:00	Telling 10/08/2023, difference of: 300,0000 for item: 19499	300	07/08/2023	23359157
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -208,0000 for item: 19500	-208	10/08/2023	23360632
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 7,0000 for item: 195009	7	10/08/2023	23360672
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 100,0000 for item: 195009	100	10/08/2023	23360670
195009	Beschermfolie 1000x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 100,0000 for item: 195009	100	10/08/2023	23360671
195003	Beschermfolie 100x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -283,0000 for item: 19500	-283	10/08/2023	23360657
195003	Beschermfolie 100x0,05 mm blauw - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 284,0000 for item: 195003	284	10/08/2023	23360676
195004	Beschermfolie 125x0,05 mm - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: -13,0000 for item: 195004	-13	10/08/2023	23360643
195004	Beschermfolie 125x0,05 mm - 100 m	10/08/2023 00:00	Telling 10/08/2023, difference of: 13,0000 for item: 195004	13	10/08/2023	23360677

Which columns do you wish to total up? Aantal, or something inside oms25?

 

[Romano_odK]

Which columns do you wish to total up? Aantal, or something inside oms

That would be "Aantal".

 

[Larry Haydn]

Do you need to sum the rows based on some criteria?

 

[mumps]

The best thing would be to sum the amounts and then delete the rest of the lines

There are 4 rows of data for the artcode 195009. My assumption is that you would want to sum the four values for these four rows in the aantal column. If this is correct, which of the four rows do you want to keep?

 

[Romano_odK]

There are 4 rows of data for the artcode 195009. My assumption is that you would want to sum the four values for these four rows in the aantal column. If this is correct, which of the four rows do you want to keep?

Do you need to sum the rows based on some criteria?

Good afternoon,
Leading for me are 'artcode' and 'Itemdescription', next to that I would only like to SUM "aantal". The rest is not important., Thank you for your time.

 

[mumps]

Leading for me are 'artcode' and 'Itemdescription'

I'm not sure what you mean by this. Do you want to sum based on 'artcode' only? This would mean that the sum for 195009 would be -1. Is this correct? If not, please clarify in detail what the sum for 195009 would be. Also, where do you want to place the sum?

I Have a few thousand lines

Would the 'artcode' values always be grouped together, one underneath each other, or could they be anywhere on the sheet? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Romano_odK]

I'm not sure what you mean by this. Do you want to sum based on 'artcode' only? This would mean that the sum for 195009 would be -1. Is this correct? If not, please clarify in detail what the sum for 195009 would be. Also, where do you want to place the sum?

Would the 'artcode' values always be grouped together, one underneath each other, or could they be anywhere on the sheet? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

It should look like this: Itemcode, description and amount, in this case -1.

195009	Beschermfolie 1000x0,05 mm blauw - 100 m -1

 

[mumps]

Try:

Sub Consolidate()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, fVisRow As Long, lVisRow As Long, total As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2", Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        For i = LBound(v) To UBound(v)
            If Not .exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                Range("A1").CurrentRegion.AutoFilter 1, v(i, 1)
                total = WorksheetFunction.Sum(Range("E2:E" & lRow).SpecialCells(xlVisible))
                fVisRow = Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
                lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
                Range("B" & fVisRow) = Range("B" & fVisRow) & " " & total
                If [subtotal(103,A:A)] - 1 > 1 Then
                    Rows(fVisRow + 1 & ":" & lVisRow).Delete
                End If
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Romano_odK]

Try:

Sub Consolidate()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, lRow As Long, fVisRow As Long, lVisRow As Long, total As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v = Range("A2", Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        For i = LBound(v) To UBound(v)
            If Not .exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                Range("A1").CurrentRegion.AutoFilter 1, v(i, 1)
                total = WorksheetFunction.Sum(Range("E2:E" & lRow).SpecialCells(xlVisible))
                fVisRow = Rows("2:" & lRow).SpecialCells(xlCellTypeVisible).Row
                lVisRow = Cells(Rows.Count, "A").End(xlUp).Row
                Range("B" & fVisRow) = Range("B" & fVisRow) & " " & total
                If [subtotal(103,A:A)] - 1 > 1 Then
                    Rows(fVisRow + 1 & ":" & lVisRow).Delete
                End If
            End If
        Next i
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Good afternoon . I get an error Object library invalid or contains references to object definitions that could not be found. Any idea what this might be?