How to use VB.net with an Excel sheet to count and sort data -

just title says, i've been using vb.net various things on excel , kinda new. if have excel sheet various data including staff , things sold, how can select 2 columns , sort them counts how many of each items sold each person messagebox or listbox?

for eg, output i'm looking like

staff   sold       how many name1 - product1 - amountsoldbyname1 name1 - product2 - amountsoldbyname1 name1 - product3 - amountsoldbyname1 name1 - product4 - amountsoldbyname1 name2 - product1 - amountsoldbyname2 name2 - product2 - amountsoldbyname2  , on... 

the furthest i've gotten counting how many of each staff member there in 1 column go step further on , 2 columns , count each product sold each person unclear how go it.

private sub getexcelfile_click(byval sender system.object, byval e system.eventargs) handles button3.click     dim excelfile new openfiledialog()     excelfile.showdialog()     if (excelfile.showdialog() = dialogresult.cancel)         return     else         dim file string = excelfile.filename         dim xlapp excel.application         dim xlworkbook excel.workbook         dim xlworksheet excel.worksheet          xlapp = new excel.application         xlworkbook = xlapp.workbooks.open(file)         xlworksheet = xlworkbook.worksheets("pssalesfullconnectionreport")          dim col string = "n"         row integer = 1 xlworksheet.usedrange.rows.count - 1             dim elemvalue string = xlworksheet.range(col & row).text             listbox1.items.add(elemvalue)         next          messagebox.show(returnduplicatelistboxitems(listbox1))          listbox1.items.clear()      end if  end sub  public shared function returnduplicatelistboxitems(byval lbox system.windows.forms.listbox) string     dim strreturn new system.text.stringbuilder     dim litems new dictionary(of string, integer)     dim intcount integer = 0     dim strcurrentitem string = string.empty      try         each nitem string in lbox.items             if not (litems.containskey(nitem))                 strcurrentitem = nitem                 each sitem string in lbox.items                     if sitem.equals(strcurrentitem)                         intcount += 1                     end if                 next                 litems.add(nitem, intcount)                 intcount = 0                 strcurrentitem = string.empty             end if         next          integer = 0 litems.count - 1             strreturn.appendline(litems.keys(i).tostring & " - " & litems.values(i).tostring)         next      catch ex exception         return strreturn.tostring     end try      return strreturn.tostring end function 

any guidance on me alot

slight delay on answer, here relevant code creating pivot table interop (sorry, it's c#) , using pivot table summarize results. nice because once pivot table done, know have unique entries sorted. can iterate pivot table results want. cases, may easier building out logic on .net side of things since excel @ aggregating data. downside have create pivot table.

this full code create dummy data , summarize general possible.

//up above namespace usage defines: //using excel = microsoft.office.interop.excel; private void button1_click(object sender, eventargs e) {     excel.application xl_app = new excel.application();      excel.workbook wkbk = xl_app.workbooks.add();     excel.worksheet sht = wkbk.worksheets[1];      xl_app.visible = true;      //create dummy data... not using array.. know     random rnd = new random();      int rows = 100;     (int = 0; < rows; i++)     {         sht.cells[2 + i, 1].value = "name" + rnd.next(5);         sht.cells[2 + i, 2].value = "prod" + rnd.next(5);         sht.cells[2 + i, 3].value =  rnd.next(10);     }      sht.cells[1, 1].value = "staff";     sht.cells[1, 2].value = "sold";     sht.cells[1, 3].value = "how many";      //create pivot table     excel.pivotcache pc = wkbk.pivotcaches().add(         excel.xlpivottablesourcetype.xldatabase,          sht.get_range("a1").currentregion);      excel.pivottable pt = sht.pivottables().add(pc, sht.get_range("f1"));      pt.pivotfields("staff").orientation = excel.xlpivotfieldorientation.xlrowfield;     pt.pivotfields("sold").orientation = excel.xlpivotfieldorientation.xlrowfield;      pt.adddatafield(pt.pivotfields("how many"), type.missing, excel.xlconsolidationfunction.xlsum);     //formatting here ensures table no values      foreach (excel.pivotfield pf in pt.pivotfields())     {         pf.subtotals[1] = false;     }      pt.columngrand = false;     pt.rowgrand = false;      pt.rowaxislayout(excel.xllayoutrowtype.xltabularrow);     pt.repeatalllabels(excel.xlpivotfieldrepeatlabels.xlrepeatlabels);      //now need go through pivot table data      excel.range rng_start = sht.get_range("f3");      foreach (excel.range rng_cell in          sht.get_range(             rng_start,              rng_start.get_end(excel.xldirection.xldown)))     {         console.writeline("{0} - {1} - {2}",             rng_cell.value,             rng_cell.get_offset(0, 1).value,             rng_cell.get_offset(0, 2).value);     }  } 

picture of resulting workbook has pivot table on over side:

console output includes rows have results. shown last couple lines:

... name4 - prod0 - 7 name4 - prod1 - 11 name4 - prod2 - 10 name4 - prod3 - 17 name4 - prod4 - 23