Comparing Multiple fields



I am attempting to compare the ROUNDOPENDATE to the OpenDate.
If any Name1 has any ROUNDOPENDATE and that matches any Name1 OpenDate then
list which product matches. In the following example Name1 product would be
ATM and Name2 would be ATM and USavings.
If I could programically fill the proper ROUNDOPENDATE for each product that
doesn't have an ROUNDOPENDATE, then i could easily compare the dates. But
there are 35K rows.

Name1 OpenDate ROUNDOPENDATE Product
Name1 03/17/1999 02/15/2007 Classic Account
Name1 02/15/2007 ATM
Name1 02/15/2007 USavings
Name2 02/14/2009 02/14/2009 MChecking
Name2 02/14/2009 ATM
Name2 02/14/2009 USavings
Name3 11/20/1997 USavings
Name4 07/28/2003 Simply Free
Name4 10/16/2009 ATM
Name4 05/15/2009 05/15/2009 MSavings
Name5 02/19/2008 05/14/2009 Bonus Checking
Name6 08/21/2009 ATM
Name6 08/18/2009 ATM
Name6 08/17/2009 ATM
Name6 08/17/2009 MSavings
Name6 05/14/2009 USavings
Name7 07/24/2004 02/07/2007 Simply Free
Name7 11/22/2009 11/22/2009 Bonus Checking
Name7 12/01/2009 ATM
Name7 02/11/2009 ATM
Name7 07/26/2007 USavings
Name7 04/11/2002 USavings
Name8 01/30/2009 ATM
Name8 11/23/2004 Simply Free
Name8 11/23/2004 USavings
Name9 11/26/2007 11/26/2007 Simply Free
Name9 02/11/2009 ATM
Name9 11/26/2007 USavings
Name9 11/26/2007 USavings


Should the ROUNDOPENDATE for any given name always be the same date
regardless of the Open Date? It kind of looks that way to me, but I'm not
sure because Name5 has 2 different dates (open date 2/19/08, roundopendate is
5/14/09). Actually, not even sure that plays into it.

Is this what you want:
Given a NAME with a ROUNDOPENDATE, find all Names in the list that have an
OpenDate = that original ROUNDOPENDATE and same NAME and list the Product
from that match/those matches?

Daryl S

Eyespike1 -

If the data is sorted as is suggested by your sample, and you would like to
copy the RoundOpenDate down to all blank cells below it (stopping with the
next RoundOpenDate), then try this:

Make a backup copy of your spreadsheet just in case...
Use a new column for the 'complete' RoundOpenDate, say column E. This
assumes the current RoundOpenDate is in column C and the titles are on row 1.
Put this in E2:
Drag or copy/paste this down the column. If the numbers don't look like
dates, then format the column as dates.
Copy column E and Paste Special | Values over column C. (You may need to
re-format as dates)
Delete column E.

Now you can add your test for the dates you wanted.


Yes, the Roundopendate will be the same.
Daryl, that worked perfectly! Thanks. I also found the following which
worked as well

Select the cells, use (F5) Edit / Go To... Special Blanks OK, then type
=, press the up arrow key once, then press Ctrl-Enter. Then copy the entire
column and pastespecial as values.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question