Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Select and count distinct values in Excel (Read 9239 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Select and count distinct values in Excel
31.01.08 at 07:45:21
Select and count distinct values in Excel
SQL*XL can help to do searches in your Excel workbooks through SQL that are difficult to do with ordinary formulas. As an example of this I gave the following advice to a colleage who needed to display a list of distinct values in a long list of about 8000 rows and count how many rows were listed for each distinct value. In SQL this is easy to do however using Excel formulas this is quite a challenge. This is how you do it in SQL:
select mycol, count(*) from mytable group by mycol
The procedure to run this query using SQL*XL is this:

  • First close the spreadsheet
  • Open a new spreadsheet to receive the results in
  • Open the SQL*XL connection dialog
  • Click the new connections button
  • Use the new file databases function
  • Select the directory where you store your spreadsheet
  • Let SQL*XL search for your xls file and select it
  • Press the test the database button
  • SQL*XL will guide  you through the steps to create a new connection
  • After making the connection, close the wizard and return to the connection screen
  • Connect to the "database" - to the Excel workbook
  • Go to the SQL*XL SQL editor to find out what the name is of the worksheet you want to use. Use this as your table name. I will use sheet1 here as an example.
  • You can also find out in the SQL editor - in the database objects list - what the column names are. I assume for the example that your column is called mycol.
  • Now the easy part: just enter the following SQL and run it:
    select mycol, count(mycol) from sheet1 group by mycol
    Rather easy isn't it?
    Now you have a connection to the spreadsheet you can use any SQL to query the data. Enjoy!
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1