returning multiple records from an excel range

[CraigMan]

Seemingly simple in concept, I have yet to find a clean solution.

I'm working from two data sources. One contains data on ACTIVE MLS listings. The other data on SOLDs. Both data sets contain essentially the same column structure. For each ACTIVE row I want to group all SOLDs from the same subdivision. The kicker is I need to screen the SOLDs against criteria based on data in the ACTIVE row first.

I've tried pivoting, sorting, filtering, and index(match) to no avail.

Any other ideas?

Craig Meyers, BSNucE, PE

http://www.lava-java.com

 

[BenRowe]

Perhaps you could treat your data sources as databases and then build a SQL query.

 

[CraigMan]

Are you suggesting using the SQL.REQUEST excel function? Can I use these functions with data that resides in an excel sheet? Do you know where I can get examples?

Craig Meyers, BSNucE, PE

 

[BenRowe]

For each of your Databases, your need to create a name.
Select the cells involved (make sure first row has field name labels), Click Insert, Name, Define, type in name, which will be your table name in SQL.

Now, click Data, Import External Data, New Database Query, click New Data Source, using MS excel as your driver and selecting your workbook as source. The SQL wizard should come up now.

This should be enough to get you started.

 

[CraigMan]

The query works great.

In the example I have below, the subdivision for the active listing is Buckleigh. So I queried the SOLD table for subdivision Buckleigh. Can I have the query refer to the cell containing the name of the active subdivision? I have 1000s of active listings and over 15000 solds. So I'm going to apply some criteria in the query to limit the solds to 6 records. Then my worksheet will look like this:

ACTIVE LISTING 1
space
header
SOLD
SOLD
SOLD
SOLD
SOLD
SOLD
ACTIVE LISTING 2
space
header
SOLD
SOLD
SOLD
SOLD
SOLD
SOLD
etc.

There may be anywhere from 0 to 6 SOLDs for each ACTIVE LISTING.

Any ideas?

[tt][small]
MLS # Street # Street Name City Zip Subdivision List Price Listing Date Total Heated SqFt
566675 2531 Buckleigh Charlotte 28215 Buckleigh 137610 01/14/06 1509

Status MLS # Street # Street Name City Zip Subdivision Sales Price Seller Contribution Sold Date Total Heated SqFt
Sold 511947 4612 Meadowfield Charlotte 28215 Buckleigh 130000 3000 01/03/06 1509
Sold 511939 4624 Meadowfield Charlotte 28215 Buckleigh 142575 3000 01/05/06 1721
Sold 503406 4818 Roxfield Charlotte 28215 Buckleigh 129500 3000 08/03/05 1247
Sold 503396 4803 Roxfield Charlotte 28215 Buckleigh 142275 3000 08/12/05 1721
Sold 503400 4814 Roxfield Charlotte 28215 Buckleigh 144050 3000 08/22/05 1721
Sold 512136 2114 Buckleigh Charlotte 28215 Buckleigh 138525 3000 08/29/05 1721
Sold 512013 4720 John Russell Charlotte 28215 Buckleigh 149615 3000 09/15/05 1826
Sold 503409 3403 Charterhall Charlotte 28215 Buckleigh 139000 3000 11/15/05 1721
Sold 558136 4640 Hadstone Charlotte 28215 Buckleigh 135000 3000 12/16/05 1721
[/small][/tt]

Craig Meyers, BSNucE, PE

 

[CraigMan]

BenRowe,

You still there? I have another question if you have a few minutes. Se my last post. I think the solution may be the SQL.REQUEST function.

Craig Meyers, BSNucE, PE