Restructure of data using array formulas

JannetteChristie

Board Regular
Joined
Dec 14, 2015
Messages
117
Hello,

I have the following data:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>L</th><th>AK</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">VR3-1005-LHT</td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">No</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">2</td><td style=";">FRB50</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">3</td><td style=";">FRB65</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">4</td><td style=";">FS5-WRAS</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">5</td><td style=";">EC65</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">6</td><td style=";">DPS-WMB-EC65</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">7</td><td style=";">DPS-FMB-EC65</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">8</td><td style=";">MSV1-0505-LHM</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">9</td><td style=";">VR3-1005-LHT</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">10</td><td style=";">FRB65</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">11</td><td style=";">FRB50</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">12</td><td style=";">FS5-WRAS</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">13</td><td style=";">LOW-UK1100100060</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">14</td><td style=";">VR4-0509-LHM</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">15</td><td style=";">FRB50</td><td style="text-align: center;;">2</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">16</td><td style=";">FS5-WRAS</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">17</td><td style=";">VMV3-10-H110</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">18</td><td style=";">FRB100</td><td style="text-align: center;;">2</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">19</td><td style=";">VMV-NOTE</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">20</td><td style=";">FS5-WRAS</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">21</td><td style=";">VR3-0409-LHT</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">22</td><td style=";">VR2-0507-LHM</td><td style="text-align: center;;">1</td><td style=";">No</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;;">23</td><td style=";">FRB50</td><td style="text-align: center;;">2</td><td style=";">Yes</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;;">24</td><td style=";">FS5-WRAS</td><td style="text-align: center;;">1</td><td style=";">Yes</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Create Quote</p><br /><br />

I am trying to use some excel formulas to show the data in this format:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;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><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;;">Standard</td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">VR3-1005-LHT</td><td style="text-align: center;border-top: 1px solid black;;">1</td><td style="border-top: 1px solid black;;">DUTYPOINT</td><td style=";">Recommended Extras</td><td style="text-align: right;;">3</td><td style=";">FRB50</td><td style="text-align: right;;">1</td><td style=";">FRB65</td><td style="text-align: right;;">1</td><td style=";">FS5-WRAS</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></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Standard</td><td style="text-align: center;;">5</td><td style=";">EC65</td><td style="text-align: center;;">1</td><td style=";">CONDITIONER</td><td style=";">Recommended Extras</td><td style="text-align: right;;">2</td><td style=";">DPS-WMB-EC65</td><td style="text-align: right;;">1</td><td style=";">DPS-FMB-EC65</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;">4</td><td style=";">Standard</td><td style="text-align: center;;">8</td><td style=";">MSV1-0505-LHM</td><td style="text-align: center;;">1</td><td style=";">HIU</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Standard</td><td style="text-align: center;;">9</td><td style=";">VR3-1005-LHT</td><td style="text-align: center;;">1</td><td style=";">DUTYPOINT</td><td style=";">Recommended Extras</td><td style="text-align: right;;">3</td><td style=";">FRB50</td><td style="text-align: right;;">1</td><td style=";">FRB65</td><td style="text-align: right;;">1</td><td style=";">FS5-WRAS</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></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Standard</td><td style="text-align: center;;">13</td><td style=";">LOW-UK1100100060</td><td style="text-align: center;;">1</td><td style=";">HIU</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Standard</td><td style="text-align: center;;">14</td><td style=";">VR4-0509-LHM</td><td style="text-align: center;;">1</td><td style=";">DUTYPOINT</td><td style=";">Recommended Extras</td><td style="text-align: right;;">2</td><td style=";">FRB50</td><td style="text-align: right;;">2</td><td style=";">FS5-WRAS</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=";">Standard</td><td style="text-align: center;;">17</td><td style=";">VMV3-10-H110</td><td style="text-align: center;;">1</td><td style=";">DUTYPOINT</td><td style=";">Recommended Extras</td><td style="text-align: right;;">3</td><td style=";">FRB100</td><td style="text-align: right;;">2</td><td style=";">VMV-NOTE</td><td style="text-align: right;;">1</td><td style=";">FS5-WRAS</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></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Standard</td><td style="text-align: center;;">21</td><td style=";">VR3-0409-LHT</td><td style="text-align: center;;">1</td><td style=";">DUTYPOINT</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Standard</td><td style="text-align: center;;">22</td><td style=";">VR2-0507-LHM</td><td style="text-align: center;;">1</td><td style=";">DUTYPOINT</td><td style=";">Recommended Extras</td><td style="text-align: right;;">2</td><td style=";">FRB50</td><td style="text-align: right;;">2</td><td style=";">FS5-WRAS</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></tbody></table><p style="width:8.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">PrintQuoteData</p><br /><br />

