Finding the closest value within a list

[4jeffthornton]

Can anyone help me with a formula to find the closest property to the subject?

I have tried the advanced filter, but the results were a range and not the closest the subject.

I need a minimum of 5 properties but more is better.

The subject criteria is: NEIG=1101, STYLE=RANCH, GRADE=B- AND TOT SQFT=2100

The STYLE must be absolute, but the NEIG, GRADE and SQFT can vary but prefer to match the subject.

NEIG STYLE GRADE CDU YEAR BUILT TOT SQFT

1098 RANCH C+ 5 1961 1805
1099 RANCH A- 6.6 1988 2996
1099 CONDO B 6.4 1970 2942
1099 RANCH B- 6.4 1955 2752
1099 RANCH B+ 6 1968 2461
1100 RANCH A 7.2 1969 5015
1100 RANCH A- 7.6 1951 3431
1100 CONDO A+ 7.8 1968 3131
1101 RANCH B 6.8 1959 1798
1101 RANCH B- 5.4 1954 1981
1101 RANCH B- 5.2 1960 1883
1101 RANCH B- 6.4 1963 2493
1101 CONDO B- 6 1964 2295
1101 RANCH B- 6 1964 2646
1101 CONDO C 5.4 1955 1691
1101 RANCH C 4.1 1961 1743
1101 RANCH C+ 6 1955 1662
1101 RANCH C+ 5.2 1952 1459


Thank You,

Jeff

If anyone needs the actual excel sheet I would be happy to sends a copy.

 

[jumbledore]

Can you just check the excel sheet in the link below and see if this is what you want?

http://www.filedropper.com/bookxxx

 

[Mackers]

Hi Jeff

You will need to define what you mean by 'closest' when matching on multiple criteria, as I think you probably don't just want to check how many exact matches you have.

One way would be to check the proportional difference of each column and then find the result with the lowest combination, but as your YEAR BUILT is only going to span a short period but starts in the 1900s this would throw out the results (i.e. being 10 years out won't be a high proportional difference). Checking the total absolute difference (i.e. finding how many years away + how many sqft away + how many CDU away + how many Grade steps away) would drastically favour TOT SQFT, as the differences there are going to be in the tens or hundreds rather than single digits.

You will also probably need to either change Grade to a number or create a lookup table from Grade to a number so a comparison can be made, in which case you will have to decide if, say, a B+ is worth 0.3 more than a B, and an A- is worth, say, 0.3 less than an A.

Once you have decided how you want to do the comparison we should be able to put together a formula for you.

Hope that helps

Mackers

 

[jumbledore]

Can you just check the excel sheet in the link below and see if this is what you want?

http://www.filedropper.com/bookxxx

Please ignore the sheet. It doesn't work.

 

[jumbledore]

Hi Jeff

You will need to define what you mean by 'closest' when matching on multiple criteria, as I think you probably don't just want to check how many exact matches you have.

One way would be to check the proportional difference of each column and then find the result with the lowest combination, but as your YEAR BUILT is only going to span a short period but starts in the 1900s this would throw out the results (i.e. being 10 years out won't be a high proportional difference). Checking the total absolute difference (i.e. finding how many years away + how many sqft away + how many CDU away + how many Grade steps away) would drastically favour TOT SQFT, as the differences there are going to be in the tens or hundreds rather than single digits.

You will also probably need to either change Grade to a number or create a lookup table from Grade to a number so a comparison can be made, in which case you will have to decide if, say, a B+ is worth 0.3 more than a B, and an A- is worth, say, 0.3 less than an A.

Once you have decided how you want to do the comparison we should be able to put together a formula for you.

Hope that helps

Mackers

Can you check my sheet at http://www.filedropper.com/bookxxx

I am able to get the columns neig, cdu,year built and tot sqft but can't seem to get the columns with Style and grade correct.

Thanks

 

[Mackers]

Hi jumbledore

I am unable to access those sheets from my location, so I can't check what you have done, but the OP will still need to decide on a weighting for 'closeness' if a formula is to be constructed.

Mackers

 

[jumbledore]

Hi jumbledore

I am unable to access those sheets from my location, so I can't check what you have done, but the OP will still need to decide on a weighting for 'closeness' if a formula is to be constructed.

Mackers

I took the closeness as the top minimum of absolute differences

 

[4jeffthornton]

I will tomorrow due to the excel sheet in my work computer at work. thank you for helping me out.

 

[4jeffthornton]

I will download the excel sheet tomorrow it is in my work computer at work. thank you for helping me out.

 

[jumbledore]

I created a VBA code for this. I used a number of online resources so none of this is my original work. Basically this macro assumes your data is in cols A to F in sheet 1 and the criteria is entered in H1,I1 and J1 as NEIG, STYLE and TOT SQFT. When you run the macro the data with the criteria will appear in sheet 2. For closeness what I have done is taken the minimums of the absolute differences between NEIG/TOT SQFT and the criteria and sorted the rows accordingly. I am not sure if this is what you want but I can work on it with your suggestions:

Sub closest()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim Rng As Range
Sheet2.Activate

Range("a1").CurrentRegion.Clear

Sheet1.Activate

Set Rng = Range([B1], Range("B" & Rows.Count).End(xlUp))

x = Cells(1, 8)
y = Cells(1, 10)


On Error Resume Next
    With Rng
        .AutoFilter , field:=1, Criteria1:=Cells(1, 9)
        .SpecialCells(xlCellTypeVisible).CurrentRegion.Copy Sheets("Sheet2").Range("A1")
        .AutoFilter
    End With
On Error GoTo 0

Sheet2.Activate

Set Rng1 = Range([A1], Range("A" & Rows.Count).End(xlUp))

Set Rng2 = Range([F1], Range("F" & Rows.Count).End(xlUp))

numel = Rng1.Count



For i = 2 To numel
        Cells(i, 7) = Abs(Rng1(i) - x) + Abs(Rng2(i) - y)
Next i

Range("A1").CurrentRegion.Select
Selection.Sort Key1:=Range("G1"), Order1:=xlAscending, Header:=xlYes


Range("g:g").Clear

Application.EnableEvents = True

End Sub