Need help with SELECT statement (return MAX date).

I need to return the MAX date either of these 3 columns below.  Thank you so 
you in advance.
Below is the rules and desire results.


IF OBJECT_ID('T1', 'u') IS NOT NULL
  DROP TABLE T1 
GO
CREATE TABLE [dbo].[T1]
(
	[LoanNum] [varchar](10) NOT NULL,
	[OfferPrice] [money] NULL,
	[MgrApprDate] [datetime] NOT NULL,
	[DirApprDate] [datetime] NOT NULL,
	[ExecApprDate] [datetime] NULL,
	[ExitStrategyMainCategoryID] [int] NULL,
	[ExitStrategySubCategory1ID] [int] NULL
)
GO


INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 250000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 250000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 243325.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 245000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 245000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 245000.00, CONVERT(DATETIME, 0x00009cd100d30f24), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 250000.00, CONVERT(DATETIME, 0x00009cd30130635e), 
CONVERT(DATETIME, 0x00009cd400d2017a), CONVERT(DATETIME, 0x00009cd5010dfd30), 
4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('100993', 250000.00, CONVERT(DATETIME, 0x00009cff00deb976), 
CONVERT(DATETIME, 0x00009d0500c44600), CONVERT(DATETIME, 0x00009d050116c730), 
4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 370000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 328000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 370000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 415000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 415000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 385000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 390000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 413500.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 413500.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x0000000000000000), CONVERT(DATETIME, 0x00009c87009523b0), 
4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 410000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x00009c6100d6f0ae), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 425000.00, CONVERT(DATETIME, 0x0000000000000000), 
CONVERT(DATETIME, 0x00009c78010c7d5a), NULL, 4, 4)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 330000.00, CONVERT(DATETIME, 0x00009bed010a7d85), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
INSERT INTO 
dbo.T1([LoanNum],[OfferPrice],[MgrApprDate],[DirApprDate],[ExecApprDate],[ExitStrategyMainCategoryID],[ExitStrategySubCategory1ID])VALUES 
('102930', 330000.00, CONVERT(DATETIME, 0x00009c1500786979), 
CONVERT(DATETIME, 0x0000000000000000), NULL, 2, 1)
go


  SELECT *
    FROM T1
  ORDER BY LoanNum ASC, MgrApprDate ASC, DirApprDate ASC, ExecApprDate ASC;
  go
  
  -- business rules:  Return the MAX date either from any of these 
MgrApprDate, DirApprDate, ExecApprDate columns
  
  -- Desire result:
LoanNum    OfferPrice            MgrApprDate             DirApprDate         
    ExecApprDate            ExitStrategyMainCategoryID 
ExitStrategySubCategory1ID
---------- --------------------- ----------------------- 
----------------------- ----------------------- -------------------------- 
--------------------------
100993     250000.00             2010-01-15 13:30:54.900 2010-01-21 
11:54:36.587 2010-01-21 16:55:00.000 4                          4
102930     330000.00             2009-05-26 07:18:24.403 1900-01-01 
00:00:00.000 NULL                    2                          1
  
0
Utf
3/2/2010 10:21:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
647 Views

Similar Articles

[PageSpeed] 45

Here is one solution (BTW, your desired result set is incorrect, there is another row with greater date):

SELECT LoanNum,
        OfferPrice,
        MgrApprDate,
        DirApprDate,
        ExecApprDate,
        ExitStrategyMainCategoryID,
        ExitStrategySubCategory1ID
FROM (
SELECT LoanNum,
        OfferPrice,
        MgrApprDate,
        DirApprDate,
        ExecApprDate,
        ExitStrategyMainCategoryID,
        ExitStrategySubCategory1ID,
        ROW_NUMBER() OVER(PARTITION BY LoanNum
                          ORDER BY CASE WHEN MgrApprDate >= DirApprDate
                                         AND MgrApprDate >= ExecApprDate
                                        THEN MgrApprDate
                                        WHEN DirApprDate >= ExecApprDate
                                        THEN DirApprDate
                                        ELSE ExecApprDate
                                   END DESC) AS rk
FROM T1) AS T
WHERE rk = 1;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
3/2/2010 10:51:26 PM
Your're right.  I will incorporate into big queries and test it out.  Again, 
Thanks so much for your help.

