How to find non-duplicated dates (VBA)

[bukimi]

Hello!

I have a product report that has some columns - one of them is date: one of two specified (let's say that it's always either 1st January or 9th January). Among all products there are few that always have only one date assigned, but still appear many times in the report (because other columns still have different data for them).


Example data:
PRODUCT NAME | DATE..... | CLIENT | ITEMS SOLD
Apple...............|1-01-2019| Shop X | 10
Apple...............|9-01-2019| Shop X | 36
Banana............|9-01-2019| Cafe Y..| 42
Orange............|1-01-2019| Shop Y.| 31
Apple..............|1-01-2019| Club A..| 12
Orange............|9-01-2019| Club A..| 48
Banana............|9-01-2019| Shop X..| 2

I need to find which products appear only with one of two dates and, preferably, delete rows that refer to these found products.
In example data I need to find that Banana was sold only on 9th January and never 1st January, so I need to delete all rows that refer to it.

Is there any way to do it by VBA? The only way I can think of is to add a helper pivot table and then check which product has only 1 row of data, but it's a long workaround.

Thank you in advance for your tips!

 

[sergioMabres]

Hi Bukimi,
You could add a column to the right of ITEMS SOLD with the Colheading DELETE, let us say column E, and in E2 use this formula

[COLOR=#444444][FONT=Calibri]=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)-1[/FONT][/COLOR]

If you get a cero do not delete, if you get a 1 delete or hide that row

PRODUCT DATE     CLIENT      ITEMS SOLD    DELETE
Apple    01/01/2019     Shop X     10    0
Apple    09/01/2019     Shop X     36    0
Banana    09/01/2019     Cafe Y..    42    0
Orange    01/01/2019     Shop Y.    31    0
Apple    01/01/2019     Club A..    12    0
Orange    09/01/2019     Club A..    48    0
Banana    09/01/2019     Shop X..    2    1

Cheers
Sergio

 

[bukimi]

Hi Bukimi,
You could add a column to the right of ITEMS SOLD with the Colheading DELETE, let us say column E, and in E2 use this formula

[COLOR=#444444][FONT=Calibri]=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)-1[/FONT][/COLOR]

If you get a cero do not delete, if you get a 1 delete or hide that row

PRODUCT DATE     CLIENT      ITEMS SOLD    DELETE
Apple    01/01/2019     Shop X     10    0
Apple    09/01/2019     Shop X     36    0
Banana    09/01/2019     Cafe Y..    42    0
Orange    01/01/2019     Shop Y.    31    0
Apple    01/01/2019     Club A..    12    0
Orange    09/01/2019     Club A..    48    0
Banana    09/01/2019     Shop X..    2    1

Cheers
Sergio

I don't know if your example has a misspell on row 3 or if it's not working properly (I don't have my real data to test it now).
I can see that row 3, that is first occurence of "Banana" has 0 in Delete column. I need to delete all "Banana" if it's the product that has only 1 different date (in example only 9th January).

EDIT: Nevertheless, it's still useful as it identifies the product. I can refer to column A (name) where Delete column has value 1.

 

[sergioMabres]

You are right, the formula only finds the 2nd, 3rd, and so on occurrence of the same fruit and date, but not the first and you want to delete the first too, I may be able to modify the formula to find the first too

=COUNTIFS(A:A,A2,B:B,B2)-1

Here it is now the formula gets both bananas

PRODUCT NAME	DATE.....	CLIENT	ITEMS SOLD	DELETE
Apple...............	1/1/2019	Shop X	10	0
Apple...............	9/1/2019	Shop X	36	0
Banana............	9/1/2019	Cafe Y..	42	1
Orange............	1/1/2019	Shop Y.	31	0
Apple..............	1/1/2019	Club A..	12	0
Orange............	9/1/2019	Club A..	48	0
Banana............	9/1/2019	Shop X..	2	1

<tbody>
</tbody>

Cheers
Sergio

 

[bukimi]

Sorry for late feedback, but I had doctor's leave, so couldn't test it earlier.

It seems that both previous and this formula give me different results and neither of them are similar to your results (and solution).
For
=COUNTIFS($A$1:A2,A2,$B$1:B2,B2)-1
I get
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1

[/TD]
[/TR]
</tbody>[/TABLE]

And for
=COUNTIFS(A:A,A2,B:B,B2)-1

I get
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

For real data it gave me results ranging from 0 to 244. One of products that had 1 as a result was really a unique solution, but second one with 1 wasn't (had 2 different dates).

 

[bukimi]

I can't edit my post anymore, so I'll add my temporary solution:
1. I add pivot table with “Add this data to the Data Model” (can't do it in VBA unfortunately).
2. In that pivot table I look for unique days for each product (it's either 1 or 2), using "Distinct values" instead of "Count".
3. I add a new column to original sheet that by VLOOKUP adds results from pivot table to all rows.
4. I run a macro to delete all rows that have "1" in helper column.
Steps 1-2 I do manually, steps 3-4 by VBA, so it still saves lots of time.

 

[sergioMabres]

So we are using a different set of data, or a different program, very weird
Sergio

 

[bukimi]

I can't upload images anywhere from my workplace (blocked), but I'll make a screenshot of finished table (with example data) from home.
Should I move the formula down or use the exact same with same cell address for every row?

 

[sergioMabres]

The formula is only for row 2, for column C, D, ... and further, not to be written in columns A nor B
the formula that you should use for row 2 is =COUNTIFS(A:A,A2,B:B,B2)-1
you can copy de formula down, when you copy it down the formula will change, for instance in row 5 it will be =COUNTIFS(A:A,A5,B:B,B5)-1
Cheers
Sergio

 

[bukimi]

The formula is only for row 2, for column C, D, ... and further, not to be written in columns A nor B
the formula that you should use for row 2 is =COUNTIFS(A:A,A2,B:B,B2)-1
you can copy de formula down, when you copy it down the formula will change, for instance in row 5 it will be =COUNTIFS(A:A,A5,B:B,B5)-1
Cheers
Sergio

Yeah, I'm doing it the right way, getting correct rows, columns, etc. I use the same example table that I posted here. I don't know how we can get different results. I'll make a screenshot some time later and post it here.