Index, Match, & Sort

notruck

New Member
Joined
Jan 19, 2011
Messages
34
Hello All,

I have multiple worksheets pulling information from one worksheet which is an imported database dump from another team. The dump consists of data similar to columns A & B. All other worksheets are similar to columns D thru G. I can gather the required information for each sheet using the formula shown below but I really need to present the data in alphabetical order. I have created a helper column that returns a number for the order but I cannot figure out how to combine it with the formula below. Any ideas?


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;background-color: #9BC2E6;;">Name</td><td style="font-weight: bold;text-align: center;background-color: #9BC2E6;;">City</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;background-color: #A9D08E;;">Alabama</td><td style="font-weight: bold;text-align: center;background-color: #A9D08E;;">Arizona</td><td style="font-weight: bold;text-align: center;background-color: #A9D08E;;">California</td><td style="font-weight: bold;text-align: center;background-color: #A9D08E;;">Colorado</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Oakland</td><td style="text-align: right;;"></td><td style="text-align: center;;">Montgomery</td><td style="text-align: center;;">Scottsdale</td><td style="text-align: center;;">Oakland</td><td style="text-align: center;;">Aurora</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Montgomery</td><td style="text-align: right;;"></td><td style="text-align: center;;">Huntsville</td><td style="text-align: center;;">Phoenix</td><td style="text-align: center;;">Santa Ana</td><td style="text-align: center;;">Fort Collins</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Scottsdale</td><td style="text-align: right;;"></td><td style="text-align: center;;">Mobile</td><td style="text-align: center;;">Gilbert</td><td style="text-align: center;;">San Jose</td><td style="text-align: center;;">Denver</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Santa Ana</td><td style="text-align: right;;"></td><td style="text-align: center;;">Birmingham</td><td style="text-align: center;;">Mesa</td><td style="text-align: center;;">Riverside</td><td style="text-align: center;;">Colorado Springs</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">California</td><td style="text-align: center;;">San Jose</td><td style="text-align: right;;"></td><td style="text-align: center;;">Hoover</td><td style="text-align: center;;">Chandler</td><td style="text-align: center;;">Fresno</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;;">Arkansas</td><td style="text-align: center;;">Little Rock</td><td style="text-align: right;;"></td><td style="text-align: center;;">Tuscaloosa</td><td style="text-align: center;;">Glendale</td><td style="text-align: center;;">Los Angeles</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;;">Alaska</td><td style="text-align: center;;">Juneau</td><td style="text-align: right;;"></td><td style="text-align: center;;">Dothan</td><td style="text-align: center;;">Tuscon</td><td style="text-align: center;;">Long Beach</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Huntsville</td><td style="text-align: right;;"></td><td style="text-align: center;;">Auburn</td><td style="text-align: center;;"></td><td style="text-align: center;;">San Francisco</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Riverside</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">San Diego</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Phoenix</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Sacramento</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Gilbert</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Anaheim</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Fresno</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Mobile</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Mesa</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Los Angeles</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Long Beach</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Chandler</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Birmingham</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: center;;">Colorado</td><td style="text-align: center;;">Aurora</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Hoover</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Glendale</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: center;;">Arizona</td><td style="text-align: center;;">Tuscon</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Tuscaloosa</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: center;;">Arkansas</td><td style="text-align: center;;">Fort Smith</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: center;;">Colorado</td><td style="text-align: center;;">Fort Collins</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: center;;">Alaska</td><td style="text-align: center;;">Fairbanks</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Dothan</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: center;;">Colorado</td><td style="text-align: center;;">Denver</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: center;;">Colorado</td><td style="text-align: center;;">Colorado Springs</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: center;;">California</td><td style="text-align: center;;">San Francisco</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: center;;">California</td><td style="text-align: center;;">San Diego</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Sacramento</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style="text-align: center;;">Alabama</td><td style="text-align: center;;">Auburn</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style="text-align: center;;">Alaska</td><td style="text-align: center;;">Anchorage</td><td style="text-align: right;;"></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><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style="text-align: center;;">California</td><td style="text-align: center;;">Anaheim</td><td style="text-align: right;;"></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:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$2:$B$36,SMALL(<font color="Green">IF(<font color="Purple">$A$2:$A$36=D$1,ROW(<font color="Teal">$B$2:$B$36</font>)-MIN(<font color="Teal">ROW(<font color="#FF00FF">$B$2:$B$36</font>)</font>)+1</font>),ROWS(<font color="Purple">$D$4:D4</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 />

