I have a list of ID numbers with a name and a birthdate assigned to each
number. There may be several instances of each ID number/name/date row--that
is, the same ID number/name/birthdate may appear several times. The rows are
different in that each ID number/name/birthdate has it's own "occurance"
date--i.e. there are ten of the same ID numbers, each with the same names and
birthdates, but with different occurance dates. I need to sort the occurance
date as either being before or after a certain date. Then I need totals of
rows for eitehr before or after the certain date. These totals are not for
the entire list, but for the groups of each number/name/date. I can create
these totals without a problem by using DataSubtotals. The output it gives
me is another "Totals" line under each group of ID number/name/date
instances. The problem is that the Totals line only lists the group's' ID
number without listing the name and birthdate. What I need is to have the
totals of either before or after the certain date for each group of ID
number/name/birthdate, however it must display, on its own row, the ID
number, name, birthdate and of course the totals for both before and after
the certain date. I hope this makes sense--it is complicated but seems like
it should be possible--like I said, I can get the totals for before and after
the first date on a new row that is labelled by the ID number but I need to
have it labeled by ID number, name, and birthdate. Then if this were a
perfect world, I would be able to seperate the Totals lines from the original
sheet, to be used on a fresh sheet of its own.
Thank you for any assistance you may offer!
-Tomas

Samot,

You have a few options, but a lot to do!

1) If everything looks good EXCEPT you want to see the names and birthdates
beside the IDs, then you can use a Vlookup to bring them in.

Assume your ID is in column A, your name is in Column B, and Birthdate is in
column c. Also, assume the "summary ID" which is in need of a related name &
birthdate is in range A540. Finally, assume the full data set is range
A1:c500.

The formula for your names is: =vlookup(A540,A1:C500,2,false)
The formula for your birthdays is: =vlookup(A540,A1:C500,3,false)

The vlookup formula finds your initial value (in this case, the ID) in the
LEFTMOST column of your range (array), then brings in the value you've
identified as being in the "nth" column over (2nd column for names, 3rd for
birthdays). The "False" is included to force exact matches. If you're ok
with "close" matches, you can put in "true" or leave it out altogether. From
my experience, always use "False"...

Since there are a few ways to skin this cat, let me know if this
does/doesn't work for you.

"samot" wrote:

I have a list of ID numbers with a name and a birthdate assigned to each
number. There may be several instances of each ID number/name/date row--that
is, the same ID number/name/birthdate may appear several times. The rows are
different in that each ID number/name/birthdate has it's own "occurance"
date--i.e. there are ten of the same ID numbers, each with the same names and
birthdates, but with different occurance dates. I need to sort the occurance
date as either being before or after a certain date. Then I need totals of
rows for eitehr before or after the certain date. These totals are not for
the entire list, but for the groups of each number/name/date. I can create
these totals without a problem by using DataSubtotals. The output it gives
me is another "Totals" line under each group of ID number/name/date
instances. The problem is that the Totals line only lists the group's' ID
number without listing the name and birthdate. What I need is to have the
totals of either before or after the certain date for each group of ID
number/name/birthdate, however it must display, on its own row, the ID
number, name, birthdate and of course the totals for both before and after
the certain date. I hope this makes sense--it is complicated but seems like
it should be possible--like I said, I can get the totals for before and after
the first date on a new row that is labelled by the ID number but I need to
have it labeled by ID number, name, and birthdate. Then if this were a
perfect world, I would be able to seperate the Totals lines from the original
sheet, to be used on a fresh sheet of its own.
Thank you for any assistance you may offer!
-Tomas