| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing Multiple fields
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? "eyespike1" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing Multiple fields
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: =IF(ISNUMBER(C2),C2,E1) 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. -- Daryl S "eyespike1" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Comparing Multiple fields
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. "Daryl S" wrote: 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: =IF(ISNUMBER(C2),C2,E1) 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. -- Daryl S "eyespike1" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing fields in an excel worksheet | Excel Worksheet Functions | |||
| HELP! I need to make an IF Statement comparing date/time fields.. | Excel Worksheet Functions | |||
| Comparing text fields | Excel Worksheet Functions | |||
| Comparing and potentially adding two fields | Excel Worksheet Functions | |||
| Comparing Date Fields | Excel Worksheet Functions | |||
Linear Mode
