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

 

[Rijnsent]

Hi there, so you want unique values for the columns identifier+cost? If you have Office 365, you could use the HSTACK+UNIQUE functions:
=UNIQUE(HSTACK(B2:B25,E2:E25)) (based on your example).

 

[ItalianPlatinum]

I get SPILL warning when I use the formula. never used either before. Do I need to reference H somewhere in the formula for the identifier criteria?

But that is mostly correct. For Identifier (B1) I need to know how many unique pairs before our program inventory manager consolidates them. So in B1 example. I have a pair of 2 and trio of 3. So the 2 will compress to 1 and 3 will compress to 1. So I need to know where i started from (5) and where i ended up (2).

 

[Rijnsent]

If you want to know more about these new formulas, check out e.g. Unique values from multiple ranges
And for the SPILL error: see #SPILL! error in Excel - what it means and how to fix - the range where the results appear needs to be empty, and you now have e.g. "B1" in several cells, try the formula in an empty part of your sheet.

If you're just looking for those numbers:

I3: =COUNTIFS($B$2:$B$25,H3)
J3: =COUNTA(UNIQUE(FILTER($E$2:$E$25,$B$2:$B$25=H3)))

More info on those formulas, e.g. how to filter with multiple criteria: Unique values with criteria

 

[ItalianPlatinum]

Ah ok got that to work it is pretty cool never knew anything about that. So it is just replaying my data again on an open table? Those formulas are not quite matching up to the desired.

 

[Rijnsent]

To know what those formulas do: check out the Exceljet website, they have plenty of good examples. These are the new formulas that were added last year: Dynamic array formulas in Excel
On your desired result: maybe you can show how you would count/calculate the values in I3:J6 and what you want to do with them afterwards?

 

[ItalianPlatinum]

I think your question you are asking is my problem statement if I knew that answer I would not need to seek help. I tried reviewing the dynamic array but I don't think they pertain to my case. Unless a combination of them could work - but i don't currently see how. The formulas provided thus far don't yield the results in my I3:J6.

The approach I was doing/attempting caused my tool to run for 15 hours and crash - I was counting every occurrence using COUNTIFS with CONCATENATE of the identifier and cost, then taking VBA to copy the data to a new sheet, removing duplicates based off of the CONCATENATE, then summing the COUNTIFS column to get the removal figure in column I. That is when I realized I cannot do that approach my data set is over 1 million rows broken out into different sheets due to its size.

What I plan to do with the results is have a preventive check before my inventory tool runs to see what it would be doing. Currently it could run for hours but I can limit how it runs based off of the identifier so if I had a way to limit its run I can target how it runs reducing time constraints. Right now i don't have any mechanism for that.

 

[Rijnsent]

I'm asking to explain the problem / describe what you want, not to give me the formula:

If you look at cell I3, there is a 5, why is that a 5? For what I can see you have 6 lines with "B1" in your table, that is what my COUNTIFS formula calculates. It seems like you are trying to calculate the number of rows where column B is equal to "B1" and the price occurs multiple times (is not unique) in that group. The cell next to it shows a 2, that is the amount of unique prices in that sub-group (of 5 lines), so you want to add that back in. If you do that you end up with 3 lines for the group "B1", which is exactly the result you also get when you use that HSTACK+UNIQUE function. The -3 difference is the same as you would achieve with my 2 formulas (column I minus column J equals column L minus M). Ci sono tante le strade che portano a roma...

Depending on what your desired outcome is, there are probably smarter ways to achieve that than removing all the "lines with duplicate prices" and than adding them back in. That's why I'm wondering what outcome/overview you are looking for - the table you have in I3:J6 probably is not the final result you are working towards?

 

[ItalianPlatinum]

I thought that was described in post 3?

But that is mostly correct. For Identifier (B1) I need to know how many unique pairs before our program inventory manager consolidates them. So in B1 example. I have a pair of 2 and trio of 3. So the 2 will compress to 1 and 3 will compress to 1. So I need to know where i started from (5) and where i ended up (2).

It becomes 5 because I only care about matches. If there isn't a match so in B1 example for cost of 20.9125 that lot has no pair so it stays untouched. For your point yes the desired result for difference does play itself out BUT ill have such an inflated figure as my dataset say for B1 could be 9.000. Therefore for the 5 (3 @ 22) and (2 @ 65) are going to be removed by the inventory manager and recreated with 1 consolidated record of 22 and 1 consolidated record of 65. The second column ends up being how many unique pairs exist. So two in our case (22 and 65).

You are sort of correct it isn't the last stop in this project. seeing you helped on the loop function to get me to this point

 

[Rijnsent]

I thought that was described in post 3?

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).