Compare list to a table, and output multiple reference numbers

C

Cipher226

New Member
Joined
Sep 9, 2011
Messages
16
I'm trying to check a list of numbers to a table and output a reference number of sorts. Now, I've got the formula to do this with some help. Now, the issue is, the table has over 600 entries. There are multiple reference numbers that have the same combination of numbers. I would like to be able to display all the different reference numbers. Below is what I've got.

The formula I'm using to get the reference numbers is

=INDEX(Sheet3!$A$4:$A$626,MATCH(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2,Sheet3!C4:C626&Sheet3!D4:D626&Sheet3!E4:E626&Sheet3!F4:F626&Sheet3!G4:G626&Sheet3!H4:H626&Sheet3!I4:I626&Sheet3!J4:J626&Sheet3!K4:K626&Sheet3!L4:L626,0))

Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Group</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">01</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">37</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">40</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Assembly Number</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">AT 540</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​



Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Main Housing</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pump</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Speed & Governor Drive Gears</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Clutches / Gear Unit</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Control Valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Retarder Control valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Governor</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Torque Converter</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pan</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Vacum Modulator</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Assembly Number</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Model</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">14</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">21</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">37</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">40</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">AT 540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">542</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">543</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">508</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD></TR></TBODY></TABLE>
Sheet3​




Any help would be greatly appreciated.
 
Sort by date Sort by votes
Cipher226 said:
I've got to be doing something wrong here... T.Valko, your formula shows up as #n/a, and Markmzz, your formula shows up as #name?. I typed both arrays, and copied both arrays. Truth be told, I don't understand either formula so I can't even begin to poke around and see what I did wrong.

Below is a link to my actual excel sheet without either formula entered as they do not want to work for me. It's not the greatest file sharing site, gotta click the left button for free download and wait about a minute before you can download the file. But any assistance would be much appreciated.

AT545 Assembly Number List.xls - 123.5 KB
Click to expand...

Sorry,

Translation error. Here is the correctly formula:

=IF(SUM(--(MMULT(--(B$2:K$2=Sheet3!C$3:L$626),ROW($1:$10)^0)=10))>=ROWS(B$6:B6),
INDEX(Sheet3!A$3:A$626,SMALL(IF(MMULT(--(B$2:K$2=Sheet3!C$3:L$626),ROW($1:$10)^0)=10,ROW(Sheet3!A$3:A$626)-ROW(Sheet3!A$3)+1),ROWS(B$6:B6))),"")

Markmzz
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Your data with the formulas:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Group</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16-1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16-2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">21</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">37</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #000000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">503</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">509</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">504</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">544</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">540</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">507</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">514</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">501</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; FONT-WEIGHT: bold">Assembly Number</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; FONT-WEIGHT: bold">Model</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6835200</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836424</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD></TR></TBODY></TABLE>Sheet1


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B6</TH><TD style="TEXT-ALIGN: left">=IF(A6="","",VLOOKUP(A6,Sheet3!A$3:B$626,2,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A6</TH><TD style="TEXT-ALIGN: left">{=IF(SUM(--(MMULT(--(B$2:K$2*1=Sheet3!C$3:L$626),ROW($1:$10)^0)=10))>=ROWS(A$6:A6),
INDEX(Sheet3!A$3:A$626,SMALL(IF(MMULT(--(B$2:K$2*1=Sheet3!C$3:L$626),ROW($1:$10)^0)=10,ROW(Sheet3!A$3:A$626)-ROW(Sheet3!A$3)+1),ROWS(A$6:A6))),"")}

</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

Markmzz
 
Upvote 0
Ummm... not that I'm not completely gratefull and all that, but I was wondering if I could get assistance with maybe one more thing for the same spread sheet. :biggrin:

After you guys helped me with the correct formula, I was curious if it was possible to modify it so that it returned the closest correct assembly number. By that I mean, if I left certain fields blank, and only had say 5 groups with numbers in them, it would display a number of assembly numbers that had those 5 sets of numbers in them.

I never even thought about this till you had finished helping me figure out how to do the last bit. Being able to do display the information in this manner would probably be the most benificial.
 
Upvote 0
Cipher226 said:
Ummm... not that I'm not completely gratefull and all that, but I was wondering if I could get assistance with maybe one more thing for the same spread sheet. :biggrin:

After you guys helped me with the correct formula, I was curious if it was possible to modify it so that it returned the closest correct assembly number. By that I mean, if I left certain fields blank, and only had say 5 groups with numbers in them, it would display a number of assembly numbers that had those 5 sets of numbers in them.

I never even thought about this till you had finished helping me figure out how to do the last bit. Being able to do display the information in this manner would probably be the most benificial.
Click to expand...
Just off the top of my head without any testing, maybe...

Instead of using =10, try using something like >=N.

Where N is the minimum number of matches that you will accept. You mentioned 5 so N would be >=5.

Array entered**:

=INDEX(Sheet3!B3:B626,MATCH(TRUE,MMULT(--(B2:K2=Sheet3!C3:L626),ARRAY)>=5,0))

Or, you can use a COUNTA function to evaluate N as being the total number of codes you enter in B2:K2...

Array entered**:

=INDEX(Sheet3!B3:B626,MATCH(TRUE,MMULT(--(B2:K2=Sheet3!C3:L626),ARRAY)>=COUNTA(B2:K2),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This latter one seems like it might be the most logical option.
 
Last edited:
Upvote 0
That COUNTA one works, but the only issue is I can only get one refence number with that formula. There are some reference numbers that have the exact same set of numbers that make it up. I want to be able to show mutliple refence numbers. Markmmz formula can output multiple numbers, but you need to have all the numbers matching.
 
Upvote 0
Cipher226 said:
That COUNTA one works, but the only issue is I can only get one refence number with that formula. There are some reference numbers that have the exact same set of numbers that make it up. I want to be able to show mutliple refence numbers. Markmmz formula can output multiple numbers, but you need to have all the numbers matching.
Click to expand...
Maybe this... but I'm not sure what that highlighted phrase means.

AT545_Assembly_Number_List(2).xls

http://cjoint.com/?AIubZBSUrzR
 
Upvote 0
Maybe this:

Note: with the T. Valko's suggestion.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Group</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">01</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16-1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">16-2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">21</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">37</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #000000; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">503</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">509</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">504</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">501</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffff00; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">500</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; FONT-WEIGHT: bold">Assembly Number</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; FONT-WEIGHT: bold">Model</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6835200</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836424</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836425</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836426</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836427</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836428</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6836431</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6884018</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">6885202</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">AT 540</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD><TD style="TEXT-ALIGN: center">*****</TD></TR></TBODY></TABLE>Sheet11


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B6</TH><TD style="TEXT-ALIGN: left">=IF(A6="","",VLOOKUP(A6,Sheet3!A$3:B$626,2,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A6</TH><TD style="TEXT-ALIGN: left">{=IF(SUM(--(MMULT(--(B$2:K$2*1=Sheet3!C$3:L$626),ROW($1:$10)^0)>=COUNTA(B$2:K$2)))>=ROWS(A$6:A6),
INDEX(Sheet3!A$3:A$626,SMALL(IF(MMULT(--(B$2:K$2*1=Sheet3!C$3:L$626),ROW($1:$10)^0)>=COUNTA(B$2:K$2),ROW(Sheet3!A$3:A$626)-ROW(Sheet3!A$3)+1),ROWS(A$6:A6))),"")}

</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Sheet3 Filtered

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Main Housing</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Oil Pump</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Speed & Governor Drive Gears</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Clutches / Gear Unit</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Control Valve</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Retarder Control valve</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Governor</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Torque Converter</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Oil Pan</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Vacum Modulator</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Assembly Number</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Model</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">01</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">10</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">11</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">14</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">16-1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">16-2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">17</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">21</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">37</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">40</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">6835200</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">503</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">509</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">504</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">544</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">540</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">507</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">514</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">501</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">500</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">6835200</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836424</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836424</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836425</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836425</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836426</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836426</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836427</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836427</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836428</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836428</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836431</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6836431</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">34</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6884018</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">508</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6884018</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">39</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6885202</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">543</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">6885202</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">627</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>Sheet3


Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,514
Messages
6,179,223
Members
452,896
Latest member
IGT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top