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!

 

[bukimi]

These are promised screenshots of my table. You may notice that there is different name of formula. It's Polish equivalent of Countifs and works the same way. We also use ; instead of , in formulas in Excel.
First screenshot is results in numbers, second is with "show formulas" view turned on. Below that is a fragment of PL-ENG translator of formulas.

 

[sergioMabres]

Hi Bukimi
As I thought, our data set is different
I see that:
Apple 01/01/2019 is twice that is why is turned on row 2 and 6
Banana 09/01/2019 is also twice that is why is turned on row 4 and 8
That is what the formula is doing what you asked: "I need to find which products appear only with one of two dates"
Rows 3, 5 and 7 are only once value 0
Rows 2, 4, 6, and 8 are twice
Cheers
Sergio

 

[bukimi]

I used the same table that I posted in my first post as example data with the same product names and dates. Therefore, I didn't expect anyone to use different set.

"I need to find which products appear only with one of two dates". Apple appears in database both for 1st and 9th of January, the same applies to Orange. Only banana "appears only with one of two dates" - meaning one date in whole database, regardless of other columns.

Thank you again for your help and effort. I'm sticking to my pivot table solution for now. Is there an option to refer to xlDistinctCount (by adding and using Data Model) by VBA to make all 4 steps automated?

 

[sergioMabres]

So you are looking for fruits that appear twice and with the same date only, that is to say bananas yes that apear twice with 09/01/2019, but NOT apples that also appear twice with 01/01/2019 and once with 09/01/2019, I think I can do that

 

[Akuini]

Hi, bukimi
I don't quite understand the requirement.
But let's try it.
Note:
I'm not sure about which part to delete, you mean you want to keep 1 Banana & delete the rest of Banana?
So I mark it with 0 & 1
I add the last row to show the ones with 0.

[FONT=lucida console][color=Royalblue]Sub[/color] a1083915a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1083915-how-find-non-duplicated-dates-vba-2.html[/color][/i]

