different length columns

[dsal]

Hey guys, great site here and very helpful

I have an issue here with data that needs to be used, here goes.

A ______ B _______ f(A) ______ f(B)

1.1 _____ 1.0 _____ value _____ value
1.8 _____ 2.5 _____ value _____ value
2.5 _____ 2.7 _____ value _____ value
3.2 _____ 3.6 _____ value _____ value
3.7 _____ _ _ _____ value


The above is a representation of what is going on. I have 4 columns.
A and B represent a location in space, and f(A) and f(B) are data
values at these respective locations.

I need to take the difference between f(A) and f(B), but to do that, I
must first match up as close as possible columns A and B. For example,
in the above set I would want to match up...

1.1 with 1.0
1.8 would be deleted, as well as the f(A) value corresponding to it
2.5 with 2.5
3.2 with 2.7
3.7 with 3.6


This example would be easy to do manually, except in reality the
columns do not match up this nicely, and A and B are about 100 and 80
cells long respectively.

My thoughts on solving this would be to write a code that would take
the difference between columns A and B, and to use the two cells with
the -smallest- difference between them, and the corresponding values of
f(A) and f(B) to achieve the goal. The end result must be columns of
equal lengths, so some cells need to be deleted.

I hope I have explained this problem well. Please ask questions if
something needs clearing up.

Thanks alot,
-Dan

 

[Guest]

What is your criteria and priority for matching?

 

[dsal]

I guess a good way to put it is I want a cell from A to match up with
EACH cell of B, b/c there are less cells in B than A. The rest of the
cells in A can be deleted. So I want to make A and B equal lengths, by
matching up as close as possible numerially each cell in B with a cell
in A.

Then, once they are equal lengths, to have the corresponding cells from
f(A) and f(B) to be arranged in the same way A and B are arranged.

I hope this clears things up a little. Thanks.

Dan

 

[Herbert Seidenberg]

1. Name A SetA
2. Name B SetB
3. Create a column with the formula =(SetA-SetB)^2
4. Name it FitAB
5. Delete row in SetA and f(A) and "Shift cells up"
6. If FitAB in that row and the next row decreases
go to 7.
Otherwise, hit UNDO and go to 7.
7. Select next row in SetA and go to 5.
8. Repeat 5, 6 and 7 to end of SetA

 

[dsal]

Hey, I appreciate the help Herbert, but I believe the method you
suggested has me doing the operation manually. I want Excel to choose
the best matching cells automatically. Any more suggestions anybody?

I have attached the basic file I'm working on, maybe it will be more
self-explanatory.


+-------------------------------------------------------------------+
|Filename: excelhelp.txt |
|Download: http://www.excelforum.com/attachment.php?postid=3564 |
+-------------------------------------------------------------------+

 

[Herbert Seidenberg]

If you want Excel to do the operation automatically,
just code it as VBA.
Sub fitLS()
Dim lengthA As Integer
Dim lengthB As Integer
Dim Cull As Single
Dim Fit1 As Single
Dim Fit2 As Single
Dim Fit1B As Single
Dim Fit2B As Single
Dim kg As Integer
lengthB = Range("Bcol").Count 'length of B
For kg = 1 To lengthB - 1 'for each B
Cull = Range("Acol").Cells(kg, 1) 'store A cull
Fit1 = Range("FitAB").Cells(kg, 1) 'store fit
Fit2 = Range("Sumfit") 'store least squares
lengthA = Range("Acol").Count 'new length of A
Range("Acol").Range(Cells(kg + 1, 1), Cells(lengthA, 1)).Copy _
Range("Acol").Range(Cells(kg, 1), Cells(lengthA - 1, 1)) 'shift up,
Range("Acol").Cells(lengthA, 1).ClearContents 'same as delete
Fit1B = Range("FitAB").Cells(kg, 1) 'store new fit
Fit2B = Range("Sumfit") 'store new least squares
If Fit1B > Fit1 Or Fit2B > Fit2 Then 'if delete of A doesn't help
Range("Acol").Range(Cells(kg, 1), Cells(lengthA - 1, 1)).Copy _
Range("Acol").Range(Cells(kg + 1, 1), Cells(lengthA, 1)) 'shift down
Range("Acol").Cells(kg, 1) = Cull 'restore A cull
End If
Next kg
End Sub
On your spreadsheet, add this formula
=SQRT(SUM(FitAB))
and name it Sumfit