none
LinqToExcel: Distinct values in excel column RRS feed

  • Question

  • This might be a very simple thing for you gurus, but I'm not familiar with C#4 and INTEROP. Therefore, I'm stumped. Here's my problem. I have an excel column that has duplicate data and I want to trim it down to only unique values. Here's what the data looks like:

    ColA    ColB
    10      Adam
    12      Jane
    14      Adam
    18      Adam
    20      Eve

    So, in the end I just want unique names from ColB:

    Adam
    Jane
    Eve

    I know that I can do this by getting all those values into a List and then adding the Distinct functionality to it. But I think I'm doing something wrong. Anyway, here's my program:

    Application XLApp = new Microsoft.Office.Interop.Excel.Application();
    var XLBook = XLApp.Workbooks.Open(@"c:\temp\Test.xls", ReadOnly: false);
    // Grab the 1st sheet
    var XLSheet = (Microsoft.Office.Interop.Excel.Worksheet)XLBook.Worksheets.get_Item(1);
    
    XLApp.Visible = true;
    // I think I need help with the following lines
    IEnumerable<string> myCol = XLApp.Range["B2", XLApp.Range["B2"].End[XlDirection.xlDown]].Select();
    myCol.ToList().Distinct();
    
    XLBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
    ((_Application)XLApp).Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(XLApp);

    How do I go about doing this using C#4 against Excel 2003?

    Thanks in advance.

    Note: I've already tried using LinqToExcel but I haven't had much luck.  If you're curious, this is the query that I tried:

    var excel = new ExcelQueryFactory("worksheetFileName");
    var distinctNames = (from row in excel.WorkSheet()
                         select row["ColB"]).Distinct()

    Thursday, May 3, 2012 12:03 AM

Answers

  • HI inquisitive_one,

    Please try this snippet:

                Excel.Worksheet ws = Application.ActiveSheet;
                Excel.Range data = ws.Range[ws.Cells[1,1], ws.Cells[6,2]];
                data.Select();
                data.RemoveDuplicates(2, Excel.XlYesNoGuess.xlNo);

    Have a good day,
    Tom

    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 7, 2012 6:44 AM
    Moderator

All replies

  • Hi inquisitive_one,

    Thanks for posting in the MSDN Forum.

    It seems that your issue is regard about LinqToExcel. I would recommend you post it in http://groups.google.com/group/linqtoexcel for better support.

    And do you accept the work round which developed via Excel PIA?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 3, 2012 6:38 AM
    Moderator
  • Thanks @Tom_Xu.

    Yes, I'll accept Excel PIA workaround.  And thank you for the suggestion of following up regarding LinqToExcel.

    Thursday, May 3, 2012 2:21 PM
  • HI inquisitive_one,

    Please try this snippet:

                Excel.Worksheet ws = Application.ActiveSheet;
                Excel.Range data = ws.Range[ws.Cells[1,1], ws.Cells[6,2]];
                data.Select();
                data.RemoveDuplicates(2, Excel.XlYesNoGuess.xlNo);

    Have a good day,
    Tom

    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 7, 2012 6:44 AM
    Moderator
  • Thanks @Tom_Xu.  That helped a lot.
    Wednesday, May 9, 2012 8:20 PM