Excel Formula Help

mathetes Thanks for your time and help...

Regards

shahzad_afzal 

You don't seem to realize that what I've said is that until you give us feedback on the approaches we've given you, which appear to BE answers, functional, that we're done.

Go back and re-read, carefully, the most recent messages you've gotten. For example, you said that my different approach was working perfectly, from a SAN-ID sort basis. And I pointed out that the SAN ID sort is what you've been giving us all along. So if it's working "perfectly," don't ignore it. Suggest a different way to sort it. You can in fact tweak the formula yourself to change the basis for the sort; it's all accomplished by the SORT function. The whole point there was that when you separate the output from the input, you can tweak the output until it is what you need.

From @mtarler you got several formulas and he was left without any answer from you as to whether they worked...and they do appear to.

So, as I said in my previous last post, it appears you have gotten satisfactory answers. If they're not, then you need to do a LOT more to be clear on what is needed, why what you've been given doesn't work. It appears otherwise that you're just ignoring what you've been given already. So it's not that we don't want to help; it's that you don't appear to be able to respond in any substantive way to what you HAVE been given.

mathetes Any luck on the formula please....

shahzad_afzal 

If by any mean; new activity will get date from last instance (activity end) date, everything will work as I need...

If that's your last word on this, then I believe you've gotten more than one way to do that. fini

mtarler Attaching updated sheet...

mathetes you are right about reports...

• 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

If by any mean; new activity will get date from last instance (activity end) date, everything will work as I need...

shahzad_afzal  I am still curious about the 3 equations I presented in my attachment previously and if they work or what doesn't work.  Also, I pointed out that the first row had a start date after the end date and was wondering if that was just a typo in the sample or something that should be expected in this data set.

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.

mathetes Hi; answering all of your pending questions:

1. your version is working perfectly fine on SAN base sort...

2. business is internet services; customer joining, using, lock if not at home for some time and terminate if not needed... Upgrade or downgrade is also available as required...

3. Extracting SAN base report is easy; but on daily base, if I extract and add in the sheet; it will again be date sorted... SAN sorting is still a last resort if date based sorting option is not workable...

shahzad_afzal 

Sorry, but you seem to answer only one question at a time. There were others:

• I need to ask what you thought of the version I created.
• But also, whether you use that or not, you really haven't ever clarified what the whole process is that you are trying to create. It's clear that you're capturing data on various events, but toward what purpose? Does a report sorted by SAN ID have a place? Would it be better to just extract the full history of any given SAN ID? Given that you've said you have thousands of records, it just isn't clear what you really want. The formula is easy once you define things clearly.

Would you please address both of these bullets? The first one is more curiosity--since I spent some time creating an alternative approach, I wondered if it had any value. The second is my attempt to get a much better understanding of the full context.

mathetes Hi; Yes I am referring to last instance for the given SAN ID...

Sorting by SAN ID is the last resort if unable to get last instance for the given SAN ID; else need is sort by activity date...

shahzad_afzal 

I need to ask what you thought of the version I created.

But also, whether you use that or not, you really haven't ever clarified what the whole process is that you are trying to create. It's clear that you're capturing data on various events, but toward what purpose? Does a report sorted by SAN ID have a place? Would it be better to just extract the full history of any given SAN ID? Given that you've said you have thousands of records, it just isn't clear what you really want. The formula is easy once you define things clearly.

To your question: New data addition will always be in the last row; what is the possible option to get the date from the previous instance without sorting by SAN ID? I need to ask again, "previous instance" of what?? Last instance for the given SAN ID? Say that. Last instance over all? Say that. Date and time of start  or end?

mathetes Dear; added more data (see attached) and if sorted by SAN ID, things are perfect...

New data addition will always be in the last row; what is the possible option to get the date from the previous instance without sorting by SAN ID?

shahzad_afzal 

Here's a totally different approach. It uses most of your data but approaches it by separating INPUT from OUTPUT.

The input table uses most of your raw data, but simplified to only three columns and they've been entered in date and time sequence:

• Start date and time
• SAN ID
• Transaction (event)

Then I use two formulas only to create the output.

• The first one is =SORT(Table1,2) which sorts the input table on the basis of the second column, which is SAN ID   It "spills" down to populate however many rows there are in the basic input table. That could be refined if need be, to limit it in various ways, depending on your needs. SORT is one of the new Dynamic Array functions, available only in the newest release of Excel. If this doesn't work for you, that's why.
• The second formula is =IF(C5=C4,B5-TIME(0,0,1),"") and that actually derives the end time from the start time of the NEXT activity for the same SAN ID. In other words, I've reversed your logic. AND I only create the end time at the report stage of things. It's assumed that when you start a new stage of the contract with a given SAN ID, that it means the last stage is now superceded.

I would encourage you to add additional events, new SANIDs, at the bottom of the input sheet. You'll see that the output sheet will automatically keep up with it.

shahzad_afzal 

You wrote: Data entry will be manual like adding new addition date; then adding activity end date, which will help to get next activity start date and so on till termination...

Which doesn't yet make it clear. Of course you add new data by entering an ID, an activity description, and a date. The question was aimed at how the table is maintained, which wasn't so much what data are entered--that's pretty obvious--but whether or not you enter each transaction at the bottom of the table, regardless of which SAN ID it is, or do you insert a row in the section for the given SAN ID? 

Let me take a few minutes to create a mock-up for how transactional databases are generally laid out, with an example of what could then be created as an output report.