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?



Excel 2010
ABCDEFG
1NameCityAlabamaArizonaCaliforniaColorado
2CaliforniaOaklandMontgomeryScottsdaleOaklandAurora
3AlabamaMontgomeryHuntsvillePhoenixSanta AnaFort Collins
4ArizonaScottsdaleMobileGilbertSan JoseDenver
5CaliforniaSanta AnaBirminghamMesaRiversideColorado Springs
6CaliforniaSan JoseHooverChandlerFresno
7ArkansasLittle RockTuscaloosaGlendaleLos Angeles
8AlaskaJuneauDothanTusconLong Beach
9AlabamaHuntsvilleAuburnSan Francisco
10CaliforniaRiversideSan Diego
11ArizonaPhoenixSacramento
12ArizonaGilbertAnaheim
13CaliforniaFresno
14AlabamaMobile
15ArizonaMesa
16CaliforniaLos Angeles
17CaliforniaLong Beach
18ArizonaChandler
19AlabamaBirmingham
20ColoradoAurora
21AlabamaHoover
22ArizonaGlendale
23ArizonaTuscon
24AlabamaTuscaloosa
25ArkansasFort Smith
26ColoradoFort Collins
27AlaskaFairbanks
28AlabamaDothan
29ColoradoDenver
30ColoradoColorado Springs
31CaliforniaSan Francisco
32CaliforniaSan Diego
33CaliforniaSacramento
34AlabamaAuburn
35AlaskaAnchorage
36CaliforniaAnaheim
Sheet1
Cell Formulas
RangeFormula
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 …
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top