Querying Excel data without another program

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
 
D

Debra Dalgleish

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...
 
O

onedaywhen

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.
 
Top