Index, Match, & Sort

[notruck]

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?

Excel 2010
	A	B	C	D	E	F	G
1	Name	City		Alabama	Arizona	California	Colorado
2	California	Oakland		Montgomery	Scottsdale	Oakland	Aurora
3	Alabama	Montgomery		Huntsville	Phoenix	Santa Ana	Fort Collins
4	Arizona	Scottsdale		Mobile	Gilbert	San Jose	Denver
5	California	Santa Ana		Birmingham	Mesa	Riverside	Colorado Springs
6	California	San Jose		Hoover	Chandler	Fresno
7	Arkansas	Little Rock		Tuscaloosa	Glendale	Los Angeles
8	Alaska	Juneau		Dothan	Tuscon	Long Beach
9	Alabama	Huntsville		Auburn		San Francisco
10	California	Riverside				San Diego
11	Arizona	Phoenix				Sacramento
12	Arizona	Gilbert				Anaheim
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
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($B$2:$B$36,SMALL(IF($A$2:$A$36=D$1,ROW($B$2:$B$36)-MIN(ROW($B$2:$B$36))+1),ROWS($D$4:D4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks for your help …

 

[AlphaFrog]

Couldn't you just alphabetically sort the imported data in columns A and B?

 

[notruck]

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]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

City​

[/td][td][/td][td]

Alabama​

[/td][td]

Arizona​

[/td][td]

California​

[/td][td]

Colorado​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

California​

[/td][td]

Oakland​

[/td][td][/td][td]

Auburn​

[/td][td]

Chandler​

[/td][td]

Anaheim​

[/td][td]

Aurora​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Alabama​

[/td][td]

Montgomery​

[/td][td][/td][td]

Birmingham​

[/td][td]

Gilbert​

[/td][td]

Fresno​

[/td][td]

Colorado Springs​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Arizona​

[/td][td]

Scottsdale​

[/td][td][/td][td]

Dothan​

[/td][td]

Glendale​

[/td][td]

Long Beach​

[/td][td]

Denver​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

California​

[/td][td]

Santa Ana​

[/td][td][/td][td]

Hoover​

[/td][td]

Mesa​

[/td][td]

Los Angeles​

[/td][td]

Fort Collins​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

California​

[/td][td]

San Jose​

[/td][td][/td][td]

Huntsville​

[/td][td]

Phoenix​

[/td][td]

Oakland​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Arkansas​

[/td][td]

Little Rock​

[/td][td][/td][td]

Mobile​

[/td][td]

Scottsdale​

[/td][td]

Riverside​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Alaska​

[/td][td]

Juneau​

[/td][td][/td][td]

Montgomery​

[/td][td]

Tuscon​

[/td][td]

Sacramento​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Alabama​

[/td][td]

Huntsville​

[/td][td][/td][td]

Tuscaloosa​

[/td][td][/td][td]

San Diego​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

California​

[/td][td]

Riverside​

[/td][td][/td][td][/td][td][/td][td]

San Francisco​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Arizona​

[/td][td]

Phoenix​

[/td][td][/td][td][/td][td][/td][td]

San Jose​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Arizona​

[/td][td]

Gilbert​

[/td][td][/td][td][/td][td][/td][td]

Santa Ana​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

California​

[/td][td]

Fresno​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

Alabama​

[/td][td]

Mobile​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

Arizona​

[/td][td]

Mesa​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

California​

[/td][td]

Los Angeles​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

California​

[/td][td]

Long Beach​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

Arizona​

[/td][td]

Chandler​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td]

Alabama​

[/td][td]

Birmingham​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td]

Colorado​

[/td][td]

Aurora​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td]

Alabama​

[/td][td]

Hoover​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

22​

[/td][td]

Arizona​

[/td][td]

Glendale​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

23​

[/td][td]

Arizona​

[/td][td]

Tuscon​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

24​

[/td][td]

Alabama​

[/td][td]

Tuscaloosa​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

25​

[/td][td]

Arkansas​

[/td][td]

Fort Smith​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

26​

[/td][td]

Colorado​

[/td][td]

Fort Collins​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

27​

[/td][td]

Alaska​

[/td][td]

Fairbanks​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

28​

[/td][td]

Alabama​

[/td][td]

Dothan​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

29​

[/td][td]

Colorado​

[/td][td]

Denver​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

30​

[/td][td]

Colorado​

[/td][td]

Colorado Springs​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

31​

[/td][td]

California​

[/td][td]

San Francisco​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

32​

[/td][td]

California​

[/td][td]

San Diego​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

33​

[/td][td]

California​

[/td][td]

Sacramento​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

34​

[/td][td]

Alabama​

[/td][td]

Auburn​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

35​

[/td][td]

Alaska​

[/td][td]

Anchorage​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

36​

[/td][td]

California​

[/td][td]

Anaheim​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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]

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]

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]

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 ....

 

[Marcelo Branco]

You're welcome. Glad to help.

M.