Remove duplicates in a column only when the values of the adjacent column are the same

Hello everyone.

I worked on a set of data, and I made specific sums with it.

The data is composed of a first column containing the name of the object, second containing the quantity of items for every object and a third column containing a batch number.

Now I have the sums in a column next to the data.

If there are 20 lines of data, there will be 20 lines of results, the sums.

The thing is that as I did sums of the quantity of objects by batch, I made groups of objects and added their quantities, my code to display the sums repeats the sums.

For exemple if i have :
5
7
3
5
6

and i'm adding the first 3 columns and the last 2, separately, the sums colums will be like that :

15
15
15
11
11

There are duplicates.

So I wrote this line in VBA in order to remove duplicates :

Please Login or Register  to view this content.

But I was wondering that if by any chance I had the same sum value for batch 1 and batch 2, this code would take the value of batch 2 as a duplicate and remove it.

So I'm asking : is there a way to remove duplicates only when the values of the cells in the adjacent column, the batch number column, are all the same ?
It will only remove the duplicates for the lines where the batch number is 1, then consider separately the lines where the batch number is 2 and remove the duplicates there and so on.
This way it won't remove the value of the sum of batch 2 even if it the same as that of batch 1, because the batch number will have changed.

Here is an Excel sample.

Thank you very much.

NON VBA solution.

D2=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)=1,1,"delete")


After that a pivot table of the data.


See the attached file.

Thank you very much for your quick answer.

I didn't understand what you did and your final result. Can you explain rapidly ?

If I wasn't clear I will repeat what I would like to have :
I made sums of some specific types of products only, not all. Here it is only the fruits. (apples, oranges and pears)
So I have these sums.

But the VBA code I have to make the sums displays the result of the sum in front of every type of object of the batch, either if it has been sumed or not.

If you have, in column (but I write in lines here), 5 apples, 6 oranges, 7 pears, and 2 cars in batch 1 and 8 apples, 9 oranges, 10 pears and 2 cars in batch 2, the results of the respective sums of batch 1 and batch 2 will be displayed on my spreadsheet as 18, 18, 18, 18 in front of each of the four lines of batch 1, and 27, 27, 27, 27 in front of each of the four lines of batch 2.

Then I use

Please Login or Register  to view this content.

to remove the duplicates and have 18, 27 only.

But my concern is that in the event of having by chance the same sum results for batch 1 and batch 2, for example 18, 18, 18, 18 for batch 1 and 18, 18, 18, 18 for batch 2, when I will use the macro to remove duplicates it will take out the second 18 as it is presented like a duplicate. Instead of having the correct answer, 18, 18, I will have only 18.

So my question was : can I remove duplicates depending on the value of the adjacent cell ? In my case, I only want to remove duplicates in the list of sums for batch 1 without looking at the next batches. And then to look independantly at batch 2 and do the same.

So is it doable to have a VBA code doing the following : "Look at the cell value in the batch column for the first line. Let's call it X. Then, remove duplicates in the sums column for all the lines below the first (first included) IF the cell value in the batch column in front of that sum equals X. Then look at the next line and do the same by comparing the first batch value and the batch value for that line. If they are the same, remove duplicates.
If they are not the same, take the new value as the new batch value and so on."

Feel free to tell me if you don't understand something in my explanations.

So my question was : can I remove duplicates depending on the value of the adjacent cell ? In my case, I only want to remove duplicates in the list of sums for batch 1 without looking at the next batches. And then to look independantly at batch 2 and do the same.

Batch 1, row 2 and row 8 are duplicated.
Row 2 is value 1 and is not duplicated on that moment.
Row 8 gives the result 2 and is a duplicated value.


That is also the result on my formula.



After that you can use a pivot table to analyse the data.


You expect 21 as result, which is also the result in column I / cell I25.


If the result should be differant, you have to add the expected results manualy in the file.

Why not just...

Please Login or Register  to view this content.

Or...Am I misunderstanding the requirement

That doesn't work. The code you gave removes duplicates within the 3 columns of the table. This is not what I want.
The thing I want is more complicated. I don't want to get rid of duplicates in the original data table, I want to get rid of duplicates in the sum column I put on the right which is made after operations on the original data.

I will explain once again the requirement.

I have a table with 3 columns
1 = Products
2 = Their quantity
3 = The batch they belong to

I made SUMS of SOME products, not all. Only the fruits.
And the sums are made by batch.
In my example, there are 3 batches.
All apples, oranges and pears where the batch column shows 1 are sumed up and I have a result.
All the apples, oranges and pears where the batch column shows 2 are sumed up and I have a second result.
All the apples, oranges and pears where the batch column shows 3 are sumed up and I have a third result.

The code I have to do the sums displays the sums in another column.
And this column has the same number of lines as the original data table.
So there are duplicates in the SUM COLUMN, and these are the duplicates I want to get rid of.

For example :

Products Quantity Batch

Apples 3 1

Oranges 10 1

Pears 1 1

Non fruit 2 1

Apples 5 2

Oranges 12 2

Pears 6 2

Non fruit 5 2

So here I have 2 batches. Each one contains 4 type of products.
What I do is this : I only sum up the fruits. And I do the sum for each batch.
The result for the sum of fruits of the first batch here is 14, and for the second it is 23.

Now : what my code for sums will display is this (as it is clearly shown on my excel sample sheet I posted) :

SUM COLUMN

14
14
14
14
23
23
23
23

As I have 4 type of products in each batch, the sum will be displayed in front of each line, of each product, whether it has been sumed up or not.
It is in THIS COLUMN and only in this column that I want to remove duplicates.

So I did this :

Please Login or Register  to view this content.

Because my sum column will always be in the column 13.

And for the example above, I will get this :

SUM COLUMN

14
23

Ok, I have 2 batches, I get 2 sum results, that's I wanted, it works, great.


BUT, there is a problem : if by chance the result of the first batch is let's say 20, and the result of the other batch is 20, this code will remove all the 20's including those pertaining to the second batch ! As it will be labeled as a duplicate.
And that's a problem.

To be clear I will explain it with an example (with the batch number between brackets) :

Instead of going from :
20 (1)
20 (1)
20 (1)
20 (1)
20 (2)
20 (2)
20 (2)
20 (2)

To :

20 (1)
20 (2)

which is I what I want, I will instead get :

20 (1)

and that's it because the second and different 20 is seen as a duplicate of the first one.

So my question is only "how to handle this exception, how to avoid results of other batches being mistakenly taken for duplicates of the same batch and removed ?"

My only problem is to avoid the removal of the sum of a batch if it equals to the sum of another batch. That's just that particulal case that I want to solve : the case when 2 or more sums happen to be the same, and if so, the removal of duplicates will also remove those sums and it's problematic.

What I do is this : I only sum up the fruits. And I do the sum for each batch.

This is a differant question.