PDA

View Full Version : [SOLVED] get latest?



lhs42
04-14-2005, 05:56 AM
Hi

my spreadsheet holds project reports - each as a line item (about 1000)
i can do this manually but its clunky & relies on no human error (not good)

i want to produce a summary that shows latest report for current projects and also projects failing to report - esentially a show"n"tell
something like this
A B C D
a 14/11/2005 2 current
c no return 0 current
d 13/12/2004 23 current

from a list like this
project date result status
a 01/12/2004 90 current
a 26/02/2005 3 current
b 24/05/2005 23 not live
b 19/08/2005 98 not live
a 14/11/2005 2 current
c no return 0 current
d 13/12/2004 23 current


i've made a pivot table which pretty much does the job. but i cant figure out how to get it to only show the latest report for a project. it lists them all - i then go through & delete the old ones. its clunky & subject to error.

all thoughts appreciated - my brain's run out of ideas

OBP
04-14-2005, 11:02 AM
Has your spreadsheet got a report number or a report counter by which you can sort the table?
If it has then in visual basic sort the table by project letter and then Date, thus putting all of the "a"s, "b"s and "c"s etc together and the last a will have the last report date and so on.
Use a for next/loop where the search variable string is set to project a. When it finds an entry that is not "a" you know you have the last report. Copy that information in to a new worksheet or range. Set your search variable string to "b" and repeat for each project.
When the loop has finished re-sort using the Project number or date to return it back to as it was.

Now use the new worksheet or range as the input for your pivot table if you still need it.

acw
04-14-2005, 06:01 PM
Hi

Assuming that your source data is in the range A1:D8 and you want the output starting in column G with G1:J1 containing the headings and G2:G??? containing the project, array enter the formula (ctrl, shift, enter)


=IF(COUNTIF($A$2:$A$8,$G2) = 1,VLOOKUP($G2,$A$2:$D$8,COLUMN()-6),INDEX(B$1:B$8,MAX(($A$2:$A$8=$G2)*($B$2:$B$8=MAX(($A$2:$A$8=$G2)*IF(ISNU MBER(($B$2:$B$8)),$B$2:$B$8,)))*ROW($A$2:$A$8))))


in H2 and copy down / across as required.

The offsets for the vlookup are based on this column positioning.


Tony

lhs42
04-15-2005, 06:12 AM
Guys
many many thanks - 2 different ideas to try. i may even learn how vlookups work! its been on my list of things to do ........
thanks again