comparing a list to another list with multiples, then take values from first list and find closest values on second list.

brianclay111

New Member
Joined
Sep 6, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
1600011050118.png


Hi! so here I took the first column and took out the letters at the beginning and the numbers at the end. On column F the multiples were taken out. Now I have 2 different values for each row in column G and H. AAU for example has 169.84 in G and -168.3321 in H. I would like to compare the list in F to list C. For example AAU comparing it to list C, there are 4 AAU's in list C. I would like to compare the first number of AAU which is 169.84 to all the numbers in the D column that matches with the AAU's. 2.5, 2.5, 5, 5. and It would match to the closest number which is 5 and it would write the 5 in column I. The same needs to be done for column H, which would be written in column J.
-Thank You!-
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
A couple of different solutions. 1 difficult and 1 pretty easy.

Difficult first: A stupid long formula

I broke this into a few helper columns, H:J, but I also combined all of them into 1 formula in column K.

Book1
CDEFGHIJK
1Min DiffPositionLookup ValueAIO
2AAU2.5AAOI174.99760   
3AAU2.5AAU169.84164.84355
4AAU5ABEO114.34020   
5AAU5ACRX102.918795.4187197.57.5
6ACOR1ABUS308.36350   
7ACOR1ACOR154.9296149.92961355
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet3
Cell Formulas
RangeFormula
H2:H7H2=MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))))
I2:I7I2=IF(H2>0,MATCH(H2,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")
J2:J7J2=IF(I2="","",INDEX($D$2:$D$21,I2))
K2:K7K2=IF(IF(H2>0,MATCH(MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)))),ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")="","",INDEX($D$2:$D$21,IF(H2>0,MATCH(MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)))),ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Easy Way: Power Query
For this one I split the data into 2 tables as below. Then I merged the tables as a new query joining on the 'Value' column. After you perform the join you can go into the advanced editor and paste the following.

Power Query:
let
    Source = Table.NestedJoin(Table2,{"Label"},Table1,{"Label"},"Table1",JoinKind.LeftOuter),
    Closest = Table.AddColumn(Source, "Custom", (rw) => Table.Min(Table.TransformColumns(rw[Table1],{{"Value", each Number.Abs(rw[Value]) - _}}),"Value")[Value])
in
    Closest

And here are the results

Book1
ABCDEFGHI
1LabelValueLabelValueLabelValueCustom
2AAU2.5AAOI174.9976AAOI174.9976
3AAU2.5AAU169.84AAU169.84164.84
4AAU5ABEO114.3402ABEO114.3402
5AAU5ACRX102.9187ACRX102.918795.4187
6ACOR1ABUS308.3635ABUS308.3635
7ACOR1ACOR154.9296ACOR154.9296149.9296
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet4


Let me know if either of these work for you.

Also, in the future, please use the XL2BB add in to post sample data. Makes it easier not having to manually type in test data.
 
Upvote 0
A little update to the Power Query.

Book1
ABCDEFGHI
1LabelValueLabelValueLabelValueResult
2AAU2.5AAOI174.9976AAOI174.9976
3AAU2.5AAU169.84AAU169.845
4AAU5ABEO114.3402ABEO114.3402
5AAU5ACRX102.9187ACRX102.91877.5
6ACOR1ABUS308.3635ABUS308.3635
7ACOR1ACOR154.9296ACOR154.92965
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet4


Power Query:
let
    Source = Table.NestedJoin(Table2,{"Label"},Table1,{"Label"},"Table1",JoinKind.LeftOuter),
    Dif = Table.AddColumn(Source, "Dif", (rw) => Table.TransformColumns(rw[Table1],{{"Value", each Number.Abs(rw[Value] - _)}})),
    Min = Table.AddColumn(Dif, "Min", each Table.Min([Dif],"Value")),
    Result = Table.AddColumn(Min, "Result", each [Table1]{Table.PositionOf([Dif],[Min])}[Value]),
    SC = Table.SelectColumns(Result,{"Label", "Value", "Result"})
in
    SC
 
Upvote 0
A little update to the Power Query.

Book1
ABCDEFGHI
1LabelValueLabelValueLabelValueResult
2AAU2.5AAOI174.9976AAOI174.9976
3AAU2.5AAU169.84AAU169.845
4AAU5ABEO114.3402ABEO114.3402
5AAU5ACRX102.9187ACRX102.91877.5
6ACOR1ABUS308.3635ABUS308.3635
7ACOR1ACOR154.9296ACOR154.92965
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet4


Power Query:
let
    Source = Table.NestedJoin(Table2,{"Label"},Table1,{"Label"},"Table1",JoinKind.LeftOuter),
    Dif = Table.AddColumn(Source, "Dif", (rw) => Table.TransformColumns(rw[Table1],{{"Value", each Number.Abs(rw[Value] - _)}})),
    Min = Table.AddColumn(Dif, "Min", each Table.Min([Dif],"Value")),
    Result = Table.AddColumn(Min, "Result", each [Table1]{Table.PositionOf([Dif],[Min])}[Value]),
    SC = Table.SelectColumns(Result,{"Label", "Value", "Result"})
in
    SC

Is this giving a solution for every other one?
 
Upvote 0
A couple of different solutions. 1 difficult and 1 pretty easy.

Difficult first: A stupid long formula

I broke this into a few helper columns, H:J, but I also combined all of them into 1 formula in column K.

Book1
CDEFGHIJK
1Min DiffPositionLookup ValueAIO
2AAU2.5AAOI174.99760   
3AAU2.5AAU169.84164.84355
4AAU5ABEO114.34020   
5AAU5ACRX102.918795.4187197.57.5
6ACOR1ABUS308.36350   
7ACOR1ACOR154.9296149.92961355
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet3
Cell Formulas
RangeFormula
H2:H7H2=MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))))
I2:I7I2=IF(H2>0,MATCH(H2,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")
J2:J7J2=IF(I2="","",INDEX($D$2:$D$21,I2))
K2:K7K2=IF(IF(H2>0,MATCH(MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)))),ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")="","",INDEX($D$2:$D$21,IF(H2>0,MATCH(MIN(IF(ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2))<>0,ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)))),ABS((G2-$D$2:$D$21)*($C$2:$C$21=F2)),0),"")))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Easy Way: Power Query
For this one I split the data into 2 tables as below. Then I merged the tables as a new query joining on the 'Value' column. After you perform the join you can go into the advanced editor and paste the following.

Power Query:
let
    Source = Table.NestedJoin(Table2,{"Label"},Table1,{"Label"},"Table1",JoinKind.LeftOuter),
    Closest = Table.AddColumn(Source, "Custom", (rw) => Table.Min(Table.TransformColumns(rw[Table1],{{"Value", each Number.Abs(rw[Value]) - _}}),"Value")[Value])
in
    Closest

And here are the results

Book1
ABCDEFGHI
1LabelValueLabelValueLabelValueCustom
2AAU2.5AAOI174.9976AAOI174.9976
3AAU2.5AAU169.84AAU169.84164.84
4AAU5ABEO114.3402ABEO114.3402
5AAU5ACRX102.9187ACRX102.918795.4187
6ACOR1ABUS308.3635ABUS308.3635
7ACOR1ACOR154.9296ACOR154.9296149.9296
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
Sheet4


Let me know if either of these work for you.

Also, in the future, please use the XL2BB add in to post sample data. Makes it easier not having to manually type in test data.

ok. I've tried the AIO Formula so far with the min diff. those are some really long formulas.. lol.. But they seem to work pretty well. I had to change a couple things. I had to take out the $ symbols and extend the C and D values. I'm assuming that is implied to do so anyway. Thank You for this I've been knocking my head trying to figure it out all day.
 
Upvote 0
Glad to hear that you got the formulas to work. Be careful about the '$'s. That denotes a static range and if you don't have them the range will move as you copy down.

As for the question about Power Query, it was doing every other one because it just couldn't find matches for those since I didn't have them in the first table.

In that example I also only did it for the 1 value not for both of them.

Here is a more complete example.

Book1
ABCDEFGHIJKL
1LabelValueLabelValue1Value2LabelValue1Value2Result1Result2
2AAU2.5AAOI174.9976-148.219AAOI174.9976-148.2195020
3AAU2.5AAU169.84-168.332AAU169.84-168.332152.5
4AAU5ABEO114.3402-109.602ABEO114.3402-109.6024100100
5AAU5ACRX102.9187-70.5146ACRX102.9187-70.51467.52.5
6ACOR1ABUS308.3635-301.415ABUS308.3635-301.414682
7ACOR1ACOR154.9296-160.664ACOR154.9296-160.664351
8ACOR2
9ACOR2
10ACOR3
11ACOR3
12ACOR4
13ACOR4
14ACOR5
15ACOR5
16ACRX2.5
17ACRX2.5
18ACRX5
19ACRX5
20ACRX7.5
21ACRX7.5
22AAOI20
23AAOI30
24AAOI40
25AAOI50
26ABEO100
27ABEO200
28ABEO300
29ABUS2
30ABUS4
31ABUS6
32ABUS8
Sheet5


Power Query:
let
    Source = Table.NestedJoin(TC,{"Label"},LK,{"Label"},"LK",JoinKind.LeftOuter),
    Dif1 = Table.AddColumn(Source, "Dif1", (rw) => Table.TransformColumns(rw[LK],{{"Value", each Number.Abs(rw[Value1] - _)}})),
    Dif2 = Table.AddColumn(Dif1, "Dif2", (rw) => Table.TransformColumns(rw[LK],{{"Value", each Number.Abs(rw[Value2] - _)}})),
    Min1 = Table.AddColumn(Dif2, "Min1", each Table.Min([Dif1],"Value")),
    Min2 = Table.AddColumn(Min1, "Min2", each Table.Min([Dif2],"Value")),
    Result1 = Table.AddColumn(Min2, "Result1", (rw) => rw[LK]{Table.PositionOf(rw[Dif1],rw[Min1])}[Value]),
    Result2 = Table.AddColumn(Result1, "Result2", (rw) => rw[LK]{Table.PositionOf(rw[Dif2],rw[Min2])}[Value]),
    SC = Table.SelectColumns(Result2,{"Label", "Value1", "Value2", "Result1", "Result2"})
in
    SC
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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