Sunday, September 18, 2016

[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:
[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:
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:
and I put that formula into column E.
And to get the specific maximum date, I put this formula into column F2 initially:
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.

Related: How Can You Update & Download Toshiba Satellite 5105-S501 Intel PRO 10/100 Driver v.,How to Update & Download Toshiba Satellite C655D-S5139 BookPlace v.2.2.7530 driver,Method to Update & Download Toshiba Satellite L655-S5073 Realtek Wireless LAN Driver v.2.00.0011,Method to Update & Download Toshiba Satellite L840-ST2N01 Sleep Utility v.1.4.0022.000104 driver,Best Way to Update & Download Toshiba Satellite Pro S300-EZ2502 Extended Tiles for Windows Mobility Center v.1.01 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17,Way to Download RealTek RTL8100C(L) Driver v.5.01,Way to Download RealTek RTL8100E Drivers v.694,Way to Update & Download RealTek RTL8101L Auto Installation Program v.6.110 driver,Method to Update & Download RealTek RTL8111G PXE and RPL ROM code v.2.58 driver,How to Update & Download RealTek RTL8411B(N) Driver v.10.003,Best Way to Update & Download ASUS A53SV nVidia Graphics Driver v.,Method to Herunterladen ASUS K75VJ Intel Rapid Storage Technology Treiber v.,How Can You Update & Download ASUS CG8580 Intel Chipset Driver v.,Method to Update & Download ASUS K41VD Intel INF Update Driver v.,Way to Update & Download ASUS Pro70T NB Probe v.3.0.0026 driver,Can't Download PP Templates Automatically To Windows 7 W/IE 8,Can't Change Drive Letter - Vista,Can't Change Homegroup Sharing Permission,Can't Connect XP Shared Printer To Win7 Except As A Local Printer.__,Can You Connect The LAN Adapter For Wii On Your Windows 7 Laptop?
Read More: [Solved] Cant play spades help,How to Fix - change Excel 2007 colors to 2003 colors?,Tech Support: Cant log on to internet explorer,Troubleshooter of Error: cant view website pictures,How to Fix Problem - Cant install Easislides?,Can't access c drive to open programs.,Can you use just one hard drive to store a backup disk image and restore for multiple computers?,can not install Mcafee due to Java script error,,Can Word's track changes comments print in the outside border?,cannot install old software program because computer missing indeo codec driver

No comments:

Post a Comment