Determine Minimum Size Needed in Range

Warship

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,055
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Data layout:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">i751</td><td style=";">1</td><td style=";">lb</td><td style=";">i759</td><td style=";">3</td><td style=";">lb</td><td style=";">i756</td><td style=";">2</td><td style=";">lb</td><td style=";">i758</td><td style=";">1.5</td><td style=";">lb</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
I need to determine the minimum size to meet a need and return the Col# of the SizeID Col.
For Example:
If need is 3 or more lbs then Col 5 (i759)
need is 1.25 lbs then Col 11 (i758)
need is .5 lbs then Col 2 (i751)
need is 1.75 lbs then Col 8 (i756)
need is 2 lbs then Col 8 (i756)

Sorting Sizes at data entry would help but I'm trying to avoid if possible.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Warship said:
Data layout:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">i751</td><td style=";">1</td><td style=";">lb</td><td style=";">i759</td><td style=";">3</td><td style=";">lb</td><td style=";">i756</td><td style=";">2</td><td style=";">lb</td><td style=";">i758</td><td style=";">1.5</td><td style=";">lb</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
I need to determine the minimum size to meet a need and return the Col# of the SizeID Col.
For Example:
If need is 3 or more lbs then Col 5 (i759)
need is 1.25 lbs then Col 11 (i758)
need is .5 lbs then Col 2 (i751)
need is 1.75 lbs then Col 8 (i756)
need is 2 lbs then Col 8 (i756)

Sorting Sizes at data entry would help but I'm trying to avoid if possible.
Click to expand...
Let A1:M2 house the sample and A6:A10 test sizes of interest,

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>i759</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>i758</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>i751</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>1.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>i756</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>i756</TD></TR></TBODY></TABLE>

B6, control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$K$2,MATCH(MIN(IF(ISNUMBER($C$2:$L$2),IF($C$2:$L$2>=A6,$C$2:$L$2))),$C$2:$L$2,0))
 
Upvote 0
hi, try:

=INDEX(A2:M2,MATCH(ROUNDUP(A10,0),A2:M2,)-1)

where A10 is the reference to your imput value (like 1, 0.5, 3, etc)
 
Upvote 0
Warship said:
Data layout:
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Item</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td><td style=";">SizeID</td><td style=";">Size</td><td style=";">Unit</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">i751</td><td style=";">1</td><td style=";">lb</td><td style=";">i759</td><td style=";">3</td><td style=";">lb</td><td style=";">i756</td><td style=";">2</td><td style=";">lb</td><td style=";">i758</td><td style=";">1.5</td><td style=";">lb</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
I need to determine the minimum size to meet a need and return the Col# of the SizeID Col.
For Example:
If need is 3 or more lbs then Col 5 (i759)
need is 1.25 lbs then Col 11 (i758)
need is .5 lbs then Col 2 (i751)
need is 1.75 lbs then Col 8 (i756)
need is 2 lbs then Col 8 (i756)

Sorting Sizes at data entry would help but I'm trying to avoid if possible.
Click to expand...

Apologies and appreciations all around... however, I neglected to mention I need a VBA solution.
 
Upvote 0
hi,

assuming that the formula i provided was correct (which i think it was) - you can simply plug it into VBA. i wouldnt recreate the wheel since the formula already exists.

So, something like this:

Code: 
sub test()
 range("A2").formula = "=INDEX(A2:M2,MATCH(ROUNDUP(A10,0),A2:M2,)-1)"
 range("A2").value = range("A2").value
end sub

or, you can populate the entire row / column by looping thru the cells:

Code: 
sub test*()
for each TmpRng in range("A2:M2")
 TmpRng.Formula = "=INDEX(A2:M2,MATCH(ROUNDUP(A10,0),A2:M2,)-1)"
 TmpRng.value = TmpRng.value
next
end sub

HTH
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
692
John_w
J
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top