Find the closest option by comparing 3 variables

S

Said Marin

New Member
Joined
Mar 26, 2018
Messages
9
Given the following data:

[TABLE="width: 562"]
<colgroup><col span="6"><col></colgroup><tbody>[TR]
[TD]Material[/TD]
[TD]Class[/TD]
[TD]Y_ID[/TD]
[TD]Y_OD[/TD]
[TD]Y_L[/TD]
[TD]MatlCode[/TD]
[TD]STD[/TD]
[/TR]
[TR]
[TD]109.02253[/TD]
[TD]PR09[/TD]
[TD]152.4[/TD]
[TD]172.9[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.02635[/TD]
[TD]PR09[/TD]
[TD]228.6[/TD]
[TD]244.3[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.02754[/TD]
[TD]PR09[/TD]
[TD]238.13[/TD]
[TD]259.31[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03071[/TD]
[TD]PR09[/TD]
[TD]95.1738[/TD]
[TD]109.982[/TD]
[TD]6.2992[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03287[/TD]
[TD]PR09[/TD]
[TD]254[/TD]
[TD]274.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03295[/TD]
[TD]PR09[/TD]
[TD]304.8[/TD]
[TD]326.06[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.03342[/TD]
[TD]PR09[/TD]
[TD]280[/TD]
[TD]304[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.03466[/TD]
[TD]PR09[/TD]
[TD]250[/TD]
[TD]270.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.03889[/TD]
[TD]PR09[/TD]
[TD]419.1[/TD]
[TD]440.3344[/TD]
[TD]8.1026[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.04023[/TD]
[TD]PR09[/TD]
[TD]88.9[/TD]
[TD]104[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05289[/TD]
[TD]PR09[/TD]
[TD]20[/TD]
[TD]30.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05445[/TD]
[TD]PR09[/TD]
[TD]36.53[/TD]
[TD]43.79[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.05958[/TD]
[TD]PR09[/TD]
[TD]114.3[/TD]
[TD]129.39[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.06389[/TD]
[TD]PR09[/TD]
[TD]501.65[/TD]
[TD]525.66[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.06653[/TD]
[TD]PR09[/TD]
[TD]523.88[/TD]
[TD]545.13[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.07057[/TD]
[TD]PR09[/TD]
[TD]350[/TD]
[TD]374[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.07317[/TD]
[TD]PR09[/TD]
[TD]190[/TD]
[TD]210.5[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.07518[/TD]
[TD]PR09[/TD]
[TD]22.23[/TD]
[TD]29.51[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.08527[/TD]
[TD]PR09[/TD]
[TD]215.9[/TD]
[TD]236.4[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.08631[/TD]
[TD]PR09[/TD]
[TD]558.8[/TD]
[TD]580.06[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.08646[/TD]
[TD]PR09[/TD]
[TD]228.6[/TD]
[TD]249.1[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.08776[/TD]
[TD]PR09[/TD]
[TD]406.4[/TD]
[TD]430.4[/TD]
[TD]8.1[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09047[/TD]
[TD]PR09[/TD]
[TD]57.15[/TD]
[TD]67.89[/TD]
[TD]4.29[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09280[/TD]
[TD]PR09[/TD]
[TD]17.45[/TD]
[TD]24.74[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09283[/TD]
[TD]PR09[/TD]
[TD]20[/TD]
[TD]26.75[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09289[/TD]
[TD]PR09[/TD]
[TD]22[/TD]
[TD]30[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09293[/TD]
[TD]PR09[/TD]
[TD]28[/TD]
[TD]38.5[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09305[/TD]
[TD]PR09[/TD]
[TD]34.93[/TD]
[TD]42.24[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09313[/TD]
[TD]PR09[/TD]
[TD]40[/TD]
[TD]55.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09314[/TD]
[TD]PR09[/TD]
[TD]44[/TD]
[TD]51.5[/TD]
[TD]3.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09316[/TD]
[TD]PR09[/TD]
[TD]44.958[/TD]
[TD]55.7276[/TD]
[TD]4.2672[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09317[/TD]
[TD]PR09[/TD]
[TD]45[/TD]
[TD]55.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09322[/TD]
[TD]PR09[/TD]
[TD]50[/TD]
[TD]65.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09330[/TD]
[TD]PR09[/TD]
[TD]60[/TD]
[TD]75.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09332[/TD]
[TD]PR09[/TD]
[TD]65[/TD]
[TD]80.5[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09334[/TD]
[TD]PR09[/TD]
[TD]70[/TD]
[TD]85[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09336[/TD]
[TD]PR09[/TD]
[TD]75[/TD]
[TD]85.7[/TD]
[TD]4.2[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09339[/TD]
[TD]PR09[/TD]
[TD]82.55[/TD]
[TD]97.64[/TD]
[TD]6.3[/TD]
[TD]T23[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]109.09343[/TD]
[TD]PR09[/TD]
[TD]88.9[/TD]
[TD]104.5464[/TD]
[TD]6.2738[/TD]
[TD]T23[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]109.09349[/TD]
[TD]PR09[/TD]
[TD]101.6[/TD]
[TD]117.25[/TD]
[TD]6.27[/TD]
[TD]T23[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

The materials with the letter S in the last column of the data are considered standard sizes.

What i need to do is compare Y_ID, Y_OD and Y_L of every material and find the closest standard part number and give it in an additional column.

I've been struggling with nested if's and index match and can't figure out how to do it. I'll appreciate some help or guidance.

Thanks.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
It looks like he's trying to match a Column A value to each row that doesn't have an S in column G according to the closest part #s . My first idea would be sorting on column C and then flash filling the empty adjacent cells (the numbers seem at least almost sorted and close enough that this might work). Otherwise each row can be tested against the others using either VBA or a formula (perhaps with solver).
 
Last edited:
Upvote 0
With solver, I got:


Excel 2010
ABCDEFGHIJKLMNOPQRST
2MaterialClassY_IDY_ODY_LMatlCodeSTDMaterialClassY_IDY_ODY_LMatlCodeSTD
3109.0928PR092026.753.2T23117.4524.743.24.56109.0928PR0917.4524.743.2T23S
4109.0929PR0922303.2T23117.4524.743.29.81109.0529PR092030.74.2T23S
5109.0752PR0922.2329.513.2T2322030.74.20.04109.0545PR0936.5343.793.2T23S
6109.0929PR092838.54.2T23336.5343.793.2-12.82109.0932PR0944.95855.72764.2672T23S
7109.0931PR0934.9342.243.2T23336.5343.793.2-3.15109.0905PR0957.1567.894.29T23S
8109.0931PR094055.56.3T23444.95855.72764.2672-3.1528109.0934PR097585.74.2T23S
9109.0931PR094451.53.2T23444.95855.72764.2672-6.2528109.0934PR0982.5597.646.3T23S
10109.0932PR094555.74.2T23444.95855.72764.2672-0.0528109.0402PR0988.91046.3T23S
11109.0932PR095065.56.3T23557.1567.894.29-7.53109.0596PR09114.3129.396.3T23S
12109.0933PR096075.56.3T23557.1567.894.2912.47109.0225PR09152.4172.98.1T23S
13109.0933PR096580.56.3T23557.1567.894.2922.47109.0732PR09190210.58.1T23S
14109.0933PR0970856.3T2367585.74.2-3.6109.0853PR09215.9236.48.1T23S
15109.0934PR0988.9104.54646.2738T23888.91046.30.5202109.0865PR09228.6249.18.1T23S
16109.0307PR0995.1738109.9826.2992T23888.91046.312.255109.0347PR09250270.58.1T23S
17109.0935PR09101.6117.256.27T23888.91046.325.92109.0334PR092803048.1T23S
18109.0264PR09228.6244.36.3T2313228.6249.18.1-6.6109.0706PR093503748.1T23S
19109.0275PR09238.13259.318.1T2313228.6249.18.119.74109.0878PR09406.4430.48.1T23S
20109.0329PR09254274.58.1T2314250270.58.18109.0639PR09501.65525.668.1T23S
21109.033PR09304.8326.068.1T23152803048.146.86
22109.0389PR09419.1440.33448.1026T2317406.4430.48.122.637
23109.0665PR09523.88545.138.1T2318501.65525.668.141.7
24109.0863PR09558.8580.068.1T2318501.65525.668.1111.55
25381.6906
Sheet12
Cell Formulas
RangeFormula
I3=INDEX($P$3:$R$20,$H3,COLUMN(A1))
L3=(C3+D3+E3)-(I3+J3+K3)
L25=SUMPRODUCT(ABS(L3:L24))


asfcQWo.png
 
Upvote 0
Which next to your original data appears as:


Excel 2010
ABCDEFGHIJ
1MaterialClassY_IDY_ODY_LMatlCodeSTDxyz
2109.0225PR09152.4172.98.1T23S
3109.0264PR09228.6244.36.3T23228.6249.18.1
4109.0275PR09238.13259.318.1T23228.6249.18.1
5109.0307PR0995.1738109.9826.2992T2388.91046.3
6109.0329PR09254274.58.1T23250270.58.1
7109.033PR09304.8326.068.1T232803048.1
8109.0334PR092803048.1T23S
9109.0347PR09250270.58.1T23S
10109.0389PR09419.1440.33448.1026T23406.4430.48.1
11109.0402PR0988.91046.3T23S
12109.0529PR092030.74.2T23S
13109.0545PR0936.5343.793.2T23S
14109.0596PR09114.3129.396.3T23S
15109.0639PR09501.65525.668.1T23S
16109.0665PR09523.88545.138.1T23501.65525.668.1
17109.0706PR093503748.1T23S
18109.0732PR09190210.58.1T23S
19109.0752PR0922.2329.513.2T232030.74.2
20109.0853PR09215.9236.48.1T23S
21109.0863PR09558.8580.068.1T23501.65525.668.1
22109.0865PR09228.6249.18.1T23S
23109.0878PR09406.4430.48.1T23S
24109.0905PR0957.1567.894.29T23S
25109.0928PR0917.4524.743.2T23S
26109.0928PR092026.753.2T2317.4524.743.2
27109.0929PR0922303.2T2317.4524.743.2
28109.0929PR092838.54.2T2336.5343.793.2
29109.0931PR0934.9342.243.2T2336.5343.793.2
30109.0931PR094055.56.3T2344.95855.72764.2672
31109.0931PR094451.53.2T2344.95855.72764.2672
32109.0932PR0944.95855.72764.2672T23S
33109.0932PR094555.74.2T2344.95855.72764.2672
34109.0932PR095065.56.3T2357.1567.894.29
35109.0933PR096075.56.3T2357.1567.894.29
36109.0933PR096580.56.3T2357.1567.894.29
37109.0933PR0970856.3T237585.74.2
38109.0934PR097585.74.2T23S
39109.0934PR0982.5597.646.3T23S
40109.0934PR0988.9104.54646.2738T2388.91046.3
41109.0935PR09101.6117.256.27T2388.91046.3
Sheet1
 
Upvote 0
Columns C, D and E are the dimensions of every part number in A. If the part number has a letter S in column G it means it is a standard part number. What I'm trying to do is compare the dimensions of every part number and find the closest standard part number.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,828
Messages
6,181,206
Members
453,022
Latest member
RobertV1609

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