Approaching deadline!!!

[Obandy13]

Hey guys! I have a serious issue that is driving me insane. Here's the problem:

I have proprietary software that generates an excel spreadsheet every week with sales data. The columns of this spread sheet and some example data are listed below...

B18 C18 D18 E18 F18 G18 H18 I18 J18 K18 L18 M18 N18 O18 P18 Q18 R18 S18 T18 U18

STK #

Buyer Full

Caller Name

Car Description Short

Mini Deal

Doesn't count in 8

Date Purchased

Balance Paid Date

Date Sold

How Sold

Title Received Date

Price Purchased

Total Haul Cost

Parts Total Price

Price Sold

Gross Margin

Gross Margin-Haul

Gross Margin-Haul & Parts

Gross Margin-Haul & Parts(600)

Gross Margin-Haul & Parts(750)

<colgroup><col span="6" width="130"><col span="3" width="130"><col width="130"><col width="130"><col width="130"><col width="130"><col span="2" width="130"><col span="5" width="130"></colgroup><tbody>
</tbody>

G0657 Bryan Miller 2010 Nissan Frontier, XE X-CAB no no 4/6/2015 4/6/2015 4/8/2015 Direct Wholesale 4/9/2015 $ 2,000.00 $ - $ - $ 4,500.00 $ 2,500.00 $ 2,500.00 $ 2,500.00 $ 1,900.00 $ 1,750.00 G0675LR Bryan Miller 2004 Toyota Highlander, Limite no no 4/8/2015 4/8/2015 4/9/2015 Direct Wholesale 4/15/2015 $ 3,400.00 $ - $ 5,900.00 $ 2,500.00 $ 2,500.00 $ 2,500.00 $ 1,900.00 $ 1,750.00 G0677LR Bryan Miller 2011 Toyota RAV4, Limited 4WD no no 4/8/2015 4/8/2015 4/9/2015 Direct Wholesale 4/15/2015 $ 11,100.00 $ - $ 12,700.00 $ 1,600.00 $ 1,600.00 $ 1,600.00 $ 1,000.00 $ 850.00 G0700LRX Chad Dickerson 2004 Toyota Highlander, Limite no no 4/13/2015 4/13/2015 $ 2,500.00 $ - $ - $ - $ - $ - $ - G0408NLR Chad Dickerson 2008 Lexus LS 460, w/NAV/LEATH no no 3/2/2015 3/6/2015 4/8/2015 OVE 3/12/2015 $ 17,000.00 $ 581.90 $ - $ 18,200.00 $ 1,200.00 $ 618.10 $ 618.10 $ 18.10 $ (131.90) G0492NLRX Chad Dickerson 2007 Mercedes-Benz GL-Class, G no no 3/16/2015 3/25/2015 4/14/2015 Imperial Auction 4/6/2015 $ 10,000.00 $ 400.00 $ 18.00 $ 12,500.00 $ 2,500.00 $ 2,100.00 $ 2,082.00 $ 1,482.00 $ 1,332.00 G0445LR Chad Dickerson 2007 Toyota Camry, SE w/LEATHE no no 3/9/2015 3/9/2015 4/13/2015 Direct Wholesale 3/20/2015 $ 5,000.00 $ 475.00 $ - $ 6,000.00 $ 1,000.00 $ 525.00 $ 525.00 $ (75.00) $ (225.00) G0476 Chad Dickerson 2006 Honda Ridgeline, RT 4WD, no no 3/13/2015 3/25/2015 4/14/2015 Imperial Auction 3/30/2015 $ 4,000.00 $ 550.00 $ - $ 5,600.00 $ 1,600.00 $ 1,050.00 $ 1,050.00 $ 450.00 $ 300.00 G0500 Chad Dickerson 2010 Toyota Highlander, 4WD, W no no 3/17/2015 3/23/2015 4/8/2015 OVE 3/27/2015 $ 11,500.00 $ 75.00 $ - $ 13,000.00 $ 1,500.00 $ 1,425.00 $ 1,425.00 $ 825.00 $ 675.00 G0524LR Chad Dickerson 2005 Honda Odyssey, EX-L w/LEA no no 3/19/2015 3/23/2015 4/14/2015 Imperial Auction $ 3,000.00 $ 525.00 $ - $ 4,100.00 $ 1,100.00 $ 575.00 $ 575.00 $ (25.00) $ (175.00) G0525LR Chad Dickerson 2010 Chevrolet Traverse, LT w/ no no 3/19/2015 3/23/2015 4/14/2015 OVE 3/31/2015 $ 8,000.00 $ 500.00 $ 301.33 $ 9,400.00 $ 1,400.00 $ 900.00 $ 598.67 $ (1.33) $ (151.33) G0563 Chad Dickerson 2010 Toyota Camry, LE, Gray no no 3/24/2015 3/25/2015 4/14/2015 OVE 4/8/2015 $ 5,850.00 $ 450.00 $ - $ 7,400.00 $ 1,550.00 $ 1,100.00 $ 1,100.00 $ 500.00 $ 350.00 G0591L Chad Dickerson 2011 Toyota Tacoma, TRD Sport no no 3/27/2015 3/30/2015 4/14/2015 Imperial Auction 4/6/2015 $ 12,900.00 $ - $ 16,200.00 $ 3,300.00 $ 3,300.00 $ 3,300.00 $ 2,700.00 $ 2,550.00 G0602 Chad Dickerson 2010 Toyota Camry, LE, Silver no no 3/30/2015 4/3/2015 4/14/2015 Direct Wholesale 4/9/2015 $ 4,600.00 $ 300.00 $ - $ 6,400.00 $ 1,800.00 $ 1,500.00 $ 1,500.00 $ 900.00 $ 750.00 G0597NLR Chad Dickerson 2010 Lexus IS 250, SPORT AWD w no no 3/30/2015 4/1/2015 4/14/2015 Imperial Auction 4/8/2015 $ 9,750.00 $ 400.00 $ - $ 12,800.00 $ 3,050.00 $ 2,650.00 $ 2,650.00 $ 2,050.00 $ 1,900.00 G0608 Chad Dickerson 2011 Toyota Tundra, DOUBLE CAB no no 3/30/2015 3/30/2015 4/14/2015 Imperial Auction $ 11,000.00 $ 500.00 $ 173.67 $ 14,900.00 $ 3,900.00 $ 3,400.00 $ 3,226.33 $ 2,626.33 $ 2,476.33 <colgroup><col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="6" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="3" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="2" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="5" width="130"> </colgroup><tbody> </tbody>