To get only the Products that have not got recommended extra, I have used the following formula:
{=IFERROR(INDEX('Create Quote'!$A$1:$AL$101,SMALL(IF(('Create Quote'!$A$1:$A$110<>"")*('Create Quote'!$A$1:$A$110<>"To start please select either Booster Set / Standard")*('Create Quote'!$AK$1:$AK$110="No"),ROW('Create Quote'!$A$1:$A$110)),ROW(1:1)),4),"")}

I am struggling to show the recommend extras for each of the products where they exist - hoping that this makes some sense
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Check the following proposed formulas, maybe, they can help you a little.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:161.58px;" /><col style="width:37.07px;" /><col style="width:38.97px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:31.37px;" /><col style="width:122.61px;" /><col style="width:119.76px;" /><col style="width:121.66px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >VR3-1005-LHT</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB50</td><td >FRB65</td><td >VR3-1005-LHT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >EC65</td><td style="text-align:right; ">5</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >DPS-WMB-EC65</td><td >EC65</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >MSV1-0505-LHM</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >VR3-1005-LHT</td><td style="text-align:right; ">9</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB65</td><td >FRB50</td><td >VR3-1005-LHT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >LOW-UK1100100060</td><td style="text-align:right; ">13</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >VR4-0509-LHM</td><td style="text-align:right; ">14</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >FRB50</td><td >VR4-0509-LHM</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >VMV3-10-H110</td><td style="text-align:right; ">17</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB100</td><td >VMV-NOTE</td><td >VMV3-10-H110</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >VR3-0409-LHT</td><td style="text-align:right; ">21</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >VR2-0507-LHM</td><td style="text-align:right; ">22</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >FRB50</td><td >VR2-0507-LHM</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">1</td><td >VR3-1005-LHT</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">2</td><td >FRB50</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">3</td><td >FRB65</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">4</td><td >FS5-WRAS</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">5</td><td >EC65</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">6</td><td >DPS-WMB-EC65</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">7</td><td >DPS-FMB-EC65</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">8</td><td >MSV1-0505-LHM</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">9</td><td >VR3-1005-LHT</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">10</td><td >FRB65</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">11</td><td >FRB50</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">12</td><td >FS5-WRAS</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">13</td><td >LOW-UK1100100060</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="text-align:right; ">14</td><td >VR4-0509-LHM</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td style="text-align:right; ">15</td><td >FRB50</td><td style="text-align:right; ">2</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td style="text-align:right; ">16</td><td >FS5-WRAS</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="text-align:right; ">17</td><td >VMV3-10-H110</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="text-align:right; ">18</td><td >FRB100</td><td style="text-align:right; ">2</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="text-align:right; ">19</td><td >VMV-NOTE</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td style="text-align:right; ">20</td><td >FS5-WRAS</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td style="text-align:right; ">21</td><td >VR3-0409-LHT</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="text-align:right; ">22</td><td >VR2-0507-LHM</td><td style="text-align:right; ">1</td><td >No</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="text-align:right; ">23</td><td >FRB50</td><td style="text-align:right; ">2</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >36</td><td style="text-align:right; ">24</td><td >FS5-WRAS</td><td style="text-align:right; ">1</td><td >Yes</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=INDEX($B$13:$B$36,SUMPRODUCT(SMALL(IF($D$13:$D$36="No",ROW($D$13:$D$36)),ROWS(D$2:D2)))-ROW($D$12))}</td></tr><tr><td >C2</td><td >{=INDEX($A$13:$A$36,SUMPRODUCT(SMALL(IF($B$13:$B$36=B2,ROW($B$13:$B$36)),COUNTIF($B$2:B2,B2)))-ROW($D$12))}</td></tr><tr><td >D2</td><td >=VLOOKUP(B2,$B$13:$C$36,2,0)</td></tr><tr><td >G2</td><td >=IF(C3="",$A$36-C2,C3-C2-1)</td></tr><tr><td >H2</td><td >=IF($G2>0,OFFSET($A$1,MATCH($C2,$A$1:$A$36,0)+MOD(COLUMNS($H$2:H2),$G2)-1,1),"")</td></tr><tr><td >I2</td><td >=IF($G2>0,OFFSET($A$1,MATCH($C2,$A$1:$A$36,0)+MOD(COLUMNS($H$2:I2),$G2)-1,1),"")</td></tr><tr><td >J2</td><td >=IF($G2>0,OFFSET($A$1,MATCH($C2,$A$1:$A$36,0)+MOD(COLUMNS($H$2:J2),$G2)-1,1),"")</td></tr></table></td></tr></table>
 

