You are here:

Excel/Matching a range of days

Advertisement


Question
QUESTION: Hi, Bob, I hope I can explain this well.

I have an involved spreadsheet with columns that include (among other things) Date, Desc, and Amt. Many of these entries have negative pairings (similar to credits and debits). So they may have the same dates, descs, and opposite amounts.

I want to mark these so they're easy to find at a glance. I decided to create a column that would give me the row number of each pair's opposite. (Of course if there is no pair, the formula would be blank.)

Of course, the MATCH function and a hidden COMBO column can do this easily. (So can SUMPRODUCT and array formulas.)

But then I found that in certain cases, the pairs are within 1-3 days of each other. Argh! WEEKNUM from the Analysis Toolpak helps, but still, what if one is on Saturday and the other is on Monday? They won't match up.

Is there someway I can find matches that have the same description, the opposite amount, and dates with 3 days of each other? Ideally, I'd like to return the row number of an entry's pair.

Thanks, Bob! Hope this isn't too much of a challenge.

ANSWER: assuming your data starts in A2 (row 1 being titles), then enter this in D2 via ctrl/shift/enter:
=IFERROR(MATCH(A2&B2&-C2,$A$1:$A$1000&$B$1:$B$1000&$C$1:$C$1000,0),"")
and fill down.
This will return the row # of the corresponding pair. You can format column D like this:
"Match in Row "General


---------- FOLLOW-UP ----------

QUESTION: Thanks for your time, Bob, but your formula does exactly what I've already done. I'm sorry, I must not have explained myself clearly. Let me try again.

I was dismayed to find that in some cases, pairs don't have exactly the same dates, but instead the dates are within 1-3 days of each other. Your formula -- and the ones I put together -- are perfect for finding exact date matches. But they miss the pairs whose dates are close but not exact.

So I need something that will find SameDesc&OppositeAmt&DateWithin1-3Days.

(As I also mentioned in my first message, I tried WEEKNUM from the Analysis Toolpak, but it only works if they're in the same week.)

Thanks!

Answer
I found a UDF easier.

Function Matching(rg1 As Range, rg2 As Range)
   For i = 1 To rg2.Rows.Count
       If rg1(1) >= rg2(i, 1) - 3 And rg1(1) <= rg2(i, 1) + 3 Then
         If rg1(2) = rg2(i, 2) And rg1(3) = (rg2(i, 3) * -1) Then
         Matching = i + rg2.Row - 1
         Exit Function
         End If
       End If
   Next
   Matching = ""
End Function

in cell D2 put
=MATCHING((A3:C3,$A$2:$C$16)
and fill down.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.