<colgroup><col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="6" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="3" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754;width:98pt" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="2" width="130"> <col style="mso-width-source:userset;mso-width-alt:4754; width:98pt" span="5" width="130"> </colgroup><tbody>
</tbody>

In order to calculate commissions based on the data from the first spreadsheet, I have another workbook that has tabs for each buyer. That spreadsheet looks like this...

B133 C133 D133 E133 F133 G133 H133 I133 J133 K133 L133 M133

Bryan Miller

Stock #

Description

Cost

Haul

Sale Fee

Pac

Repairs

Sold $

Profit

Draft (1=yes/0=no)

Mini Deal

<colgroup><col width="239"><col width="87"><col width="211"><col width="111"><col width="111"><col span="2" width="111"><col width="117"><col width="113"><col width="102"><col width="171"><col width="102"></colgroup><tbody>
</tbody>

					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -
					$ 200.00	$ 550.00			$ -

<colgroup><col style="mso-width-source:userset;mso-width-alt:8740;width:179pt" width="239"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:7716;width:158pt" width="211"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:4059; width:83pt" span="2" width="111"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> <col style="mso-width-source:userset;mso-width-alt:4132;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:6253;width:128pt" width="171"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody>
</tbody>

Obviously these two spreadsheets are not formatted the same as far as columns go, and only certain data points need to be transferred over.

I want to a VBA function that will let me automatically populate the second spreadsheet with the click of a button (active x control or something).

I can't use VLOOKUP because the data changes every week (no unique values) and a single buyer can have up to 10 purchased vehicles. I can't use INDEX & MATCH because of the same reason... the spread sheet changes every week (the data, not the format).

So if "Bryan Miller" purchased 8 vehicles, I want all the vehicles that he purchased to transfer from the 1st spreadsheet to the 2nd.

Here's how the data needs to match up based on the columns and rows used as an example above (end result of the second spread sheet):

B133 C133 D133 E133 F133 G133 H133 I133 J133 K133 L133 M133
BLANK B18 E18 M18 N18 LEAVE LEAVE O18 P18 Q18 LEAVE F18

CAN ANYONE HELP ME?

 

[npvrader]

The challenge is to create a meaningful way to join these two datasets together. If no one field can be used as a unique key field can you create one by concantenating two existing fields? For instance taking the stock number and the salespersons name and creating a new field in both workbooks?

 

[SpillerBD]

Pivot Table. Have a Tab for Each Buyer, filter the Pivot Table for that buyer.
A pivot table can be set-up to show ALL the detail of your raw data, it will just organize it in a meaningful way and provide some additional functions. One function is the ability to add a calculated field which should allow you to do your commission calculations.

You could set up a single Pivot Table instead and just set the page breaking to occur for the appropriate data field/grouping.