mrmmickle1
Well-known Member
- Joined
- May 11, 2012
- Messages
- 2,461
I have two Spreadsheets with different Data on them. They have a Customer Code that I would like to use as a Header on SubSets of data. The data looks like this for Sheet1
The Data Looks like this for OpenWorkOrders:
I would like to Use the full length name of the Company based on the Company Code as a header above the other data. The data set is about 3,000 rows. An example of what I would like is this:
The purpose of putting the data like this is so that I can group it by customer and collapse and uncollapse the data. Is there any easy way to count up from the bottom and then insert the value of column 2 in Sheet 1 based on a Vlookup? Additionally is there a simple way to run a macro to group the data in collapsable tabs based on a change in customer for example maybe look for a value in the b column. If the cell is blank then group all rows below it until another blank cell is found and then do the same thing?
I know this is a very detailed question. Any partial help or complete help would be much appreciated.
-Matt
| Excel 2010 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Code | Company Name | ||
| 2 | JLGPH | AIR CANADA JAZZ-LG PBH | ||
| 3 | RBLDL | RBLDL | ||
| 4 | HUNAI | AIR CONTRACTORS-IRELAND | ||
| 5 | MOUAC | MOUNTAIN AIR CARGO | ||
| 6 | AIRWA | AIR WISCONSIN AIRLINES | ||
| 7 | PINN | PINNACLE AIRLINES | ||
OpenWorkOrders09 06 12 | ||||
The Data Looks like this for OpenWorkOrders:
| Excel 2010 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Code | Entry Date | WO Number | Status | Company Name | Site | Part Number | Description | NSN # | Serial Number | Department | WO Description | NH WO # | Total Price | ||
| 2 | MOUAC | 3/8/2012 | L022903 | Closed | MOUNTAIN AIR CARGO | D61089 | _ | LG Non-PBH | MPG | L022072 | 0 | |||||
| 3 | MOUAC | ######## | LN30243-47 | Sub-Assembly | MOUNTAIN AIR CARGO | A76527-7 | A76527-7 | SFT24 | LG Non-PBH | LN30243 | 0 | |||||
| 4 | MOUAC | 3/8/2012 | L033568 | Closed | MOUNTAIN AIR CARGO | A76527-7 | A76527-7 | LG Non-PBH | L031925 | 0 | ||||||
| 5 | JLGPH | 3/8/2012 | L035465 | Closed | AIR CANADA JAZZ-LG PBH | #NAME? | #NAME? | LG PBH | L033288 | 0 | ||||||
| 6 | JLGPH | ######## | LC40023-8 | Closed | AIR CANADA JAZZ-LG PBH | 16188-1 | 16188-1 | LG PBH | LC40023 | 0 | ||||||
| 7 | JLGPH | ######## | LC40023-9 | Closed | AIR CANADA JAZZ-LG PBH | 16189-1 | 16189-1 | LG PBH | LC40023 | 0 | ||||||
| 8 | JLGPH | 3/8/2012 | L033612 | Closed | AIR CANADA JAZZ-LG PBH | 16189-1 | 16189-1 | LG Non-PBH | L025415 | 0 | ||||||
| 9 | HUNAI | 6/4/2012 | LN30359-1 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D60980-1 | AXLE | 1 | 16U9 | LG Non-PBH | LN30359 | 0 | ||||
| 10 | HUNAI | 6/7/2012 | LN30359-27 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D61002 | BUSHING | 1 | LG Non-PBH | LN30359 | 0 | |||||
| 11 | HUNAI | 6/1/2012 | LN30362-4 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D57340 | BUSHING-THREADED | 5 | LG Non-PBH | LN30362 | 33557.92 | |||||
Sheet1 | ||||||||||||||||
I would like to Use the full length name of the Company based on the Company Code as a header above the other data. The data set is about 3,000 rows. An example of what I would like is this:
| Excel 2010 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Code | Entry Date | WO Number | Status | Company Name | Site | Part Number | Description | NSN # | Serial Number | Department | WO Description | NH WO # | Total Price | ||
| 2 | MOUNTAIN AIR CARGO | |||||||||||||||
| 3 | MOUAC | 3/8/2012 | L022903 | Closed | MOUNTAIN AIR CARGO | D61089 | _ | LG Non-PBH | MPG | L022072 | 0 | |||||
| 4 | MOUAC | ######## | LN30243-47 | Sub-Assembly | MOUNTAIN AIR CARGO | A76527-7 | A76527-7 | SFT24 | LG Non-PBH | LN30243 | 0 | |||||
| 5 | MOUAC | 3/8/2012 | L033568 | Closed | MOUNTAIN AIR CARGO | A76527-7 | A76527-7 | LG Non-PBH | L031925 | 0 | ||||||
| 6 | AIR CANADA JAZZ-LG PBH | |||||||||||||||
| 7 | JLGPH | 3/8/2012 | L035465 | Closed | AIR CANADA JAZZ-LG PBH | #NAME? | #NAME? | LG PBH | L033288 | 0 | ||||||
| 8 | JLGPH | ######## | LC40023-8 | Closed | AIR CANADA JAZZ-LG PBH | 16188-1 | 16188-1 | LG PBH | LC40023 | 0 | ||||||
| 9 | JLGPH | ######## | LC40023-9 | Closed | AIR CANADA JAZZ-LG PBH | 16189-1 | 16189-1 | LG PBH | LC40023 | 0 | ||||||
| 10 | JLGPH | 3/8/2012 | L033612 | Closed | AIR CANADA JAZZ-LG PBH | 16189-1 | 16189-1 | LG Non-PBH | L025415 | 0 | ||||||
| 11 | AIR CONTRACTORS-IRELAND | |||||||||||||||
| 12 | HUNAI | 6/4/2012 | LN30359-1 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D60980-1 | AXLE | 1 | 16U9 | LG Non-PBH | LN30359 | 0 | ||||
| 13 | HUNAI | 6/7/2012 | LN30359-27 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D61002 | BUSHING | 1 | LG Non-PBH | LN30359 | 0 | |||||
| 14 | HUNAI | 6/1/2012 | LN30362-4 | Sub-Assembly | AIR CONTRACTORS-IRELAND | D57340 | BUSHING-THREADED | 5 | LG Non-PBH | LN30362 | 33557.92 | |||||
Sheet1 | ||||||||||||||||
The purpose of putting the data like this is so that I can group it by customer and collapse and uncollapse the data. Is there any easy way to count up from the bottom and then insert the value of column 2 in Sheet 1 based on a Vlookup? Additionally is there a simple way to run a macro to group the data in collapsable tabs based on a change in customer for example maybe look for a value in the b column. If the cell is blank then group all rows below it until another blank cell is found and then do the same thing?
I know this is a very detailed question. Any partial help or complete help would be much appreciated.
-Matt