[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

va = Range([color=brown]"A2:B"[/color] & Cells(Rows.count, [color=brown]"A"[/color]).[color=Royalblue]End[/color](xlUp).Row)

[color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
d.CompareMode = vbTextCompare

[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
    [color=Royalblue]If[/color] [color=Royalblue]Not[/color] d.Exists(va(i, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
        d(va(i, [color=crimson]1[/color])) = va(i, [color=crimson]2[/color])
    [color=Royalblue]Else[/color]
        [color=Royalblue]If[/color] d(va(i, [color=crimson]1[/color])) <> [color=brown]"x"[/color] [color=Royalblue]Then[/color]
            [color=Royalblue]If[/color] d(va(i, [color=crimson]1[/color])) <> va(i, [color=crimson]2[/color]) [color=Royalblue]Then[/color] d(va(i, [color=crimson]1[/color])) = [color=brown]"x"[/color]
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]

[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]1[/color])
[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
    [color=Royalblue]If[/color] d(va(i, [color=crimson]1[/color])) = [color=brown]"x"[/color] [color=Royalblue]Then[/color]
        vb(i, [color=crimson]1[/color]) = [color=brown]"x"[/color]
    [color=Royalblue]Else[/color]
        vb(i, [color=crimson]1[/color]) = [color=crimson]1[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]

[color=Royalblue]Set[/color] d = CreateObject([color=brown]"scripting.dictionary"[/color])
d.CompareMode = vbTextCompare

[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(vb, [color=crimson]1[/color])
    [color=Royalblue]If[/color] vb(i, [color=crimson]1[/color]) = [color=crimson]1[/color] [color=Royalblue]Then[/color]
        [color=Royalblue]If[/color] [color=Royalblue]Not[/color] d.Exists(vb(i, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
            d(vb(i, [color=crimson]1[/color])) = [color=crimson]1[/color]
        [color=Royalblue]Else[/color]
            vb(i, [color=crimson]1[/color]) = [color=crimson]0[/color]
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]Next[/color]


Range([color=brown]"E2"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color]) = vb

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

RESULT:

Book1
	A	B	C	D	E
1	PRODUCT NAME	DATE	CLIENT	ITEMS SOLD	DELETE
2	Apple	01/01/2019	Shop X	10	x
3	Apple	09/01/2019	Shop X	36	x
4	Banana	09/01/2019	Cafe Y..	42	1
5	Orange	01/01/2019	Shop Y.	31	x
6	Apple	01/01/2019	Club A..	12	x
7	Orange	09/01/2019	Club A..	48	x
8	Banana	09/01/2019	Shop X..	2	0
9	Banana	09/01/2019	Shop X..	3	0
Sheet2

 

[sergioMabres]

Hi Bukimi
I an not sure about the condition that you want but I think is this

[COLOR=#444444][FONT=Calibri]=IF(AND(COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2),COUNTIFS(A:A,A2)>1),1,0)[/FONT][/COLOR]

With this set

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
Other	9/1/2019	Shop X..	2	0

<tbody>
</tbody>

Cheers
Sergio

 

[bukimi]

Hi, bukimi
I don't quite understand the requirement.
But let's try it.

I'll try to re-state and re-phrase what it's all about. Database has hundreds of rows. One of columns contains date (one of two, never any other than X or Y) and other column contains a name of product (let's say that there are 10 different products).
I need to find WHICH product(s) appears in database always with date X (and never with date Y), OR appears always with date Y (and never with date X). There may be one ore more products that meet that criteria. Every product may appear many times on the list in database (not only twice).
In my example data only "Banana" meets criteria, as it's only product on the list that appears only with one date and never with the other date (it's always 9/1/2019 and never 1/1/2019).
Finally, I need to delete these identified products from the database (all entries about these products), but only identifying them is good enough.

I an not sure about the condition that you want but I think is this
Code:
=IF(AND(COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2),COUNTIFS(A:A,A2)>1),1,0)
​

It gave me these results on the same exact table as in your post:

0
0
0
1
0
0
0
1

<tbody>
</tbody>

so I got "1" next to Orange and next to "Other", not next to "Banana"...


Nevertheless, I think that it's not worth the effort to try further with my problem. Excel takes very long time to calculate COUNTIFS formula across many rows, so it's not a good direction (it freezes my Excel for some time).

 

[Akuini]

I'll try to re-state and re-phrase what it's all about. Database has hundreds of rows. One of columns contains date (one of two, never any other than X or Y) and other column contains a name of product (let's say that there are 10 different products).
I need to find WHICH product(s) appears in database always with date X (and never with date Y), OR appears always with date Y (and never with date X). There may be one ore more products that meet that criteria. Every product may appear many times on the list in database (not only twice).
In my example data only "Banana" meets criteria, as it's only product on the list that appears only with one date and never with the other date (it's always 9/1/2019 and never 1/1/2019).
Finally, I need to delete these identified products from the database (all entries about these products), but only identifying them is good enough.

Did you try my code? How is the result?
Using your example you mean you want to delete all banana?

 

[sergioMabres]

You have a displacement in the formula =IF(AND(COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,B:B,B2),COUNTIFS(A:A,A2)>1),1,0) is for row 2, check or look in my sheet here https://1drv.ms/x/s!AovCE1fDrrdSmk2PA63g7OzAwJyF

 

[bukimi]

Did you try my code? How is the result?
Using your example you mean you want to delete all banana?

Yes, in example data, all "Banana" need to be marked for deletion or deleted.

EDIT: I tested it and got "1" in Delete column next to 1st occurence of "Banana", but not next to the other. It's still very nice.
I'm thinking what is the difference between "0" and "x" in that column?

sergioMabres, You were right. I had extra row (1st) with numbers before real database. Sorry for the confusion. I'll try it on real data, but expect it to hang my Excel.
I wonder if asking VBA to put result of such a formula in each row would be faster than having this formula in all cells at once.