LoanNum  OfferPrice            KccPaidConcessionsHud MgrApprDate             
DirApprDate             ExecApprDate            ExitStrategyMainCategoryID 
ExitStrategySubCategory1ID
-------- --------------------- --------------------- ----------------------- 
----------------------- ----------------------- -------------------------- 
--------------------------
100993   250000.00             0.00                  2010-01-15 13:30:54.900 
2010-01-21 11:54:36.587 2010-01-21 16:55:00.000 4                          4
102930   413500.00             0.00                  1900-01-01 00:00:00.000 
1900-01-01 00:00:00.000 2009-09-17 09:03:00.000 4                          4



"Plamen Ratchev" wrote:

> Here is one solution (BTW, your desired result set is incorrect, there is another row with greater date):
> 
> SELECT LoanNum,
>         OfferPrice,
>         MgrApprDate,
>         DirApprDate,
>         ExecApprDate,
>         ExitStrategyMainCategoryID,
>         ExitStrategySubCategory1ID
> FROM (
> SELECT LoanNum,
>         OfferPrice,
>         MgrApprDate,
>         DirApprDate,
>         ExecApprDate,
>         ExitStrategyMainCategoryID,
>         ExitStrategySubCategory1ID,
>         ROW_NUMBER() OVER(PARTITION BY LoanNum
>                           ORDER BY CASE WHEN MgrApprDate >= DirApprDate
>                                          AND MgrApprDate >= ExecApprDate
>                                         THEN MgrApprDate
>                                         WHEN DirApprDate >= ExecApprDate
>                                         THEN DirApprDate
>                                         ELSE ExecApprDate
>                                    END DESC) AS rk
> FROM T1) AS T
> WHERE rk = 1;
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
> .
> 
0
Utf
3/2/2010 11:51:06 PM
>> mgr_appr_date DATE NOT NULL,
   dir_appr_date DATE NOT NULL,
   exec_appr_date DATE,
 ..<<

We have DATE data types now, so it is a good idea to use them. Your
spec did not say if a NULL sorts first or last.  Your spec did not say
how to handle ties.
0
CELKO
3/3/2010 12:08:56 AM
Reply:

Similar Artilces:

