Sorting top available

[miles00577]

I'm trying to create a formula that copies data to another spreadsheet.

I've got a table set up with the following:

Column A = Price
Column B = Product
Column C = Product Number
Column D = Order Number

I'm taking a product list of say 100 items. What I'm trying to do is take data in A, B and C if there is nothing in Column D and organize it by price, but I just want the top 5 available items by price. If Column D is filled, then the data would not copy over. I can easily create a table to move the unavailable products to the bottom of the table, but then I constantly have to update that table once an item is gone. I'm trying to cut corners so I don't have to refresh the table.

 

[Trevor G]

Look to use Pivot Tables as you can set to look at Top 10 or others if using Excel 2007 or higher.

 

[miles00577]

I am currently using tables. However, the certain data needs to be copied over to another spreadsheet. This isn't for just one table, but it's taking data from multiple tables, so instead of having to reapply (or refresh) each table, I was looking for a formula to just copy the info over if there is nothing showing in Column D. If there is something showing in Column D, then the data does not apply.

 

[markmzz]

If I understand correctly what you want, then I think this can help you:

Note: the formula in A2 is a array formula, then entered with 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 /></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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Price</td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">Produc Number</td><td style="font-weight: bold;;">Order Number</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">152</td><td style=";">Product01</td><td style="text-align: right;;">7420</td><td style="text-align: right;;">120</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">274</td><td style=";">Product02</td><td style="text-align: right;;">2282</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">633</td><td style=";">Product03</td><td style="text-align: right;;">1617</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">218</td><td style=";">Product04</td><td style="text-align: right;;">1521</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">843</td><td style=";">Product05</td><td style="text-align: right;;">9352</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">320</td><td style=";">Product06</td><td style="text-align: right;;">7205</td><td style="text-align: right;;">130</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">837</td><td style=";">Product07</td><td style="text-align: right;;">1234</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">396</td><td style=";">Product08</td><td style="text-align: right;;">5646</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">824</td><td style=";">Product09</td><td style="text-align: right;;">6495</td><td style="text-align: right;;">400</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">820</td><td style=";">Product10</td><td style="text-align: right;;">4777</td><td style="text-align: right;;"></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">Sheet1</p><br /><br /><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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Price</td><td style="font-weight: bold;;">Product</td><td style="font-weight: bold;;">Produc Number</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">843</td><td style=";">Product05</td><td style="text-align: right;;">9352</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">837</td><td style=";">Product07</td><td style="text-align: right;;">1234</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">820</td><td style=";">Product10</td><td style="text-align: right;;">4777</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">633</td><td style=";">Product03</td><td style="text-align: right;;">1617</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">396</td><td style=";">Product08</td><td style="text-align: right;;">5646</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">A2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$1:1</font>)>5,"",INDEX(<font color="Red">TabProduct,MATCH(<font color="Green">LARGE(<font color="Purple">(<font color="Teal">ONumber=""</font>)*(<font color="Teal">Price-(<font color="#FF00FF">ROW(<font color="Navy">Price</font>)/1E+100</font>)</font>),ROWS(<font color="Teal">$1:1</font>)</font>),(<font color="Purple">ONumber=""</font>)*(<font color="Purple">Price-(<font color="Teal">ROW(<font color="#FF00FF">Price</font>)/1E+100</font>)</font>),0</font>),COLUMNS(<font color="Green">$A:A</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">ONumber</th><td style="text-align:left">=Sheet1!$D$2:INDEX(<font color="Blue">Sheet1!$D:$D,ROW(<font color="Red">Sheet1!$D$1</font>)+COUNTA(<font color="Red">Sheet1!$A:$A</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Price</th><td style="text-align:left">=Sheet1!$A$2:INDEX(<font color="Blue">Sheet1!$A:$A,ROW(<font color="Red">Sheet1!$A$1</font>)+COUNTA(<font color="Red">Sheet1!$A:$A</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">TabProduct</th><td style="text-align:left">=Sheet1!$A$2:INDEX(<font color="Blue">Sheet1!$C:$C,ROW(<font color="Red">Sheet1!$C$1</font>)+COUNTA(<font color="Red">Sheet1!$A:$A</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz