Returning only one value

T

Thuynh08

New Member
Joined
Aug 17, 2011
Messages
30
Hi All,

Can someone please help me. Basically I have a material number with 2 descriptions. The one that begins with a "Z" is the deleted item. I managed to marry up the two item numbers by using Left,13 as the first 13 digits are the same. I would now like it to show only the first 13 material id and just the ONE product description. However if there is only one product description it will show that value. E.g:

Material ID: 9311783074906, Description: ZZZZZZEL 2 CUTTER-25MM##
Material ID: 931178307490601, Description: Excel 2 Cutter 25mm

End Result:
Material ID: 9311783074906, Description: Excel 2 Cutter 25mm

Any help is much appreciated many thanks :)
Thi.
 
Sort by date Sort by votes
Try this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">9311783074852</td><td style="font-weight: bold;;">9311783074838</td><td style="font-weight: bold;;">9311783034191</td><td style="font-weight: bold;;">9311783074920</td><td style="font-weight: bold;;">9311783037628</td><td style="font-weight: bold;;">9311783037611</td><td style="font-weight: bold;;">9311783037710</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Material</td><td style="font-weight: bold;;">Value Pack</td><td style="font-weight: bold;;">QTY</td><td style="font-weight: bold;;">Excel 2 75mm</td><td style="font-weight: bold;;">Excel 2 50mm</td><td style="font-weight: bold;;">Excel 2 75mm</td><td style="font-weight: bold;;">Excel 2 50mm</td><td style="font-weight: bold;;">Excel Ultra 75mm</td><td style="font-weight: bold;;">Ultra Slim 63mm</td><td style="font-weight: bold;;">Ultra Sash 63mm</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">9311783036140</td><td style=";">ZZZExcel 2 Value Pack##</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</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;">4</td><td style=";">9311783037789</td><td style=";">Excel Ultra Introductory Pack##</td><td style="text-align: right;;">1</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;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">931178303790101</td><td style=";">Excel Ultra Brush Pack 75mm & Slim 63mm</td><td style="text-align: right;;">1</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;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">931178303791801</td><td style=";">Excel 2 Brush Pk Bonus Slim Line 50mm##</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</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;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">931178303792501</td><td style=";">ZZZExcel 2 Brush Pk BonUS BBQ To Wipes##</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</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;">8</td><td style=";">931178303807601</td><td style=";">Excel 2 Brush 50mm 4Pk##</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">931178303809001</td><td style=";">ZZZExcel 2 Brush 75mm 4Pk##</td><td style="text-align: right;;">1</td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">931178303810601</td><td style=";">Excel 2 Cutter 50mm 4Pk##</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</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;">11</td><td style=";">931178303812001</td><td style=";">Excel 2 Cutter 75mm 4Pk##</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">931178303866301</td><td style=";">Excel 2 Brush Pk (2x75mm&1x63mm Cutter)</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</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;">13</td><td style=";">931178303869401</td><td style=";">Excel 2 Brush Pack with 7 in 1 Tool</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</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;">14</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;">15</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;">16</td><td style="font-weight: bold;;">Material</td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">QTY</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;">17</td><td style=";">9311783074852</td><td style=";">Excel 2 75mm</td><td style="text-align: right;;">14</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;">18</td><td style=";">9311783074838</td><td style=";">Excel 2 50mm</td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">9311783034191</td><td style=";">Excel 2 75mm</td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">9311783074920</td><td style=";">Excel 2 50mm</td><td style="text-align: right;;">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></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">9311783037628</td><td style=";">Excel Ultra 75mm</td><td style="text-align: right;;">2</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;">22</td><td style=";">9311783037611</td><td style=";">Ultra Slim 63mm</td><td style="text-align: right;;">2</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;">23</td><td style=";">9311783037710</td><td style=";">Ultra Sash 63mm</td><td style="text-align: right;;">1</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;">24</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;">25</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><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C17</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">D$1:J$1=A17,$D$3:$J$13</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Thanks Markmzz. However I need to reference back to the first table that I created. Instead I tried this but then I would manually have to include all IFs for the various Value Packs. Is there a way to reference back to that table that I created in a different worksheet?