Convert weekno to a date
I have a week no. (say week 2) in cell A4 and I want to convert that to the Fiday of that week (in cell B7) in the format Fri 14th Jan 2005. Could anybody tell me how to achieve this please. See if this is something you can work with: A4: 2 (The week number) A5: 2005 (The year..you didn't mention if the formula might apply to other years) B7: =DATE(A5,1,CHOOSE(WEEKDAY(DATE(A5,1,1),2),5,4,3,2,1,7,6)+(A4-1)*7) Does that help? •••••••••• Regards, Ron "Box666" wrote: > I have a week no. (say week 2) in cell A4 and I want to convert that > to the Fiday of that week ...

Any word on CRM 1.2 release date?
Has anyone heard a firm release date yet for CRM version 1.2? And will CRM 1.2 work with SBS2003 that was just released? -kw "Jim" <jim@nospam.com> wrote in message news:ehxGoPvnDHA.644@TK2MSFTNGP11.phx.gbl... > Has anyone heard a firm release date yet for CRM version 1.2? > > This depends on where you are based. I've just got home from an MS course, and we told that in Australia it will release to the partners at the conference late next month, and will be released to customers on 12/1/04. It was also confirmed that it will run on SBS2003 - it will ru...

Need help to read Pie Chart Series Range
Excel 2003, I have an existing Pie Chart and want to extract the Ranges (cells) used. My code below returns with a "Type mismatch" error. Sub GetPieChartSeries() Dim mySeries As Series ActiveSheet.ChartObjects(1).Activate Set mySeries = ActiveChart.SeriesCollection(1) Debug.Print mySeries.XValues (errors here) Debug.Print mySeries.Values End Sub Thanks, - Pat ...

date problem #10
Thanks a lot, Dave This piece of code works great. M P Redd -- mpredd ----------------------------------------------------------------------- mpreddy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1327 View this thread: http://www.excelforum.com/showthread.php?threadid=26358 ...

need Help for Migration Exchange 5.5
Hello NG, we`ve the following Configuration : - Exchange 5.5 with SP4 - single Winwos 2000 DC - configured both side ADC a new Hardware should be used for an Exchange 2003 and second DC. next steps i would do as following : - Update ADC Version with Exchange 2003 ADC - running Exchange 2003 forestprep, then domainprep, an after all running setup to install Exchange 2003 in the same organization, site - replicate system Folders and public Folders - moving all Mailboxes to the Exchange 2003 Store - configure the Exchange 2003 virtual SMTP Server to send outgoing Mail - reconfigure the F...

Word Doc Macro help needed please!
I need to make a mocro that will work in a word doc. This macro would start with [000001] and count up one number each paragraph. For example paragraph two would auto show [000002] and so on. Any help would be GREAT! I have a word file which contains 100 pages. All the pages have name & address of companies.(not in table) i have to transfer them to excel in column format(for example first name, last name, address, phone etc) can any one tell me code for this( i know how to open word & creat new excel sheet) . I need code for how to reach to lines of word. r there any thing like &quo...

How do I get a filter to "UPDATE" the rows selected?
Hey there! I am using Excel 2002 and I have a speadsheet that has prices in it. If I have the filter set to (the filter criteria is much more complex than this though) prices for items that cost >$5.00. Of I change an item to 4.59, it should not be seen. How can I get it to re-update the rows shown, with out releaseing the filter and resetting it? Is there something like refresh.filter or anything like that? Thanks Phil AFAIK you must release and reset but a macro with a worksheet_change event could trigger this for you so that it would happen automatically. -- Don Guillett SalesAid...

Do I need a licence to distribute MSFLXGRD.OCX?
I wish to use the MS FlexGrid control in my Access database. Do I need a licence to distribute MSFLXGRD.OCX? Pual Paul H wrote: > I wish to use the MS FlexGrid control in my Access database. > > Do I need a licence to distribute MSFLXGRD.OCX? If you also use a VB application, the answer is no. But you will need to build and deploy a VB application to distribute and register the OCX. You cannot distribute it with an Office application alone. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Arvin Meyer [MVP]"...

Printing changes layout
I am printing a booklet on Pub 2002 - when I print one page, it prints a page where everything has changed - resized, cut off the edges, etc. The worst part - when done printing, it goes back to normal view and it actually changes my layout to match what is printed - permanently. Any ideas what is going on? I printed on both my Lexmark and HP printers, does the same thing. I have Windows XP. Help! Thanks! Look at your page layout, has it changed? I know this will happen to me occasionally when I change printers. If I change back to the original printer the publication will revert...

Select last value
I am trying to select the last (bottom) value on a one-column list. I am using the COUNT function to designate the bottom value that is not zero, and the CHOOSE function to select the designated value. But, I can't make that work. Help appreciated. try =match(a number larger than possible,your range) -- Don Guillett SalesAid Software donaldb@281.com "Carl" <c@invalid.com> wrote in message news:eciLEKsvFHA.3236@TK2MSFTNGP14.phx.gbl... > I am trying to select the last (bottom) value on a one-column list. I am > using the COUNT function to designate the bottom va...

copy date in a cell if within a date range
Column M is a listing of percentages Column A is various dates, anywhere from Jan 1, 1998 to the present. I need to copy the contents of let's say M3 into cell T3 is the date in cell A3 is any date in the year 2010. If the date is in another year, leave cell T3 blank Thanks "carrerapaolo" wrote: > Column M is a listing of percentages > > Column A is various dates, anywhere from Jan 1, 1998 to the present. > > I need to copy the contents of let's say M3 into cell T3 is the date in cell > A3 is any date in the year 2010. If the ...

Help With Sorting #3
Gudday to all XL gurus. I need some help on using the SORT function. I have a list of 100 competitors in a scoring spreadsheet that I hav written. I was trying to sort by surname (a-z). The problem is that this list i generated elsewhere and contains VLOOKUP functions. Obviously if i hav less than 100 competitors I end up with some cells appear blank bu contain VLOOKUP functions. XL sorts them first and I want them sorte last after the surnames (a-z). I cant add a helper column as this lis relates to over 48,000 forumulaes and functions that are already added If I add a helper column I have...

Need help converting Microsoft Outlook calendar to Entourage.... At wits' end here! Please advise...
I have a Mac at work which runs Microsoft Outlook 2001 client on an Exchange server. I have a calendar on this machine (stored locally, not on the server) which has a ton of reminders programmed into it - very useful! Now, at home I got a new Mac laptop with Entourage X. Is there any way to move all of the reminders (appointments) from my calendar on Outlook to my Entourage calendar? I've tried two things: 1) I exported the calendar info as a CSV text file, and then tried importing it in Entourage. The problem is that Entourage import function seems to think it's importing an Address...

CtreeCtrl multiple selection
Hi, Is there any simple way (samples) to set a CTreeCtrl has multiple selection feature? Thanks, Chi Try these: http://www.techsoft.no/bendik/ http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c723 http://www.codeguru.com/Cpp/controls/treeview/misc-advanced/article.php/c629 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Chi" <anonymous@discussions.microsoft.com> wrote in message news:21bd01c4aa63$2e355d70$a501280a@phx.gbl... > Hi, > > Is there any simple way (samples) to set a CTreeCtrl has > multiple selection feature? Thanks, > &...

Bill Summary skips Apay due dates
I know I saw something about this some time ago but I can't find the info anymore. Microsoft Money 2007 Deluxe Version 16.0.120.1303 Bank - Wachovia Issue: I have 2 monthly Apays that I set up on Wachovia's website which were downloaded into Money's Bill Summary. I looked today and, though the next payment that hadn't posted to the register should be due 10/20/2008 the Due Date showing in Bill Summary was 3/20/2009. I've canceled the Apays and re-created them but I know this is a chronic issue. I already went through the full file repair process a while ago and ...

I need a list of command line switches for Microsoft WORD.
I am using a Shell command to execute Microsoft WORD 2003. I need to halt execution of VBA code until WORD finishes it's task. Is there a command line switch for WORD to cause it to pause the VBA function until it is finished? Is there a VBA command to pause execution of code until WORD finishes? Thanks. I am pretty sure the answer to your specific question is "No". If you tell us exactly what you are doing, including from where the Shell command is being executed, maybe we can suggest another way that will overcome the problem. -- Hope this helps. Please r...

help with lookup formula (sheet 2, not a double post)
I need to make a formula using a lookup. The first spreadsheet is a inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha tells number of gallons of alcohol per foot/inch/fraction. On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i cells R5 and T5. I want these numbers to lookup the number of gallon off of the P&G 103 worksheet. For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2" These two numbers should be added together and multiplied by the numbe ...

Help!! Way to find users sending email to large amount of receipients.
Could some one tell me if there is a way in Exchange 2003 to find out what users are sending email to a large amount of recipients? I'm having some serious performance issues and I'm sure it's some one sending email to a large amount of recipients. Thanks, Will ...

Help #15
I have a problem. I have a list of numbers that are in one column. Alot of numbers 8000 or so, and I need to find if there are an duplicates. I sort them so that they are in order, but having t scroll through the whole list takes alot of time and hurts my eyes!! I tried advanced filter but I can only find how to find duplicates in different columns. Someone help me please! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

sp_columns does not return NVARCHAR data type columns
Hi, our SQL Server Version is 2000 SP4. For several days the SP "sp_columns" does not return NVARCHAR data type columns any more. I reviewd the SP "sp_columns" and it seems, that the used SP "spt_datatype_info" does not return that type any more. A second production server returns that column type. The SP "sp_columns" is used by the ODBC-Driver to determine the table columns. Any suggestions? Thanks a lot, Nils Ok, my fault, it seems that "spt_datatype_info" is a table and there is no NVARCHAR row. After insert...

Formula to process 3 cells using IF statements
I have 3 columns of experimental data (C:E). Row 30 contains the sums (C30:E30). I need a formula that will examine the three sums and return the column number that has the lowest sum. If more than one column is lowest, select one randomly. Example: C30 D30 E30 Result 10 11 12 1 (C) 22 20 21 2 (D) 32 31 30 3 (E) 40 41 40 Randomly select 1 or 3 51 50 50 Randomly select 2 or 3 60 60 60 Randonly select 1, 2, or 3 Can this be done with IF statements or do I need to write a macro? Well, this is a bit cumbersome, but it se...

Help on filter
HI, I have a list to filter. I have to keep only integers. I tried everything in advanced filters... does not work. Can you help me please? Thanks Sylvai -- Message posted from http://www.ExcelForum.com Hi one way: use a helper column. Lets say your numbers are in column A then enter the following formula in an adjacent cell for row 2 =IF(MOD(A1,1)=0,"X","") copy this down for all rows. After this filter with this helper column -- Regards Frank Kabel Frankfurt, Germany > HI, > I have a list to filter. I have to keep only integers. > I tried everything in ...

help!
Hi I have been given a dataset with data,but the problem is now i need to create membership cards,which i plan to use the label wizard in reports,but i also have to display the intials of the customer but i have not been given the fields for intials just forename and last name.Is there maybe a way of running a query to search for some of the forename field to use as my intial???? thanks ! I thought I had seen this post a day or two ago and it was answered. If you want Initial and Last name Left(FirstName,1) & " " & LastName -- Dave Hargis, Microsoft Access MVP &qu...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...