Location Selection to buy

[freddocp]

Good day,

Please, I appreciate the help in advance I'll try to explain with an example what I really need; a formula to fill in the locality I should buy in column B.

First all the statuses need to be “Available” line 1

1- As I'm located in Brazil, this would be the location priority. Example in line 3; I need 2 ; “needed :2” and in Brazil I have 297 units available.

2- When not available in Brazil, select the nearest countries, for example in line 5, Chile or Colombia. Note that China has been left out.
To decide between Chile and Colombia, check which country has more different ID numbers available; for example, between rows 5 and 8, Chile has 4, and Colombia 2. Therefore, it is more interesting to buy from Chile.

3- If the “Available” quantity is below the “Needed” quantity, select the one with the quantity or more; example line 9, Australia.

Location to buy test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Available	Available	Available	Available	Available	Available	Available	Not available	Available	Available
2	Id Number	Location	Needed	Argentina	Chile	Colombia	Uruguai	Portugal	France	Japan	China	Australia	Brazil
3	5623	Brazil	2	0	1	50	0	0	0	0	0	0	297
4	2458	Brazil	1	0	0	0	0	0	0	0	0	0	7
5	213335	Chile	2	0	7	2	0	0	0	0	254	0	0
6	598456	Chile	3	0	3	0	0	0	2	0	0	0	0
7	369546	Chile	2	0	5	10	0	0	0	0	0	0	0
8	365982	Chile	1	0	2	0	0	0	0	0	0	17	0
9	123458	Australia	3	0	1	0	0	1	2	0		4	0
10	32156	Brazil	3	10	0	0	3	0	0	0	0	0	8
11	6712569	Brazil	1	0	0	9	0	0	0	0	0	0	1
12	364781	Uruguai	2	0	0	0	2	0	0	0	0	1	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A12	Cell Value	duplicates	text	NO
D3:M12	Cell Value	>=$C3	text	NO
D3:M12	Cell Value	between $C3 and 0,1	text	NO

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Available	Available	Available	Available	Available	Available	Available	Not available	Available	Available
2	Id Number	Location	Needed	Argentina	Chile	Colombia	Uruguai	Portugal	France	Japan	China	Australia	Brazil
3	5623	Brazil	2	0	1	50	0	0	0	0	0	0	297
4	2458	Brazil	1	0	0	0	0	0	0	0	0	0	7
5	213335	Chile	2	0	7	2	0	0	0	0	254	0	0
6	598456	Chile	3	0	3	0	0	0	2	0	0	0	0
7	369546	Chile	2	0	5	10	0	0	0	0	0	0	0
8	365982	Chile	1	0	2	0	0	0	0	0	0	17	0
9	123458	Australia	3	0	1	0	0	1	2	0		4	0
10	32156	Brazil	3	10	0	0	3	0	0	0	0	0	8
11	6712569	Brazil	1	0	0	9	0	0	0	0	0	0	1
12	364781	Uruguai	2	0	0	0	2	0	0	0	0	1	1
Sheet9

Cell Formulas
Range	Formula
B3:B12	B3	=LET(avail,$D$1:$M$1,ctry,$D$2:$M$2,need,$C$3:$C$12,brz,M3,tbl,$D$3:$M$12,ix,ROWS($B$3:$B3),t,MMULT(SEQUENCE(,ROWS(tbl),,0),--(tbl>=need))*(avail="available")*(INDEX(tbl,ix,0)>=INDEX(need,ix)),sb,SORTBY(ctry,t,-1),IFS(INDEX(need,ix)<=brz,"Brazil",INDEX(need,ix)>MAX(INDEX(tbl,ix,0)),"N/A",1,INDEX(sb,1)))

 

[freddocp]

It works very well! Thank you very much! Great formula!

 

[Eric W]

Happy to help!

 

[freddocp]

Happy to help!

Hi Eric ,

I've done some tests, and I'd like to see if you can help me, if possible. I need to try to prioritize the closest countries, I put in line 14 for example a priority level, I tried to add in your formula but I could not.

I really asked for the premise to use where there are more different items for a lot, but however, in this example when the priority of the country is higher (value closer to 1) the ideal would be to buy from this country.

As an example: Chile has 3 available, Portugal has 4. The formula determines Portugal, but Chile has Priority 2, so in this case buy from Chile which is closer to Brazil. If the priority is the same, apply the formula as before.

I hope I've explained, thanks again

