Sorting strings with formula

[yytsunamiyy]

Hi everyone,

I have a table that lists company names in Col A and contacts in Col B. I use that table as a data-validation table for Customer-contact tracking (basically a very simple CRM database).

In my contact tracking table I use data validation in Col's B & C. The validation is set up in a way that allows me to chose the contact (Col C) depending on the company (Col B) or the company depending on the contact chosen. The list that returns the contact names is a named range referring to the contacts Sheet Col D, which I sort alphabetically through the formulas in Col C and D in the contacts sheet. Obviously it is easier to search a alpha-sorted list of contact names than finding the person you are looking for in an apperently random list, as it would be the case if the named range would refer to Col B on the contacts sheet. That's why I'm jumping through these hoops.

It all works just fine - I am just wondering if there might be a way to unify the formulas in Columns C and D on the contacts sheet into one formula that returns a alpha-sorted list of strings.

This is more an exercise to see what you can get excel to do than a real need that has to be adressed, but if anyone is up for the challenge I'd love to see how it can be done.


Company / Contact sheet:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 211px"><COL style="WIDTH: 171px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Aquamaris Strandresidenz Rügen, Juliusruh</TD><TD>Bötzer, Manuela</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Behrens, Renate</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Aquamaris Strandresidenz Rügen, Juliusruh</TD><TD>Dörschel, Harald</TD><TD style="TEXT-ALIGN: right">7</TD><TD>Behrmann, Hr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Aquamaris Strandresidenz Rügen, Juliusruh</TD><TD>Wulff, Anette</TD><TD style="TEXT-ALIGN: right">44</TD><TD>Bötzer, Manuela</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>AVR Rügen, Göhren</TD><TD>Heissler, Hr.</TD><TD style="TEXT-ALIGN: right">17</TD><TD>Brüdgam, Edda</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Cliff Hotel, Sellin</TD><TD>Fr. Knuth</TD><TD style="TEXT-ALIGN: right">13</TD><TD>Bursche, Thomas</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Cliff Hotel, Sellin</TD><TD>Schwarz, Peter</TD><TD style="TEXT-ALIGN: right">36</TD><TD>Danzer, Fr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Dorint Strandresort & Spa Ostseebad Wustrow</TD><TD>Behrmann, Hr.</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Dörschel, Harald</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>FH Stralsund</TD><TD>Rethmeier, Bernd</TD><TD style="TEXT-ALIGN: right">32</TD><TD>Färber - Eichler, Fr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Folian GmbH</TD><TD>Löhe, Fr.</TD><TD style="TEXT-ALIGN: right">26</TD><TD>Färber, Hr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>Fremdenverkehrs- und Gewerbeverein Göhren e.V.</TD><TD>Brüdgam, Edda</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Finow, Hr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Getränke Nordmann</TD><TD>Bursche, Thomas</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Fleck, Petra</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Golfpark Strelasund</TD><TD>Remer, Jörg</TD><TD style="TEXT-ALIGN: right">31</TD><TD>Fr. Kantim</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>Gutshof Groß Behnkenhagen</TD><TD>Färber - Eichler, Fr.</TD><TD style="TEXT-ALIGN: right">8</TD><TD>Fr. Knuth</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>Gutshof Groß Behnkenhagen</TD><TD>Färber, Hr.</TD><TD style="TEXT-ALIGN: right">9</TD><TD>Fr. Turowski</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>Hotel Albatros, Göhren</TD><TD>Hr. Franz</TD><TD style="TEXT-ALIGN: right">20</TD><TD>Germer, Ralf</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>Hotel Meerlust Zingst</TD><TD>Sund, Britt</TD><TD style="TEXT-ALIGN: right">41</TD><TD>Heinichen, Christiane</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD>Hotel Vier Jahreszeiten Binz </TD><TD>Süße, Yvonne</TD><TD style="TEXT-ALIGN: right">40</TD><TD>Heissler, Hr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD>Hotel Vier Jahreszeiten Zingst</TD><TD>Fr. Kantim</TD><TD style="TEXT-ALIGN: right">12</TD><TD>Hofmann, Anja</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD>Inselhotel Rügen, Sellin</TD><TD>Riffel, Markus</TD><TD style="TEXT-ALIGN: right">33</TD><TD>Höhn, Ilgo Hagen</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD>Jasmar Resort Rügen, Neddesitz</TD><TD>Germer, Ralf</TD><TD style="TEXT-ALIGN: right">15</TD><TD>Hr. Franz</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD>Jugendherberge Stralsund</TD><TD>Heinichen, Christiane</TD><TD style="TEXT-ALIGN: right">16</TD><TD>Hr. Kraus</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD>Kurklinik Sellin</TD><TD>Zimmermann - Schulze, Cornelia</TD><TD style="TEXT-ALIGN: right">45</TD><TD>Hr. Völlkmer</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD>Kurmittelcentrum Zingst</TD><TD>Hr. Kraus</TD><TD style="TEXT-ALIGN: right">21</TD><TD>Kampe, Ilka</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD>Kurmittelcentrum Zingst</TD><TD>Wolff-Kraus, Eveline</TD><TD style="TEXT-ALIGN: right">43</TD><TD>Kietz, Jan-Holger</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD>Kurverwaltung Ostseebad Göhren</TD><TD>Waschow, Berit</TD><TD style="TEXT-ALIGN: right">42</TD><TD>Kühnel, Fr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD>Nationalparkzentrum Königsstuhl</TD><TD>Kühnel, Fr.</TD><TD style="TEXT-ALIGN: right">25</TD><TD>Löhe, Fr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD>Ostsee- Kurklinik Fischland GmbH</TD><TD>Pijpers, Hr.</TD><TD style="TEXT-ALIGN: right">30</TD><TD>Lohr, Bianca</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD>Ostseehotel Dierhagen</TD><TD>Finow, Hr.</TD><TD style="TEXT-ALIGN: right">10</TD><TD>Müller, Sylvi</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD>Ostseehotel Dierhagen</TD><TD>Kampe, Ilka</TD><TD style="TEXT-ALIGN: right">23</TD><TD>Neumann, Yvette</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD>Ostseehotel Dierhagen</TD><TD>Scheiwe, Fr.</TD><TD style="TEXT-ALIGN: right">35</TD><TD>Pijpers, Hr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD>Ostseehotel Waldschlößchen, Prerow</TD><TD>Danzer, Fr.</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Remer, Jörg</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD>Ostseeklinik Zingst</TD><TD>Fleck, Petra</TD><TD style="TEXT-ALIGN: right">11</TD><TD>Rethmeier, Bernd</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD>Ostseeklinik Zingst</TD><TD>Stein, Martin</TD><TD style="TEXT-ALIGN: right">38</TD><TD>Riffel, Markus</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD>Parkhotel Rügen, Bergen</TD><TD>Schalley, Charis</TD><TD style="TEXT-ALIGN: right">34</TD><TD>Schalley, Charis</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD>Pension und Restaurant "Zum Anker" Zingst</TD><TD>Hofmann, Anja</TD><TD style="TEXT-ALIGN: right">18</TD><TD>Scheiwe, Fr.</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD>Schlößchen Sundische Wiese, Zingst</TD><TD>Siemon, Steffen</TD><TD style="TEXT-ALIGN: right">37</TD><TD>Schwarz, Peter</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD>Steigenberger Strandhotel Zingst</TD><TD>Höhn, Ilgo Hagen</TD><TD style="TEXT-ALIGN: right">19</TD><TD>Siemon, Steffen</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD>Steigenberger Strandhotel Zingst</TD><TD>Fr. Turowski</TD><TD style="TEXT-ALIGN: right">14</TD><TD>Stein, Martin</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD>Strandhotels Arkona & Rugard, Binz</TD><TD>Behrens, Renate</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Stroh, Andreas</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD>Tourismusgesellschaft Binz</TD><TD>Lohr, Bianca</TD><TD style="TEXT-ALIGN: right">27</TD><TD>Süße, Yvonne</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD>Villa Lottum, Sellin</TD><TD>Stroh, Andreas</TD><TD style="TEXT-ALIGN: right">39</TD><TD>Sund, Britt</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD>Villa Sano, Baabe</TD><TD>Neumann, Yvette</TD><TD style="TEXT-ALIGN: right">29</TD><TD>Waschow, Berit</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</TD><TD>Waldseilpark Rügen, Altefähr</TD><TD>Kietz, Jan-Holger</TD><TD style="TEXT-ALIGN: right">24</TD><TD>Wolff-Kraus, Eveline</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</TD><TD>Waldseilpark Rügen, Altefähr</TD><TD>Müller, Sylvi</TD><TD style="TEXT-ALIGN: right">28</TD><TD>Wulff, Anette</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</TD><TD>Weisse Flotte Stralsund</TD><TD>Hr. Völlkmer</TD><TD style="TEXT-ALIGN: right">22</TD><TD>Zimmermann - Schulze, Cornelia</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Formulas: (filled down to C45 / D45)</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>C1</TD><TD>=IF(B1<>"",SUMPRODUCT((B1>=Kontakte)+0),"")</TD></TR><TR><TD>D1</TD><TD>=IF(C1<>"",INDEX(Kontakte,MATCH(ROW(),Kontakte_alpha_Rang,0)),"")</TD></TR><TR><TD>C2</TD><TD>=IF(B2<>"",SUMPRODUCT((B2>=Kontakte)+0),"")</TD></TR><TR><TD>D2</TD><TD>=IF(C2<>"",INDEX(Kontakte,MATCH(ROW(),Kontakte_alpha_Rang,0)),"")</TD></TR><TR><TD>C3</TD><TD>=IF(B3<>"",SUMPRODUCT((B3>=Kontakte)+0),"")</TD></TR><TR><TD>D3</TD><TD>=IF(C3<>"",INDEX(Kontakte,MATCH(ROW(),Kontakte_alpha_Rang,0)),"")</TD></TR><TR><TD>C4</TD><TD>=IF(B4<>"",SUMPRODUCT((B4>=Kontakte)+0),"")</TD></TR><TR><TD>D4</TD><TD>=IF(C4<>"",INDEX(Kontakte,MATCH(ROW(),Kontakte_alpha_Rang,0)),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4


Tracking sheet:

Aktivitäten

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 57px"><COL style="WIDTH: 217px"><COL style="WIDTH: 141px"><COL style="WIDTH: 72px"><COL style="WIDTH: 250px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Datum</TD><TD style="FONT-WEIGHT: bold">Kunde</TD><TD style="FONT-WEIGHT: bold">Kontakt</TD><TD style="FONT-WEIGHT: bold">Typ</TD><TD style="FONT-WEIGHT: bold">Zweck</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">18.05.10</TD><TD>Jasmar Resort Rügen, Neddesitz</TD><TD>Germer, Ralf</TD><TD>Besuch</TD><TD>Erstbesuch, Vorstellung durch Fr. Plantikow, </TD></TR><TR style="HEIGHT: 73px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">18.05.10</TD><TD>Parkhotel Rügen, Bergen</TD><TD>Schalley, Charis</TD><TD>Besuch</TD><TD>Erstbesuch, Vorstellung durch Fr. Plantikow, </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">25.05.10</TD><TD>Parkhotel Rügen, Bergen</TD><TD>Schalley, Charis</TD><TD>Networking Event</TD><TD>Kontaktaufnahme Xing</TD></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">27.05.10</TD><TD>Nationalparkzentrum Königsstuhl</TD><TD>Kühnel, Fr.</TD><TD>Besuch</TD><TD>Erstbesuch, Vorstellung durch Fr. Plantikow, </TD></TR></TBODY></TABLE>

Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

 

[yytsunamiyy]

Just a quick bump after 2 weeks