Finding the lowest cost dynamically

[traveler84]

On my spreadsheet, I have multiple addresses with vendor bids. The addresses might be slightly different such as an extra space or thoroughfare spelled (i.e. ST vs Street). So I was going to create a helper column that I will statically number for duplicated addresses. My list could be 10 addresses or could be 1000s. I'm trying to find a formula that can calculate the lowest cost in each group number for every address.

Here is a simplied version of the data: (could be 1 entry for an address or could be 10)

The output that I would like on another sheet would look like:

Where I am confused is how to keep running the formula to generate all the best options starting with 1 through the last number. In my mind, I am thinking of some kind of loop function that seeks all the remaining options until the last number. If the data was static, I am pretty confident I could figure it out but since the address lists could vary day to day, that's my challenge.

The SMALL function might work if I could figure out the loop till the last number and output each number. I am open to VBA which isn't my strong suit if that's what I need.

Thank you all so very much.

 

[Fluff]

I'm trying to find a formula that can calculate the lowest cost in each group number

You can use MINIFS for that MINIFS Function

 

[Peter_SSs]

Perhaps this to include making all of the result table?

23 05 05.xlsm
	A	B	C	D	E	F	G
1	1	123 Elm	$500		1	123 Elm	500
2	1	123 Elm street	$600		2	100 Elm Street	200
3	1	123 Elm st	$700		3	999 Elm	300
4
5	2	100 Elm Street	$200
6	2	100 Elm St.	$600
7
8	3	999 Elm	$500
9	3	999 Elm st	$600
10	3	999 Elm	$300
11
Lowest

Cell Formulas
Range	Formula
E1:E3	E1	=LET(r,A1:A10000,UNIQUE(FILTER(r,r<>"")))
F1:F3	F1	=VLOOKUP(E1#,A1:B10000,2,0)
G1:G3	G1	=MINIFS(C1:C10000,A1:A10000,E1#)
Dynamic array formulas.

 

[traveler84]

Perhaps this to include making all of the result table?

Thanks Peter!

I started to explore expanding the results by adding the city, state, zip. Then I got greedy and added the vendor name next to the price. No matter if I change the Vlookup to True or False, it seems to not always give me the correct name to the corresponding price point.

Some are matching up but like this example here, but on location 3, it shows up as Acme but should be ZZZ. Is there a way to match the result with the lowest price point? Would I need an index / match formula for that one? Just not sure how to implement with a unique filter if it's even possible.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	123 Elm	State	a	l	w	500		1	123 Elm	State	a	l	w	500
2	1	123 Elm street	State	b	m	x	600		2	100 Elm Street	State	e	p	aa	200
3	1	123 Elm st	State	c	n	y	700		3	999 Elm	State	j	u	af	300
4
5	2	100 Elm Street	State	e	p	aa	200
6	2	100 Elm St.	State	f	q	ab	600
7
8	3	999 Elm	State	h	s	ad	500
9	3	999 Elm st	State	i	t	ae	600
10	3	999 Elm	State	j	u	af	300
11
Sheet4

Cell Formulas
Range	Formula
I1:I3	I1	=UNIQUE(FILTER(A1:A1000,A1:A1000<>""))
O1:O3	O1	=MINIFS(G1:G1000,A1:A1000,I1#)
J1:N3	J1	=FILTER($B$1:$F$1000,($A$1:$A$1000=I1)*($G$1:$G$1000=O1))
Dynamic array formulas.

 

[traveler84]

That does work but would require for me to paste the J1:N3 values in every possible cell due to the unknown of volume that will be presented in each situation. The Unique filter works as I input more data but leaves the remaining cells blank due to no spill.

=XLOOKUP(A2,(A2)*(MRC=G2),Vendor)

I am stepping away from the example and copying/pasting the exact code in my sheet. The above works as well and provides the results but I wanted to figure out if there's a way to spill the results so I don't have to copy/paste in each cell. I got an error when I try to add # to either A2 or both.

Just trying to prevent copying the formulas and freezing up the sheet with 100,000 formulas copied just to account for the possibility of it being that many results.

 

[Fluff]

Realistically how many rows are you likely to have in col I

 

[traveler84]

Just to clarify on my reply earlier, A2 is the number I gave each address a unique number like 1,2,3,4,5, etc. A1 is a header. In the example, the unique values start in A1.

The largest amount of addresses was 50,000 at one time. But each address could have multiple vendor bids. Most of the time, it's between 10-1000 but it's not uncommon to see 10,000 or more.

 

[Fluff]

The only way I can of to get the formula to spill down is to use the REDUCE function, but over that number of rows it is likely to be very very slow.
Personally I think you would be better of dragging the formula down, unless someone else can think of another way.

 

[Fluff]

Had an idea, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1	123 Elm	State	a	l	w	500		1	123 Elm	State	a	l	w	500
2	1	123 Elm street	State	b	m	x	600		1	123 Elm st	State	c	n	y	500
3	1	123 Elm st	State	c	n	y	500		2	100 Elm Street	State	e	p	aa	200
4									3	999 Elm	State	j	u	af	300
5	2	100 Elm Street	State	e	p	aa	200
6	2	100 Elm St.	State	f	q	ab	600
7
8	3	999 Elm	State	h	s	ad	500
9	3	999 Elm st	State	i	t	ae	600
10	3	999 Elm	State	j	u	af	300
Sheet4

Cell Formulas
Range	Formula
I1:O4	I1	=LET(u,UNIQUE(FILTER(A1:A1000,A1:A1000<>"")),m,MINIFS(G:G,A:A,u),FILTER(A1:G1000,BYROW((A1:A1000=TOROW(u))*(G1:G1000=TOROW(m)),LAMBDA(br,SUM(br)))))
Dynamic array formulas.