@shahzad_afzal
You do realize, I trust, that your first example (and all since) have been SAN-based sorts. So if that's not what you want (or only as a last resort), then it remains confusing as to what you actually need here.
Your original request was for a formula to generate a date, the max date for previous transactions for the given SAN ID. Unless I'm mistaken @mtarler has given you several workable solutions to that original question.
You just said in your last message that the revised spreadsheet I gave, separating the input from an output, was, in your words working perfectly fine on SAN base sort.... And I'll remind you, first, that a SAN base sort is what you originally presented as needing a solution. So if mine was working perfectly, what remains to be done? Second, that's an output report, so if in fact it's not perfect--if you want some other kind of sort, for example--then describe that. The WHOLE POINT was to separate the raw data from the output. The SORT function enables us to take the raw input data and sort it in a variety of ways; I chose SAN based because that's what you'd given us as needing to be fixed. If that's not in fact what you want, then tweak it; sort it a different way. Don't tell me it's "working perfectly" and then ignore it.
My point so far: from all appearances, it looks like you've gotten workable answers to the dilemma you first presented.
Now, however, let me go back to one of your earlier messages. I noticed something there that may help resolve the confusion in the room. You said a few days ago something that we may have missed at the time:
I would love to sort this by activity date as every month need to generate a month activity report and revenue report out of it...
And maybe the crux of the matter here--and what would actually answer the questions @mtarler and I have been asking, would resolve the confusion we've expressed--centers on this monthly activity report. Maybe what you've been showing us isn't really what you need at all; it's just closing the loop on an individual transaction. What you really are needing to produce is this monthly activity report.
So let me ask this: what does that required monthly Activity Report need to show? I would assume--but want your confirmation and clarification--that a monthly activity report would consists of such things as:
- Count of active customers at end of prior month
- Count of new additions during the month
- Count of terminations during the month
- Counts of upgrades/downgrades
- Other?
- Current Active customers at end of current month
Please clarify or correct. If you have an example of that monthly activity report, could you post it? How is it generated? What relationship does the spreadsheet you've been sharing with us have with that monthly activity report?
Do you also do something like that on a daily basis?
Now: you don't need to answer any of those questions. If the monthly process is working just fine, I'll be glad to hear it. If all you needed was the formula you originally asked about, then let's go back to my earlier points here, that it appears from the record that you've gotten workable answers....what in what you've received from @mtarler or me needs further revision, or have you in fact received a workable answer? If not, you're going to need to be a lot clearer on exactly what you need.
