Finding least sum of differences

[Wintio]

Hi,


I have a rather large table (5760 rows and 10 columns) and another table of the same sort of data(1440 rows and 10 columns). What I need to do is find for each row in the smaller table, the row in the larger table which has the least sum of differences (ie. the minimum of "column 1, table 1 - column 1, table 2" + "column 2, table 1 - column 2, table 2" + .... up to column 9) and once I identify which row is closest, I need to get the number in the 10th column of the larger table.


I was able to do this for one row of the small table by making a new table where each column had for example- =$A$1-L1 (where table 1 is A1:J1440 and table 2 is L1:U5760) and filling out another 5760*9 table, with the 10th column (the value I needed) copied across, then taking the sum of each row, then putting the sum column first, reordering the rows from smallest to largest (since apparently vlookup only works if they're in order, which required me to copy the table out using values so that all the formulae didn't just recalculate), finding the minimum of the sum column and then using =vlookup(the minimum value I found, table 3, 11)....


So that did it for one row, I need this value for all 1440. I would ideally like to find that there's really some function like =min((A1-LX)+(B1-MX)+(C1-NX)....) where it was possible to check all the X's, but have them always look at the same row, not just find the individual best matches for each column.


It's hard to explain but I hope that helped.

 

[Hui]

Update the Pmupdate sub as below

[pre]

Private Sub pmupdate_Click()

Application.ScreenUpdating = True
Worksheets("Conv Schedule").Select
Worksheets("Conv Schedule").ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=cbounit.Value

ActiveCell.SpecialCells(xlLastCell).Offset(, -4).Value = cbopmdate.Value
ActiveCell.SpecialCells(xlLastCell).Offset(, -1).Value = cbotechname.Value
Application.ScreenUpdating = True

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
End Sub

[/pre]

 

[Hui]

Wintio


Firstly, welcome to the Chandoo.org Forums


I'm thinking about using a Linest in conjunction with a data Table to tackle this

You might want to have a read of: http://chandoo.org/wp/2011/06/20/analyse-data-like-a-super-hero/

Linest and some of the other Statistics functions can calculate least squares when fed an array of data

Data Tables give you that ability


Otherwise you may want to post a file somewhere so we can have a look at what your trying to do

 

[Wintio]

Hey Hui,


Thanks for the quick reply. I read that page, and while I couldn't figure out a way to use Linest, I did learn about offset, which I hadn't known before. From the offset link I learnt about macros... and several hours later I have a macro that is redoing my manual method for each row (as I type this it just went by row 200, lets hope the pc doesn't crash =D).


It'll take a little while, but it's a pretty nice fix. And I've learnt a lot tonight.


Thanks again,

Wintio