Look up one value and return multiple corresponding values and copy data from multiple sheets to one sheet (excluding the blank rows

[Carguy37122]

I hope someone can assist me with this excel/VBA question.

The project I am working on compares a list of VIN#s (this worksheet is called Seller List) to historical sales data (this worksheet is called 2014 Sales Matrix) based on the 1st 8 digits of the VIN#s.
I've created 49 sheets that reference a single vin# from the Seller List worksheet. Each of these worksheets are named 2, 3, 4, 5, thru 50.

My goal is to be able to paste random vin#s in the "Seller List" and produce a summary of sales data from 2014 sorted by buyer that lists buyers who purchase similar vehicles. I would like to be able to email or fax the specific VIN# to the potential buyers. So sheet 2 could have 4 potential buyers, while sheet 3 could have 39 potential buyers, etc...

The 2014 Sales Matrix data is updated weekly, so the results will vary based on VIN# and quantity of sales.

I've added examples of the worksheets below. I'm sure there is an easier way to pull this data, but I'm not the best when it comes to excel. Thanks in advance!

sample of 2014 Sales Matrix

VIN# - First 8 Digits <tbody> </tbody><colgroup><col></colgroup>

Buyer ID <tbody> </tbody><colgroup><col></colgroup>

Buyer Name <tbody> </tbody><colgroup><col></colgroup>

Buyer Representative <tbody> </tbody><colgroup><col></colgroup>

Buyer Phone <tbody> </tbody><colgroup><col></colgroup>

Buyer Fax# <tbody> </tbody><colgroup><col></colgroup>

Buyer Email <tbody> </tbody><colgroup><col></colgroup>

Internet Buyer? <tbody> </tbody><colgroup><col></colgroup>

Buyer Street <tbody> </tbody><colgroup><col></colgroup>

Buyer City <tbody> </tbody><colgroup><col></colgroup>

Buyer State <tbody> </tbody><colgroup><col></colgroup>

Buyer Zip <tbody> </tbody><colgroup><col></colgroup>

Sold Date <tbody> </tbody><colgroup><col></colgroup>

Location <tbody> </tbody><colgroup><col></colgroup>

Sale Price <tbody> </tbody><colgroup><col></colgroup>

VIN <tbody> </tbody><colgroup><col></colgroup>

Mileage <tbody> </tbody><colgroup><col></colgroup>

Asking Price

Status <tbody> </tbody><colgroup><col></colgroup>

Description <tbody> </tbody><colgroup><col></colgroup>

Location ID <tbody> </tbody><colgroup><col></colgroup>

W/O

Venue <tbody> </tbody><colgroup><col></colgroup>

Venue Code <tbody> </tbody><colgroup><col></colgroup>

Days Offered

Highest Offering <tbody> </tbody><colgroup><col></colgroup>

Title Status <tbody> </tbody><colgroup><col></colgroup>

Blue <tbody> </tbody><colgroup><col></colgroup>

Green

Red

Yellow

Seller Name <tbody> </tbody><colgroup><col></colgroup>

Seller Address1 <tbody> </tbody><colgroup><col></colgroup>

Seller City <tbody> </tbody><colgroup><col></colgroup>

Seller State <tbody> </tbody><colgroup><col></colgroup>

Seller Zip <tbody> </tbody><colgroup><col></colgroup>

Days Run <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>

Vehicle Grade <tbody> </tbody><colgroup><col></colgroup>

1G6DC5EY <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>

1234567

Trucks are fun

Steve Jobs

999-999-1234

999-999-2234

bigdaddy@trucksjob.com

yes

1234 Granny Smith Lane

San Diego

CA

32124

04/02/2014

Internet Sale

19000

1G6DC5EYhwyeu182hs8abe988s8 <tbody> </tbody><colgroup><col></colgroup>

54000

21000

Sold

SALME114hwyeu182hs8abe988s8 2004 LAND ROVER RANGE ROVER - <tbody> </tbody><colgroup><col></colgroup>

SDEW

64372

Open Sale

2

3

19000

Title sent to buyer

no

yes

no

no

abc motors

3322 main street

San Diego

CA

11111

4

2.8

1G1PE5SB

6573332

cars for peeps

Riley Johnson

345-543-2345

345-543-3321

carjohnson@carguyjohnson.com

no

3453 Long Street

Tampa

FL

33311

09/022/2014

Lot Sale

7000

1G1PE5SBjf3839synhdoa837ues <tbody> </tbody><colgroup><col></colgroup>

102000

8000

Sold/Funds Not Sent

3N1BC13Ejf3839synhdoa837ues 2009 NISSAN VERSA - <tbody> </tbody><colgroup><col></colgroup>

TAMF

188283

Open Sale

2

7

7000

Title pending funding

no

yes

no

no

xyz motors

9283 Circle S Drive

Tampa

FL

23212

2

3.8

<tbody>
</tbody>

Sample of Seller List VIN# <tbody> </tbody><colgroup><col></colgroup> VIN# - 8 Digits <tbody> </tbody><colgroup><col></colgroup> Year <tbody> </tbody><colgroup><col></colgroup> Make <tbody> </tbody><colgroup><col></colgroup> Model <tbody> </tbody><colgroup><col></colgroup> Trim <tbody> </tbody><colgroup><col></colgroup> Miles <tbody> </tbody><colgroup><col></colgroup> # of Matches (formula listed) 1G6DC5EY3r2537541 <tbody> </tbody><colgroup><col></colgroup> 1G6DC5EY 2011 Cadillac CTS 33,654 =COUNTIFS('2014 Sales Matrix'!$A:$A,$B2) 1G1PE5SB3f6374832 <tbody> </tbody><colgroup><col></colgroup> 1G1PE5SB 2014 Chevrolet Cruze 25,304 =COUNTIFS('2014 Sales Matrix'!$A:$A,$B3) <tbody> </tbody>

Sample of worksheet named 2 (worksheets 2-50 are formatted the same way) 1 potential buyer(s) for 1G6DC5EY5B0149272 2011 CADILLAC CTS 35958 Miles <tbody> </tbody><colgroup><col></colgroup> VIN# <tbody> </tbody><colgroup><col></colgroup> Row# Buyer <tbody> </tbody><colgroup><col></colgroup> Rep Name <tbody> </tbody><colgroup><col></colgroup> Tel# <tbody> </tbody><colgroup><col></colgroup> Address <tbody> </tbody><colgroup><col></colgroup> City <tbody> </tbody><colgroup><col></colgroup> State <tbody> </tbody><colgroup><col></colgroup> Zip <tbody> </tbody><colgroup><col></colgroup> Purchase Date <tbody> </tbody><colgroup><col></colgroup> Sale Price <tbody> </tbody><colgroup><col></colgroup> Miles <tbody> </tbody><colgroup><col></colgroup> Grade <tbody> </tbody><colgroup><col></colgroup> 1G6DC5EY <tbody> </tbody><colgroup><col></colgroup> 1624 <tbody> </tbody><colgroup><col></colgroup> sell cars motors steve smith 394-239-3301 329 2nd street Nashville TN 37112 02/14/2014 15,000 83000 3.9 ('SELLER LIST'!$B$2) =IF(ISERROR(SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))),"",SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,3)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,3)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,4)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,4)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,5)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,5)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,9)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,9)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,10)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,10)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,11)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,11)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,12)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,12)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,13)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,13)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,15)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,15)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,17)),"",INDEX('2014 Sales Matrix'!$A$2:$Q$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,17)) =IF(ISERROR(INDEX('2014 Sales Matrix'!$A$2:$AL$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,38)),"",INDEX('2014 Sales Matrix'!$A$2:$AL$29777,SMALL(IF('2014 Sales Matrix'!$A$2:$A$29777='2'!$A$3,ROW('2014 Sales Matrix'!$A$2:$A$29777)),ROW(1:1))-1,38)) <tbody> </tbody>

<tbody>
</tbody><colgroup><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col><col></colgroup>