Thanks for your help …
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,261
Couldn't you just alphabetically sort the imported data in columns A and B?
 

notruck

New Member
Joined
Jan 19, 2011
Messages
34
Yes, which is what I'm doing now. But, many of the other worksheets need a different sort, so depending on which worksheet you're on, you have to adjust the sort on the imported data.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Maybe...


A
B
C
D
E
F
G
1
Name​
City​
Alabama​
Arizona​
California​
Colorado​
2
California​
Oakland​
Auburn​
Chandler​
Anaheim​
Aurora​
3
Alabama​
Montgomery​
Birmingham​
Gilbert​
Fresno​
Colorado Springs​
4
Arizona​
Scottsdale​
Dothan​
Glendale​
Long Beach​
Denver​
5
California​
Santa Ana​
Hoover​
Mesa​
Los Angeles​
Fort Collins​
6
California​
San Jose​
Huntsville​
Phoenix​
Oakland​
7
Arkansas​
Little Rock​
Mobile​
Scottsdale​
Riverside​
8
Alaska​
Juneau​
Montgomery​
Tuscon​
Sacramento​
9
Alabama​
Huntsville​
Tuscaloosa​
San Diego​
10
California​
Riverside​
San Francisco​
11
Arizona​
Phoenix​
San Jose​
12
Arizona​
Gilbert​
Santa Ana​
13
California​
Fresno​
14
Alabama​
Mobile​
15
Arizona​
Mesa​
16
California​
Los Angeles​
17
California​
Long Beach​
18
Arizona​
Chandler​
19
Alabama​
Birmingham​
20
Colorado​
Aurora​
21
Alabama​
Hoover​
22
Arizona​
Glendale​
23
Arizona​
Tuscon​
24
Alabama​
Tuscaloosa​
25
Arkansas​
Fort Smith​
26
Colorado​
Fort Collins​
27
Alaska​
Fairbanks​
28
Alabama​
Dothan​
29
Colorado​
Denver​
30
Colorado​
Colorado Springs​
31
California​
San Francisco​
32
California​
San Diego​
33
California​
Sacramento​
34
Alabama​
Auburn​
35
Alaska​
Anchorage​
36
California​
Anaheim​

Array formula in D2 copied across and down
=IFERROR(INDEX($B$2:$B$36,MATCH(0,IF($A$2:$A$36=D$1,COUNTIFS($A$2:$A$36,D$1,$B$2:$B$36,"<"&$B$2:$B$36)-SUM(COUNTIF(D$1:D1,IF($A$2:$A$36=D$1,$B$2:$B$36)))),0)),"")
Ctrl+Shift+Enter

M.
 

notruck

New Member
Joined
Jan 19, 2011
Messages
34
Mr. Branco, this solution works great if everything is on the same page although I'm having trouble getting it to work on separate pages and/or with Named Ranges. Any ideas?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
It seems to me only a problem of adjusting the ranges.
Could you show us a situation (data sample) where the formula did not work?

M.
 

notruck

New Member
Joined
Jan 19, 2011
Messages
34
Mr. Branco, I've found the err in my way. Your solution works perfectly. My problem was with the "COUNTIF" range and a "merged cell". Once I resolved that, I now have all named ranges and tables working great. Thanks A Lot ....
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,814
Messages
5,446,653
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top