On Thu, May 28, 2009 at 3:15 PM, Brian Barker <b.m.bar...@btinternet.com> wrote: > At 14:10 28/05/2009 -0700, Mark Knecht wrote: >> >> I have a large table of weekly equity data coming out of another program >> from which I need to collect a compacted table of the week by week results. >> The table is currently about 10 years of data with (typically) 5-10 >> lines/week. The input data to calc looks like the following data, somewhat >> simplified for the sake of this email: >> >> Year Week Equity >> 2004 2 374 >> 2004 2 -52 >> 2004 3 154 >> 2004 4 774 >> 2004 5 -426 >> 2004 6 774 >> 2004 6 618 >> 2004 6 622 >> 2004 6 1396 >> 2004 7 644 >> 2004 7 218 >> 2004 9 354 >> 2004 9 178 >> 2004 10 454 >> 2004 10 28 >> >> The resulting table I want to get in calc should look like this: >> >> 2004 2 -52 >> 2004 3 154 >> 2004 4 774 >> 2004 5 -426 >> 2004 6 1396 >> 2004 7 218 >> 2004 8 0 >> 2004 9 178 >> 2004 10 28 >> >> The final table consists of the last line of a given week number. Note >> that if a week is missing I need to fill that in with a 0 value. >> >> I wonder if someone knows of way to do this. > > Suppose your original table is in columns A, B, and C - and that your new > table is in D, E, and F. Create the values in D and E (from row 2 down) > manually. In F2, put something such as: > =VLOOKUP(E2;B2:C16;2)*(COUNTIF(B2:B16;E2)>0) > and fill it down the column. > > The VLOOKUP() function searches the first column of the range B2:C16 for the > value in E2 - the week number - and happily finds the last where there are > more than one. The "2" parameter means that you want the value from the > second column - here column C - returned. That will do everything you need > except for dealing with your missing week. The COUNTIF() function counts > how many entries there are for the current week; the logical test finds if > this is non-zero. If you multiply a number by a logical value, as here, > FALSE is interpreted as zero and TRUE as one. The result of multiplying by > this is not to change the other values but to provide a zero value for the > missing week. > > If you need to incorporate the year into the test as well as the month, you > may need to combine these values in some way before using VLOOKUP(). > > I trust this helps. > > Brian Barker >
Brian Barker - saving the world one Calc user at a time. It does help. I'll need to spend some time putting it together but the idea makes sense. Thanks, Mark
