How to Remove Popups?: [Anwsered] checking latest date within a list

checking latest date within a list

I have a list that is going to be getting regular updates (field bizdev folks putting in activities by deals). I need the list into which they enter information, to verify that for the particular deal they are enterring activity on is later than the most
recent date before it. The list will look something like this:

Unique ID             Deal            Date               Activity
1                           ABC Corp      3 Jan 11        Initial contact
2                           XYZ Corp       5 Jan 11        Initial contact
3                           PDQ Corp     10 Jan 11      Called on mngr
4                           XYZ Corp       15 Jan 11      Scheduled first meeting

and so on. The trick is severalfold:

1) the list will not be sorted by date order. It will be whatever order they enter, and that means whenever a deal has activity, it gets an update. In theory, if they do what they are supposed to, the per-deal order stays correct, in that subsequent entries
mean subsequent dates. In that case, the problem I'm trying to solve is not actually a problem. However it's conceivable they'll try to 'catch up' on entries, and fill in the next line (which will give them a unique id that is incremental), but will try
to use a date that is before the last date for that deal.
2) the unique ID is simply a "+1" formula from the cell above it

the problem from the above comes because this list is feeding several pivot tables, two of which are within this book, and one or two of which will be a summary pivot that resides in a manager's file, which feeds from each individual. starting from the
tables within this individual workbook, I'm trying to accomplish the following two things:
1) table showing sequenced activities, per deal.
For this, I've constructed a Deal/Date/Activity row section, and the data section is simply the Unique ID (I'm not looking for the math, but rather the text to flow a certain way). this groups by deal, then
date order, and then shows me the last activity. However the unique ID does not necessarily end up being the last Unique ID for that deal. in the above, if the person were to enter ID 5 as PDQ-7 Jan-Initiated contact, then I'd have a date order which would
put 3 after 5.
2) table showing last activity. Here is where the above scenario becomes a problem (I think, unless there is a way around it that i'm not seeing). I want there to be a snapshot that shows last activity per deal. I have a table that does Deal/Date/Activity
grouping (with Unique ID in the data area), I've filtered Date for "Top 10" (and used "show top 1" of the Unique ID data field). Now what this allows me to do is show the last item per deal, until someone enters the problematic entry i've noted above. when
that happens, I end up with the highest Unique ID for the deal, but not necessarily the last dated entry.

Hopefully the above is explained well enough to recreate, and is solvable. I'm sure there is some MAX or other option that can help on the pivot side, and/or some validation that can help on the list entry side (multiple considerations, because it's validation
of a MAX date within a subset of the list that is related to the deal in question). i'm just not getting it myself.

thx for the helpl

Keys to the Problem checking latest date within a list

Download Error Fixer for Free Now

The best generic description of the formula probably comes from the source of the basic formula that I found at:
http://www.exceluser.com/explore/last-item-in-list.htm
[You really didn't think I kept all these things in my head did you? :)]
What it is doing is finding the maximum/greatest date associated with the company on the row within the rows above it and comparing that to the date entered on the row. If you haven't entered a date on the row yet, or if the date you do enter is equal to
or greater than the found date from the earlier rows, then nothing is displayed, otherwise you get the warning.
What the formula does NOT do is determine if the date entered should be entered at a position perhaps 3 or 4 related entries earlier in the list. It's just saying that the date is greater than the last date entered for that company.
Now, you can put this formula into another column and it will show you the row number that the max date for the company was found in above the current entry.
This would go into a cell on row 2:
=SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2)))
And to keep from always displaying a value there and being annoying in doing so, assuming that the previous warning formula is in column G (so the text will show up without being cut off by the results of a formula being in the column to its right) you can
put in a test:
=IF(G2="","",SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2))))
and I put that formula into column E.
And to get the specific maximum date, I put this formula into column F2 initially:
=IF(G2="","",INDEX(C:C,SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2)))))
Format column F as dates also.
OR you can combine it all into one great big formula like this (again in row 2)
=IF(OR(C2=0,C2>=INDEX(C:C,SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2))))),"","Please make this entry in an inserted row in the proper sequence above row #" & SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2))) & " with date " & TEXT(INDEX(C:C,SUMPRODUCT(MAX((B1:B$2=B2)*ROW(B1:B$2)))),"dd-mmm-yyyy"))
Here's its output when I added that invalid entry earlier:
Please make this entry in an inserted row in the proper sequence above row #6 with date 15-Jan-2011
So all 3 formulas become just one big one.

Run CHKDSK to Fix checking latest date within a list

Go to the Start button.
In the Search box, type "cmd" (without quotes) and press Enter.
At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
Reboot your computer.

An error might pop up:

"Access denied as you do not have significant privileges. You have to invoke this utility in elevated mode."

If you receive the above error message, it means you are not logged into you computer as an Administrator. If that's the case, follow these steps to run the command prompt in elevated mode:

Go to theStart button.
Go to "All Programs > Accessories" and right click on "Command Prompt".
Select "Run as Administrator".
In the Search box, type "cmd" (without quotes) and press Enter.
At the command prompt, type "chkdsk /r /f" (without quotes) and press Enter.
Reboot your computer.

Recommended Method to Repair the Problem: checking latest date within a list:

How to Fix checking latest date within a list with SmartPCFixer?

1. Download SmartPCFixer. Install it on your computer.

2. After the scan is done, you can see the errors and problems which need to be fixed.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in this software. Like dll downloading, windows updating and print spooler error repair.

How to Remove Popups?

Sunday, September 18, 2016

[Anwsered] checking latest date within a list

checking latest date within a list

Keys to the Problem checking latest date within a list

Run CHKDSK to Fix checking latest date within a list

Recommended Method to Repair the Problem: checking latest date within a list:

How to Fix checking latest date within a list with SmartPCFixer?

No comments:

Post a Comment