Excel/Matching a range of days
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:
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.)
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
Matching = ""
in cell D2 put
and fill down.