You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Matching a range of days

Advertisement

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!

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.

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Hey, thanks for the UDF! I don't know VBA, so can only create UDFs using spreadsheet functions. Appreciate your help. |

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

Answers by Expert:

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/

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