Formula to match criteria

[ItalianPlatinum]

I have a very large data set that I was attempting to count the occurrences by each line but it was bogging down the system. So a way to avoid is if I can accomplish the same based off a summary tab where I list the data. Two formulas I think I need to accomplish that I am struggling to find.

Quick explanation I have a program that will consolidate based off two criteria. Cost (rounded to 4 digits) and Date. For simplicity reasons I am going to avoid date for now. After it consolidates the two, three, or four occurrences it creates a new combined lot . This is an inventory management tool. I am trying to get myself some reporting and been working on VBA to do is. I am super close just running into headaches along the way like this.

I summarized the data below, short snipbit. The data is over 500k rows that is why I figure a summary is quicker than applying formulas to each row, then summarize based off that formula later.

Product	Identifier	Date Opened	Unique Lot	Cost	Cost Rounded to 4 digits			Result should be	Result should be
Group A	B1	1/1/2020	1	$ 20.91	$ 20.9125			Will be removed	Added Back	Difference
Group A	B1	1/9/2019	2	$ 22.00	$ 22.0000		B1	5	2	-3
Group A	B1	1/9/1999	3	$ 22.00	$ 22.0000		C1	0	0	0
Group A	B1	6/4/1999	4	$ 65.00	$ 65.0000		D1	6	3	-3
Group A	B1	7/9/2000	5	$ 65.00	$ 65.0000		E1	4	2	-2
Group A	B1	4/8/1988	6	$ 22.00	$ 22.0000
Group A	C1	5/5/2005	7	$ 35.00	$ 35.0000
Group A	C1	10/10/2020	8	$ 20.96	$ 20.9575
Group A	C1	1/1/2020	9	$ 40.00	$ 40.0000
Group A	C1	1/9/2019	10	$ 40.99	$ 40.9900
Group A	C1	1/9/1999	11	$ 41.30	$ 41.2990
Group A	C1	6/4/1999	12	$ 50.56	$ 50.5565
Group A	D1	7/9/2000	13	$ 51.28	$ 51.2750
Group A	D1	4/8/1988	14	$ 99.97	$ 99.9652
Group A	D1	5/5/2005	15	$ 20.91	$ 20.9125
Group A	D1	1/1/2020	16	$ 20.91	$ 20.9125
Group A	D1	1/1/2020	17	$ 99.97	$ 99.9652
Group A	D1	1/9/2019	18	$ 51.28	$ 51.2750
Group A	E1	1/9/1999	19	$ 41.30	$ 41.2990
Group A	E1	6/4/1999	20	$ 41.30	$ 41.2990
Group A	E1	7/9/2000	21	$ 51.28	$ 51.2750
Group A	E1	4/8/1988	22	$ 51.28	$ 51.2750
Group A	E1	5/5/2005	23	$ 50.56	$ 50.5565
Group A	E1	10/10/2020	24	$ 20.96	$ 20.9575

 

[ItalianPlatinum]

Oh this is great. Honestly don't even need J3 could just use I3 and K3. Let me apply it to my code and see how it manifests. but with my small dataset it seems to be working. I will just need to mess around cause the data is all mixed around

 

[ItalianPlatinum]

Ok few questions/help if I set the formula on the data sheet using VBA will that fill it down too? I tried both but doesn't seem to fill down.

Unless I keep it on the sheet for my example but on that sheet I only have identifier so would need to reference somewhere else so i don't think that would work.

This copies down, but now i am worried about time strain

lr1 = .Cells(rows.count, "A").End(xlUp).row
 .Range("R2:R" & lr1).Formula = "=COUNTIFS($D$2:$D$" & lr1 & ",$D$2:$D$" & lr1 & ",$M$2:$M$" & lr1 & ",$M$2:$M$" & lr1 & ")"

noticed it puts the @ in front of the formulas? =COUNTIFS($D$2:$D$123,@$D$2:$D$123,$M$2:$M$123,@$M$2:$M$123)

If I do this it doesn't fill down unless I am doing it wrong

lr1 = .Cells(rows.count, "A").End(xlUp).row
 .Range("R2").Formula = "=COUNTIFS($D$2:$D$" & lr1 & ",$D$2:$D$" & lr1 & ",$M$2:$M$" & lr1 & ",$M$2:$M$" & lr1 & ")"

 

[ItalianPlatinum]

actually i think i self serviced using formula2

 

[ItalianPlatinum]

Ok so I placed the helper on the data sheet. Then on my summary sheet I placed formula of column I. But I would have to reference each sheet based off the left most character. I tried to incorporate an indirect formula but its not working. Am I unable to do that approach? I can get close but then referencing with the countIF kicks the REF.

 

[ItalianPlatinum]

Made some improvements. Got myself further. converted the formula into a COUNTIF from COUNTIFS used an additional helper column of Identifier & cost. Then countif to just that new column. Macro went from running for 10 hours to 5 hours am I just SOL with the size of my data? I would hope i am not because now I am not sure what is left to do

 

[Rijnsent]

If you want I can have a try to shorten that more, but I would need to have the file. It might be that VBA and/or PowerQuery could shorten it even more, but that's hard to say.

 

[ItalianPlatinum]

I fully understand - the data is quite large so I figured I would be in a very tough spot here

 

[ItalianPlatinum]

You're right, I missed that part.

Create a helper column:

G2: =COUNTIFS($B$2:$B$25,$B$2:$B$25,$F$2:$F$25,$F$2:$F$25)  (should fill G2:G25)
I3: = COUNTIFS($B$2:$B$25,H3,$G$2#,">1")
J3: = I3+K3
K3: =COUNTA(UNIQUE(FILTER($E$2:$E$25,$B$2:$B$25=H3)))-COUNTIFS($B$2:$B$25,H3)

You can probably create a one-cell formula in I3 etc, but that takes a bit of fiddling, probably with SUMPRODUCT, UNIQUE, COUNTIFS etc. Note that $G$2# refers to $G$2:$G$25, but is a new way Excel refers to the spilled ranges (see my link with your #SPILL error).

@Rijnsent I been thoroughly testing this and noticed the formula for column K creates records of 1 even when the data isn't even present. See below for an example - would you know why that could be the case?

 

[ItalianPlatinum]

@Rijnsent I been thoroughly testing this and noticed the formula for column K creates records of 1 even when the data isn't even present. See below for an example - would you know why that could be the case?

View attachment 108381

Sorry you can ignore I found a minor tweak to get it to work. having it reference a helper column removed that problem