Home |
Search |
Today's Posts |
#1
|
|||
|
|||
different length columns
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 -- dsal ------------------------------------------------------------------------ dsal's Profile: http://www.excelforum.com/member.php...o&userid=24846 View this thread: http://www.excelforum.com/showthread...hreadid=383948 |
#2
|
|||
|
|||
What is your criteria and priority for matching?
"dsal" wrote: 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 -- dsal ------------------------------------------------------------------------ dsal's Profile: http://www.excelforum.com/member.php...o&userid=24846 View this thread: http://www.excelforum.com/showthread...hreadid=383948 |
#3
|
|||
|
|||
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 -- dsal ------------------------------------------------------------------------ dsal's Profile: http://www.excelforum.com/member.php...o&userid=24846 View this thread: http://www.excelforum.com/showthread...hreadid=383948 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 | +-------------------------------------------------------------------+ -- dsal ------------------------------------------------------------------------ dsal's Profile: http://www.excelforum.com/member.php...o&userid=24846 View this thread: http://www.excelforum.com/showthread...hreadid=383948 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns | Excel Discussion (Misc queries) | |||
can't insert columns between columns | Excel Discussion (Misc queries) | |||
Clearing information in certain columns | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Columns in Excel will not allow user to click in them | Excel Discussion (Misc queries) |