=IF($C$53:$C$105="ZZZOAM BRUSH 50##",(SUMIF($C$53:$C$105,"ZZZExcel 2 Value Pack##",$D$53:$D$106))*3,D54)
 
Upvote 0
Also in regard to the first formula you were helping me with. Your formula only references from row 2 - 6 of the column. Is there a way to make it reference starting from A$2: the last occupied cell in the column

Thanks again :)

markmzz said:
Try this (array formulas - use Ctrl+Shift+Enter and not only Enter):

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></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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Material ID</TD><TD style="FONT-WEIGHT: bold">Description</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold">Material ID</TD><TD style="FONT-WEIGHT: bold">Description</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>9311783074906</TD><TD>ZZZZZZEL 2 CUTTER-25MM##</TD><TD style="TEXT-ALIGN: right"></TD><TD>9311783074906</TD><TD>Excel 2 Cutter 25mm</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>931178307490601</TD><TD>Excel 2 Cutter 25mm</TD><TD style="TEXT-ALIGN: right"></TD><TD>9311783074907</TD><TD>ZZZZZZEL 2 CUTTER-25MM##02</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>9311783074907</TD><TD>ZZZZZZEL 2 CUTTER-25MM##02</TD><TD style="TEXT-ALIGN: right"></TD><TD>9311783074908</TD><TD>Excel 3 Cutter 25mm</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>931178307490801</TD><TD>ZZZZZZEL 2 CUTTER-25MM##03</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>9311783074908</TD><TD>Excel 3 Cutter 25mm</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="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="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>
Sheet2​


<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>D2</TH><TD style="TEXT-ALIGN: left">{=IFERROR(LEFT(INDEX(A$2:A$6,SMALL(IF(FREQUENCY(MATCH(LEFT(A$2:A$6,13),A$2:A$6,0),MATCH(LEFT(A$2:A$6,13),A$2:A$6,0))>0,ROW(A$2:A$6)-ROW(A$2)+1,2^21),ROWS(D$2:D2))),13),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=INDEX($B$2:$B$6,MAX(IF(LEFT($A$2:$A$6,13)=D2,IF(SUMPRODUCT(--(LEFT($A$2:$A$6,13)=D2))>1,IF(LEFT($B$2:$B$6,1)<>"z",ROW($B$2:$B$6)-ROW($B$2)+1,0),ROW($B$2:$B$6)-ROW($B$2)+1),0)))}</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
Click to expand...
 
Upvote 0
Thuynh08 said:
Also in regard to the first formula you were helping me with. Your formula only references from row 2 - 6 of the column. Is there a way to make it reference starting from A$2: the last occupied cell in the column

Thanks again :)
Click to expand...

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Material ID</td><td style="font-weight: bold;;"> Description</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Material ID</td><td style="font-weight: bold;;"> Description</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">9311783074906</td><td style=";">ZZZZZZEL 2 CUTTER-25MM##</td><td style="text-align: right;;"></td><td style=";">9311783074906</td><td style=";">Excel 2 Cutter 25mm</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">931178307490601</td><td style=";">Excel 2 Cutter 25mm</td><td style="text-align: right;;"></td><td style=";">9311783074907</td><td style=";">ZZZZZZEL 2 CUTTER-25MM##02</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">9311783074907</td><td style=";">ZZZZZZEL 2 CUTTER-25MM##02</td><td style="text-align: right;;"></td><td style=";">9311783074908</td><td style=";">Excel 3 Cutter 25mm</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">931178307490801</td><td style=";">ZZZZZZEL 2 CUTTER-25MM##03</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">9311783074908</td><td style=";">Excel 3 Cutter 25mm</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</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;">8</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><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IFERROR(<font color="Blue">LEFT(<font color="Red">INDEX(<font color="Green">myMat,SMALL(<font color="Purple">IF(<font color="Teal">FREQUENCY(<font color="#FF00FF">MATCH(<font color="Navy">LEFT(<font color="Blue">myMat,13</font>),myMat,0</font>),MATCH(<font color="Navy">LEFT(<font color="Blue">myMat,13</font>),myMat,0</font>)</font>)>0,ROW(<font color="#FF00FF">myMat</font>)-ROW(<font color="#FF00FF">A$2</font>)+1,2^21</font>),ROWS(<font color="Teal">D$2:D2</font>)</font>)</font>),13</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">{=IF(<font color="Blue">D2="","",INDEX(<font color="Red">myDescr,MAX(<font color="Green">IF(<font color="Purple">LEFT(<font color="Teal">myMat,13</font>)=D2,IF(<font color="Teal">SUMPRODUCT(<font color="#FF00FF">--(<font color="Navy">LEFT(<font color="Blue">myMat,13</font>)=D2</font>)</font>)>1,IF(<font color="#FF00FF">LEFT(<font color="Navy">myDescr,1</font>)<>"z",ROW(<font color="Navy">myDescr</font>)-ROW(<font color="Navy">$B$2</font>)+1,0</font>),ROW(<font color="#FF00FF">myDescr</font>)-ROW(<font color="#FF00FF">$B$2</font>)+1</font>),0</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myDescr</th><td style="text-align:left">=OFFSET(<font color="Blue">Sheet2!$B$2,0,0,COUNTA(<font color="Red">Sheet2!$B:$B</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myMat</th><td style="text-align:left">=OFFSET(<font color="Blue">Sheet2!$A$2,0,0,COUNTA(<font color="Red">Sheet2!$A:$A</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
Thuynh08 said:
Thanks Markmzz. However I need to reference back to the first table that I created. Instead I tried this but then I would manually have to include all IFs for the various Value Packs. Is there a way to reference back to that table that I created in a different worksheet?

