0

I want to know the number of rows in a excel but if the value is repeated in a "A" column it should not count .

A   B   C
aa  1   1
bb  2   2
aa  3   3
cc  4   4
bb  5   5
Total=3 Total=5 Total=5
Frits
  • 7,341
  • 10
  • 42
  • 60
Priya
  • 13
  • 1
  • 7
  • 1
    Did you intend to say VB.NET or VBA? VBA is used inside Excel. VB.NET is written inside Visual Studio. It makes a very big difference to the answer. – Andrew Morton Jul 20 '17 at 10:11
  • Vb.net written in visual studio (win forms) using this dll Microsoft.Office.Interop.Excel – Priya Jul 20 '17 at 10:31

1 Answers1

1

You need some way of counting distinct entities. A HashSet will work well for that:

The HashSet class provides high-performance set operations. A set is a collection that contains no duplicate elements, and whose elements are in no particular order.

All that needs to be done is look at each of the values in each of the columns and add it to a hashset if it isn't in there already, then count the number of entries in the hashset:

Option Infer On
Option Strict On

Imports Microsoft.Office.Interop
Imports System.Text

Public Class Form1

    Sub ShowDistinctColumnValueCounts(xlFile As String)
        Dim xl = New Excel.Application
        Dim wb As Excel.Workbook = xl.Workbooks.Open(xlFile)
        Dim ws As Excel.Worksheet = DirectCast(wb.Worksheets(1), Excel.Worksheet)

        Dim nCols = ws.UsedRange.Columns.Count
        Dim nRows = ws.UsedRange.Rows.Count

        Dim vals = DirectCast(ws.Range(ws.Cells(1, 1), ws.Cells(nRows, nCols)).Value, Object(,))

        wb.Close()
        xl.Quit()

        Dim sb As New StringBuilder

        For col = 1 To nCols
            Dim hs As New HashSet(Of Object)
            For row = 1 To nRows
                If Not hs.Contains(vals(row, col)) Then
                    hs.Add(vals(row, col))
                End If
            Next
            sb.AppendLine($"Col {col} has {hs.Count} distinct entries")
        Next

        TextBox1.Text = sb.ToString()

    End Sub

    Sub DoStuff()
        Dim src = "C:\temp\test.xlsx"
        ShowDistinctColumnValueCounts(src)

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DoStuff()

    End Sub


End Class

With your sample data (I assumed that "A", "B", and "C" are the column names, not entries), that outputs:

Col 1 has 3 distinct entries
Col 2 has 5 distinct entries
Col 3 has 5 distinct entries

Additional Ref: The proper way to dispose Excel com object using VB.NET?

Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
  • correct that's column heading names. sb.AppendLine($"Col {col} has {hs.Count} distinct entries") this line i am not understanding and its throwing errror. – Priya Jul 21 '17 at 08:51
  • @Priya I guess you are using an older version of Visual Studio - you could use `sb.AppendLine(String.Format("Col {0} has {1} distinct entries", col, hs.Count))` instead. – Andrew Morton Jul 21 '17 at 08:57
  • @Priya I'm sure you can find out where the extra one is coming from - perhaps you need to check for empty cells with something like `If Not String.IsNullOrEmpty(CStr(vals(row, col))) Then...`. – Andrew Morton Jul 21 '17 at 09:15
  • I tried like that but it was not working its not htrowing error also? – Priya Jul 21 '17 at 11:04
  • @Priya You should look at all the values in `hs`. That will help you figure out why it seems to have one entry too many. – Andrew Morton Jul 21 '17 at 12:14
  • @ Andrew i changed the code its started to working.Thank you so much – Priya Jul 21 '17 at 12:15
  • how to get the value from the hashset(What we are adding).Using arraylist? – Priya Jul 21 '17 at 12:40
  • @Priya `For each x in hs : Console.Writeline(x) : Next` should do it - you just need to make sure you can see the console output. – Andrew Morton Jul 21 '17 at 12:48
  • @Priya You're welcome :) You can mark this as the accepted answer if you like: [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers) – Andrew Morton Jul 24 '17 at 10:21