Location to buy test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Available	Available	Available	Available	Available	Available	Available	Not available	Available	Available
2	Id Number	Location	Needed	Argentina	Chile	Colombia	Uruguai	Portugal	Peru	Japan	China	Australia	Brazil
3	5623	Brazil	2	0	1	50	0	0	0	0	0	0	297
4	2458	Brazil	1	0	0	0	0	0	0	0	0	0	7
5	213335	Portugal	2	0	0	2	0	4	0	0	254	0	0
6	598456	Portugal	3	0	3	0	0	5	2	0	0	0	0
7	369546	Portugal	2	0	5	0	0	10	0	0	0	0	0
8	365982	Portugal	1	0	2	0	0	9	0	0	0	17	0
9	123458	Australia	3	0	1	0	0	1	2	0		4	0
10	32156	Brazil	3	10	0	0	3	0	0	0	0	0	8
11	6712569	Brazil	1	0	0	9	0	0	0	0	0	0	1
12	364781	Uruguai	2	0	0	0	2	0	0	0	0	1	1
13
14		Country priority		2	2	2	2	3	2	3	4	4	1
15
16
Sheet1

Cell Formulas
Range	Formula
B3:B12	B3	=LET(avail,$D$1:$M$1,ctry,$D$2:$M$2,need,$C$3:$C$12,brz,M3,tbl,$D$3:$M$12,ix,ROWS($B$3:$B3),t,MMULT(SEQUENCE(,ROWS(tbl),,0),--(tbl>=need))*(avail="available")*(INDEX(tbl,ix,0)>=INDEX(need,ix)),sb,SORTBY(ctry,t,-1),IFS(INDEX(need,ix)<=brz,"Brazil",INDEX(need,ix)>MAX(INDEX(tbl,ix,0)),"N/A",1,INDEX(sb,1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A12	Cell Value	duplicates	text	NO
D3:M12	Cell Value	>=$C3	text	NO
D3:M12,D14:M14	Cell Value	between $C3 and 0,1	text	NO

 

[Eric W]

Give this a try. I'm not sure the interaction between priority and country frequency is always going to work though.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1				Available	Available	Available	Available	Available	Available	Available	Not available	Available	Available
2	Id Number	Location	Needed	Argentina	Chile	Colombia	Uruguai	Portugal	Peru	Japan	China	Australia	Brazil
3	5623	Brazil	2	0	1	50	0	0	0	0	0	0	297
4	2458	Brazil	1	0	0	0	0	0	0	0	0	0	7
5	213335	Colombia	2	0	0	2	0	4	0	0	254	0	0
6	598456	Chile	3	0	3	0	0	5	2	0	0	0	0
7	369546	Chile	2	0	5	0	0	10	0	0	0	0	0
8	365982	Chile	1	0	2	0	0	9	0	0	0	17	0
9	123458	Australia	3	0	1	0	0	1	2	0		4	0
10	32156	Brazil	3	10	0	0	3	0	0	0	0	0	8
11	6712569	Brazil	1	0	0	9	0	0	0	0	0	0	1
12	364781	Uruguai	2	0	0	0	2	0	0	0	0	1	1
13
14		Country priority		2	2	2	2	3	2	3	4	4	1
Sheet11

Cell Formulas
Range	Formula
B3:B12	B3	=LET(avail,$D$1:$M$1,ctry,$D$2:$M$2,prty,$D$14:$M$14,need,$C$3:$C$12,tbl,$D$3:$M$12,ix,ROWS($B$3:$B3),t,MMULT(SEQUENCE(,ROWS(tbl),,0),--(tbl>=need))*(avail="available")*(INDEX(tbl,ix,0)>=INDEX(need,ix)),sb,SORTBY(CHOOSE(SEQUENCE(2),ctry,t),prty,1,t,-1),IFS(INDEX(need,ix)>MAX(INDEX(tbl,ix,0)),"N/A",1,INDEX(FILTER(sb,INDEX(sb,2,0)>0),1,1)))

 

[Eric W]

A little shorter, and some line feeds for clarity:

=LET(avail,$D$1:$M$1,
ctry,$D$2:$M$2,
prty,$D$14:$M$14,
need,$C$3:$C$12,
tbl,$D$3:$M$12,
ix,ROWS($B$3:$B3),
frq,MMULT(SEQUENCE(,ROWS(tbl),,0),--(tbl>=need))*(avail="available")*(INDEX(tbl,ix,0)>=INDEX(need,ix)),
sb,SORTBY(CHOOSE(SEQUENCE(2),ctry,frq),prty,1,frq,-1),
INDEX(FILTER(sb,INDEX(sb,2,0)>0,"N/A"),1,1))

 

[freddocp]

A little shorter, and some line feeds for clarity:

=LET(avail,$D$1:$M$1,
ctry,$D$2:$M$2,
prty,$D$14:$M$14,
need,$C$3:$C$12,
tbl,$D$3:$M$12,
ix,ROWS($B$3:$B3),
frq,MMULT(SEQUENCE(,ROWS(tbl),,0),--(tbl>=need))*(avail="available")*(INDEX(tbl,ix,0)>=INDEX(need,ix)),
sb,SORTBY(CHOOSE(SEQUENCE(2),ctry,frq),prty,1,frq,-1),
INDEX(FILTER(sb,INDEX(sb,2,0)>0,"N/A"),1,1))

My friend, I owe you a good lunch when you visit Brazil, thank you very much!