Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I would like to be able to take one set of data/value data and match another set to it. The data sets do not have values on the same days, so I would like to have a logic statement along the lines of “if the date/value of Data Set 2 is within X days (plus or minus) of the date/value Data Set 1, then match Data Sets 1 and 2, else don’t match”. For example:

    Data Set 1 values:
    1 1/3/1995 25.41
    2 1/18/1995 26.93
    3 2/1/1995 26.77
    4 2/16/1995 26.49
    5 3/1/1995 26.09

    Data Set 2 values:
    1 1/10/95 24.41
    2 1/23/95 24.96
    3 2/2/95 24.91
    4 2/18/95 24.91
    5 3/5/95 24.81
    6 3/18/95 24.36

    If I said ‘match within 3 days’, then Data Set 1 2/1/95 would match with 2/2/95; 2/16 with 2/18. No other matches would be made.

    If I said ‘match within 6 days’, then Data Set 1 1/18 would match with 1/23, 2/1 with 2/12, 2/16 with 2/18. No other matches would be made.

    A typical set of data are attached.

    Is there a way to do this?

    Thanks!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you use a wide margin, there might be multiple matches. What do you want to do with those?

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796854' date='07-Oct-2009 21:53']If you use a wide margin, there might be multiple matches. What do you want to do with those?[/quote]
    match with the one with closest date

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That needn't be unique, but OK.

    In what kind of format would you like the output? One could add the matches from Sheet2 in columns C and D on Sheet1 (or vice versa). Or create a new sheet with only the matching data from Sheet1 and Sheet2.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Do these functions work?

    The brunt of it is done with a modification of a VlookupAll function I created. But what is called the MatchRange function which loops through the min/Max of target and range and then combines all the matches found.

    [codebox]
    Option Explicit
    Function MatchRange(dValue As Double, rngAll As Range, _
    iCol As Integer, dRng As Double, _
    Optional sSep As String = ", ")

    Dim sTemp As String
    Dim x As Double
    'On Error GoTo ErrHandler
    sTemp = ""
    For x = dValue - dRng To dValue + dRng
    sTemp = VLookupAll(x, rngAll, iCol, sSep)
    If sTemp <> "" Then
    MatchRange = MatchRange & sSep & sTemp
    End If
    Next x
    If MatchRange = "" Then
    MatchRange = CVErr(xlErrNA)
    Else
    MatchRange = Right(MatchRange, Len(MatchRange) - Len(sSep))
    End If

    ErrHandler:
    If Err.Number <> 0 Then MatchRange = CVErr(xlErrValue)
    End Function
    Function VLookupAll(vValue, rngAll As Range, _
    iCol As Integer, Optional sSep As String = ", ")

    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler

    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
    If rCell.Value2 = vValue Then _
    VLookupAll = VLookupAll & sSep & _
    rCell.Offset(0, iCol).Value
    Next rCell
    If VLookupAll = "" Then
    VLookupAll = ""
    Else
    VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
    End If
    ErrHandler:
    If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
    End Function
    [/codebox]

    Call it for example In 'DataSet1" cell C1:
    =matchrange(A2,'Data Set 2'!$A$2:$A$344,1,3)

    and then copy/autofill this down the column. it will match from A1-3 to A1+3 in the range 'Data Set 2'!$A$2:$A$344 and combine the results of the values in the 1st column to the right. The default separator is ", ". If you want to use a different separator (eg "; ") then modify the formula to:
    =matchrange(A2,'Data Set 2'!$A$2:$A$344,1,3,";")

    Steve

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='797974' date='14-Oct-2009 19:50']That needn't be unique, but OK.

    In what kind of format would you like the output? One could add the matches from Sheet2 in columns C and D on Sheet1 (or vice versa). Or create a new sheet with only the matching data from Sheet1 and Sheet2.[/quote]
    create new sheet

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks.
    this is a back burner item and i'll let you know

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •