Hello!
I have a database, which is far too big and it can be compacted, because one column is not needed, but I need to sum the values in some other columns in it to delete whole rows.
Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Product_ID[/TD]
[TD]Buyer[/TD]
[TD]Date[/TD]
[TD]Items[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]12340[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]22415[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]31911[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]7[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]
From these 3 rows I need only 1. I can leave them all, but these duplicates make my file too big for some purposes.
One, final (compressed) row would look like this:
Apples | 12340 | CompanyA | 03-10-2017 | 12 | 81
One column (Product ID here) is meaningless actually and it can take value of any of compressed rows, first one, or any of them.
Two columns have values that need to be added into a sum for a new row (here: 3 + 2 + 7 for Items and 9 + 12 + 60 for Value).
Sometimes there are 2 duplicate rows, sometimes even 15 or more.
They have all columns the same, except one and of course values.
I can add it by comparing every cell with "If" statement in VBA...
... but I have many columns (A-O) and that "If" statement would be very big, too Any way to compress these columns by other means? Maybe even other than VBA?
I can't just use Pivot Table, because I need that database in this exact format unchanged, not only a sum of Items and Value.
Thank you in advance for your help.
I have a database, which is far too big and it can be compacted, because one column is not needed, but I need to sum the values in some other columns in it to delete whole rows.
Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Product_ID[/TD]
[TD]Buyer[/TD]
[TD]Date[/TD]
[TD]Items[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]12340[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]22415[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD]31911[/TD]
[TD]CompanyA[/TD]
[TD]03-10-2017[/TD]
[TD]7[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]
From these 3 rows I need only 1. I can leave them all, but these duplicates make my file too big for some purposes.
One, final (compressed) row would look like this:
Apples | 12340 | CompanyA | 03-10-2017 | 12 | 81
One column (Product ID here) is meaningless actually and it can take value of any of compressed rows, first one, or any of them.
Two columns have values that need to be added into a sum for a new row (here: 3 + 2 + 7 for Items and 9 + 12 + 60 for Value).
Sometimes there are 2 duplicate rows, sometimes even 15 or more.
They have all columns the same, except one and of course values.
I can add it by comparing every cell with "If" statement in VBA...
Code:
For i = 2 To NumberOfRows
If .Cells(i, 1) = .Cells(i - 1, 1) And .Cells(i, 3) = .Cells(i - 1, 3) And .Cells(i, 4) = .Cells(i - 1, 4) Then
.Cells(i - 1, 5).Value = .Cells(i - 1, 5).Value + .Cells(i, 5).Value 'adds items from duplicate row to row above
.Cells(i - 1, 6).Value = .Cells(i - 1, 6).Value + .Cells(i, 6).Value 'adds value from duplicate row to row above
Rows(i).EntireRow.Delete 'deletes duplicated row
i = i - 1 'moves loop one item back to avoid skipping a row after deleting it
End If
Next i
I can't just use Pivot Table, because I need that database in this exact format unchanged, not only a sum of Items and Value.
Thank you in advance for your help.