JannetteChristie

Board Regular
Joined
Dec 14, 2015
Messages
117
Thanks for your response Dante,

I am a little confused on the login used for cell H2,I2 and J2. Am I right in thing that the data that is shown in A13 to A36 is the data at the top of the original post. If I am correct can I just advise that this data is actually on another sheet in the workbook, I assume that this will mean changes to the function you have shown
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Thanks for your response Dante,

I am a little confused on the login used for cell H2,I2 and J2. Am I right in thing that the data that is shown in A13 to A36 is the data at the top of the original post. If I am correct can I just advise that this data is actually on another sheet in the workbook, I assume that this will mean changes to the function you have shown

These would be the changes

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:136.87px;" /><col style="width:23.76px;" /><col style="width:23.76px;" /><col style="width:21.86px;" /><col style="width:21.86px;" /><col style="width:13.31px;" /><col style="width:118.81px;" /><col style="width:135.92px;" /><col style="width:141.62px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >VR3-1005-LHT</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB50</td><td >FRB65</td><td >VR3-1005-LHT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >EC65</td><td style="text-align:right; ">5</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >DPS-WMB-EC65</td><td >EC65</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >MSV1-0505-LHM</td><td style="text-align:right; ">8</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >VR3-1005-LHT</td><td style="text-align:right; ">9</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB65</td><td >FRB50</td><td >VR3-1005-LHT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >LOW-UK1100100060</td><td style="text-align:right; ">13</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >VR4-0509-LHM</td><td style="text-align:right; ">14</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >FRB50</td><td >VR4-0509-LHM</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >VMV3-10-H110</td><td style="text-align:right; ">17</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">3</td><td >FRB100</td><td >VMV-NOTE</td><td >VMV3-10-H110</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >VR3-0409-LHT</td><td style="text-align:right; ">21</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >VR2-0507-LHM</td><td style="text-align:right; ">22</td><td style="text-align:right; ">1</td><td > </td><td > </td><td style="text-align:right; ">2</td><td >FRB50</td><td >VR2-0507-LHM</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >{=INDEX(Sheet1!$B$13:$B$36,SUMPRODUCT(SMALL(IF(Sheet1!$D$13:$D$36="No",ROW(Sheet1!$D$13:$D$36)),ROWS(D$2:D2)))-ROW(Sheet1!$D$12))}</td></tr><tr><td >C2</td><td >{=INDEX(Sheet1!$A$13:$A$36,SUMPRODUCT(SMALL(IF(Sheet1!$B$13:$B$36=B2,ROW(Sheet1!$B$13:$B$36)),COUNTIF($B$2:B2,B2)))-ROW(Sheet1!$D$12))}</td></tr><tr><td >D2</td><td >=VLOOKUP(B2,Sheet1!$B$13:$C$36,2,0)</td></tr><tr><td >G2</td><td >=IF(C3="",Sheet1!$A$36-C2,C3-C2-1)</td></tr><tr><td >H2</td><td >=IF($G2>0,OFFSET(Sheet1!$A$1,MATCH($C2,Sheet1!$A$1:$A$36,0)+MOD(COLUMNS($H$2:H2),$G2)-1,1),"")</td></tr><tr><td >I2</td><td >=IF($G2>0,OFFSET(Sheet1!$A$1,MATCH($C2,Sheet1!$A$1:$A$36,0)+MOD(COLUMNS($H$2:I2),$G2)-1,1),"")</td></tr><tr><td >J2</td><td >=IF($G2>0,OFFSET(Sheet1!$A$1,MATCH($C2,Sheet1!$A$1:$A$36,0)+MOD(COLUMNS($H$2:J2),$G2)-1,1),"")</td></tr></table></td></tr></table>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,057
Members
414,498
Latest member
jordanmiller7890

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
Top