=IF($C$53:$C$105="ZZZOAM BRUSH 50##",(SUMIF($C$53:$C$105,"ZZZExcel 2 Value Pack##",$D$53:$D$106))*3,D54)
Click to expand...

Thuynh08,

Sorry, but I didn't understand what you want here.

Give me a example of what you have and what you want.

Markmzz
 
Upvote 0
markmzz said:
Thuynh08,

Sorry, but I didn't understand what you want here.

Give me a example of what you have and what you want.

Markmzz
Click to expand...

Hi Markmzz,

Okay so the data i will receive will look like the below:

Material Description Reporting Quantity
9311830341191 ZZZExcel 2 Value Pack## 165
9311839035938 Excel Brush 25mm## 13
9311783035969 ZZZExcel 2 Ultimate Value Pack## 3000
9311783036201 Excel 2 Ultimate 25mm## 49
9311783036202 Excel 2 Ultimate 55mm 51
9311783074401R ZZZZZZEL BRUSH 25mm## 728
9311783074418R ZZZZZZEL BRUSH 38mm## 499
9311783074425R ZZZZZZEL BRUSH 50mm## 892
9311783035945 Excel 2 Brush Value Pack## 85

But what i want is this based on knowing how many brushes belong in each Value Pack it will multiply the Value Pack by the number of brushes and then add the original brush quantity to the final figure.

Material Description Reporting Quantity Actual Quantity
9311830341191 ZZZExcel 2 Value Pack## 165 0
9311839035938 Excel Brush 25mm## 13 [=(165*3)+C3]
9311783035969 ZZZExcel 2 Ultimate Value Pack## 3000 0
9311783036201 Excel 2 Ultimate 25mm## 49 [=(C34*2)+C5]
9311783036202 Excel 2 Ultimate 55mm 51 [=(C34*1)+C6]
9311783074401R ZZZZZZEL BRUSH 25mm## 728 728
9311783074418R ZZZZZZEL BRUSH 38mm## 499 499
9311783074425R ZZZZZZEL BRUSH 50mm## 892 [=(C40*4)+C9]
9311783035945 Excel 2 Brush Value Pack## 85 0

