Querying Excel data without another program

  • Thread starter Thread starter Don Hicks
  • Start date Start date
D

Don Hicks

Hello,

I'm an intermediate user of Excel, but I have lots of experience using
Access. If you can give me clues on how to handle the information below
using only Excel, I'd be grateful!

I have a spreadsheet in Excel 97 in which there's a Worksheet named,
"Datasource" with a column "B" containing repetitive data.

I'd like to create a new worksheet in the same Excel file which shows a
single instance of each item used in Column "B".

If I were writing the query in SQL, I'd say

"SELECT DISTINCT [Column B]
FROM [MySpreadsheet]![Datasource];"

Is there a way to do this in Excel? Using an SQL-type of query, perhaps?
Or, using a menu-driven routine within the program? Or, some other method?

Thanks for any help you can lend!

Sincerely,
Don Hicks
Portland, OR
 
You can use an Advanced Filter to extract the unique values. There are
instructions here:

http://www.contextures.com/xladvfilter01.html#FilterUR

Don said:
I'm an intermediate user of Excel, but I have lots of experience using
Access. If you can give me clues on how to handle the information below
using only Excel, I'd be grateful!

I have a spreadsheet in Excel 97 in which there's a Worksheet named,
"Datasource" with a column "B" containing repetitive data.

I'd like to create a new worksheet in the same Excel file which shows a
single instance of each item used in Column "B".

If I were writing the query in SQL, I'd say

"SELECT DISTINCT [Column B]
FROM [MySpreadsheet]![Datasource];"

Is there a way to do this in Excel? Using an SQL-type of query, perhaps?
Or, using a menu-driven routine within the program? Or, some other method?
Click to expand...
 
Yes, you can query am Excel workbook as a Jet data source (an MS
Access database is a Jet data source) using ADO. However, I disagree
with your SQL. It should look like this:

SELECT DISTINCT B
INTO [Excel 8.0;Database=C:\MyWorkbook.xls;].MyNewSheet
FROM [Excel 8.0;Database=C:\MyWorkbook.xls;].[Datasource$B:B]

You should be able to run this from any MS Access query designer. If
you connect to the workbook you can remove the [Excel 8 etc] info.
 
Back
Top