Thank you. I've been putting borders and creating new data tables i hope it turns out :eeek:
 
Upvote 0
markmzz said:
Thuynh08,

Sorry, but I didn't understand what you want here.

Give me a example of what you have and what you want.

Markmzz
Click to expand...

Oh curse Google Chrome no wonder i couldn't paste the cells!

Hope this helps a little. This is the exact data that i will be given:

<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=421><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 99pt; HEIGHT: 30.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl113 height=41 width=132>Material</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 165pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl113 width=220>Description</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl114 width=69>Reporting Quantity</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f2dddc; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl109 height=18>9311830341191</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl104>ZZZExcel 2 Value Pack##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl115 align=right>165</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl110 height=18>9311839035938</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105>Excel Brush 25mm##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105 align=right>13</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f2dddc; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl111 height=18>9311783035969</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl106>ZZZExcel 2 Ultimate Value Pack##</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl116 align=right>3000</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl110 height=18>9311783036201</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl105>Excel 2 Ultimate 25mm##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl105 align=right>49</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl110 height=18>9311783036202</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105>Excel 2 Ultimate 55mm</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105 align=right>51</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107 height=18>9311783074401R</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107>ZZZZZZEL BRUSH 25mm##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl117>728</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl108 height=18>9311783074418R</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl108>ZZZZZZEL BRUSH 38mm##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl118>499</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107 height=18>9311783074425R</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107>ZZZZZZEL BRUSH 50mm##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl117>892</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #f2dddc; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl112 height=18>9311783035945</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl112>Excel 2 Brush Value Pack##</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl119 align=right>85</TD></TR></TBODY></TABLE>

However now i need some report or formula or pivot table to show something like this wher it will multiply the certain brush components by the Value Pack quantity. But i'm not sure how to go about doing it...

<TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=556><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 24pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=18 width=32>
​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 99pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl127 width=132>
A​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 165pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl127 width=220>
B​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl127 width=69>
C​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 77pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl127 width=103>
D​
</TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 30.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=41 align=right>
1​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl119 width=132>
Material​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 165pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl119 width=220>
Description​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl121 width=69>
Reporting Quantity​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; WIDTH: 77pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl120 width=103>
Actual Quantity​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
2​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl115>
9311830341191​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl110>
ZZZExcel 2 Value Pack##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl122 align=right>
165​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl104>
0​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
3​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl116>
9311839035938​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl111>
Excel Brush 25mm##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl111 align=right>
13​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105>
[=(165*3)+C3]​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
4​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl117>
9311783035969​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl112>
ZZZExcel 2 Ultimate Value Pack##​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl123 align=right>
3000​
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl106>
0​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
5​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl116>
9311783036201​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl111>
Excel 2 Ultimate 25mm##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl111 align=right>
49​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl105>
[=(C34*2)+C5]​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
6​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl116>
9311783036202​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl111>
Excel 2 Ultimate 55mm​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl111 align=right>
51​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl105>
[=(C34*1)+C6]​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
7​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl113>
9311783074401R​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl113>
ZZZZZZEL BRUSH 25mm##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl124>
728​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107>
728​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
8​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl114>
9311783074418R​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl114>
ZZZZZZEL BRUSH 38mm##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl125>
499​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl108>
499​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
9​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl113>
9311783074425R​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl113>
ZZZZZZEL BRUSH 50mm##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl124>
892​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl107>
[=(C40*4)+C9]​
</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl128 height=18 align=right>
10​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl118>
9311783035945​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl118>
Excel 2 Brush Value Pack##​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dddc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl126 align=right>
85​
</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #eaf1dd; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl109 align=right>0</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
548
Micron
M
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
331
MARZIOTULLIO
M
trishcollins
  • Question Question
Find and filter on two different unique values
Replies
5
Views
216
trishcollins
trishcollins
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
498
Akuini
Akuini
C
  • Solved
Index match function
Replies
3
Views
737
Alex Blakenburg
A

Forum statistics

Threads
1,224,586
Messages
6,179,713
Members
452,939
Latest member
WCrawford

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