How to copy and sort a range of names from a list

[Jeddo]

I have a data base with names and I am needing to find all the names within a range (H - L for example), copy them to another range and sort them in alphabetical order. Novice with Excel. Tried doing a search with the greater than and lesser than signs, but cna't get the syntax right or even the formula right. I have mastered copying everything and sorting it alphabetically in a new range, just can't figure out how to pull out a range of names

Alpha		Hotel
Tango		India
Hotel		Juliet
Oscar		Kilo
Whiskey		Lima
Bravo
Juliet
Quebec
X-Ray
Mike
Foxtrot
Lima
Zulu
Charlie
Kilo
Romeo
Victor
Delta
India
Papa
Uniform
Yankee
Echo
Golf
November
Sierra

[/RANGE

 

[johnnyL]

@Jeddo you have displayed 3 different columns, but you have asked for a 5 column range in your question.

Exactly what columns do you want sorted? Do you want each of them sorted independently?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jeddo]

@Jeddo you have displayed 3 different columns, but you have asked for a 5 column range in your question.

Exactly what columns do you want sorted? Do you want each of them sorted independently?

My apologies. Should have done a lot better job with my example. Still trying to learn. My ultimate goal is to separate column A into four groups; A-G, H-L, M-R and S-Z, into four different ranges. Also, each name will have data with it, such as first name and address. I was hoping if I can get the formula to find and sort one group, I could figure out the rest myself. I redid the example to give a better idea.

			A-G			H-L			M-R			S-Z
Last Name	First Name	Address	Last Name	First Name	Address	Last Name	First Name	Address	Last Name	First Name	Address	Last Name	First Name	Address
Alpha	James	Austin	Alpha	James	Austin	Hotel	Suzy	Houston	Mike	Lee	Cut n shoot	Sierra	Rex	Hutto
Tango	Karen	Dallas	Bravo	Ricky	Waco	India	Carol	Bryan	November	Dustin	Flatonia	Tango	Karen	Dallas
Hotel	Suzy	Houston	Charlie	John	Thorndale	Juliet	Ann	Fort Worth	Oscar	Ken	Cistern	Uniform	Sam	Caldwell
Oscar	Ken	Cistern	Delta	Jimmy	Beaumont	Kilo	Charles	Cotulla	Papa	Kimberly	Cameron	Victor	Robbie	Galveston
Whiskey	Benny	San Antonio	Echo	Luke	Schulenberg	Lima	Paul	Rowlett	Quebec	Amy	Abilene	Whiskey	Benny	San Antonio
Bravo	Ricky	Waco	Foxtrot	Mike	Paris				Romeo	Matthew	Laredo	X-Ray	Adam	El Paso
Juliet	Ann	Fort Worth	Golf	Ron	Shiner							Yankee	Mark	La Grange
Quebec	Amy	Abilene										Zulu	Dave	Round Rock
X-Ray	Adam	El Paso
Mike	Lee	Cut n shoot
Foxtrot	Mike	Paris
Lima	Paul	Rowlett
Zulu	Dave	Round rock
Charlie	John	Thorndale
Kilo	Charles	Cotulla
Romeo	Matthew	Laredo
Victor	Robbie	Galveston
Delta	Jimmy	Beaumont
India	Carol	Bryan
Papa	Kimberly	Cameron
Uniform	Sam	Caldwell
Yankee	Mark	La Grange
Echo	Luke	Schulenberg
Golf	Ron	Shiner
November	Dustin	Flatonia
Sierra	Rex	Hutto

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Perhaps you missed Peter's comments/request here.
Some of the newer versions of Excel have some pretty cool new functions that can be used, but we do not want to suggest formulas that you cannot use.
So if you can update your account details to show us what version of Excel you are using, that would be most helpful!

 

[Jeddo]

Perhaps you missed Peter's comments/request here.
Some of the newer versions of Excel have some pretty cool new functions that can be used, but we do not want to suggest formulas that you cannot use.
So if you can update your account details to show us what version of Excel you are using, that would be most helpful!

Ugh! Got busy making a new example and forgot all about that. Updated my account details. Would only let me enter year of the version I'm using. Using Office Professional Plus 2019

 

[Joe4]

I have mastered copying everything and sorting it alphabetically in a new range, just can't figure out how to pull out a range of names

You mention that you got the copy/sort piece working.
Can you tell us how you are doing that now, and maybe we can see if we can help you build on that?

Also, are you opposed to a VBA solution, if we were to offer that?
And do you have any issue if we re-sort the original list alphabetically, or does it need to stay the way that it is?

 

[Fluff]

A formula approach, if your happy with a helper column (which can always be hidden)

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					A-G			H-L			M-R			S-Z
2	Last Name	First Name	Address		Last Name	First Name	Address	Last Name	First Name	Address	Last Name	First Name	Address	Last Name	First Name	Address
3	Alpha	James	Austin	1	Alpha	James	Austin	Hotel	Suzy	Houston	Mike	Lee	Cut n shoot	Sierra	Rex	Hutto
4	Tango	Karen	Dallas	20	Bravo	Ricky	Waco	India	Carol	Bryan	November	Dustin	Flatonia	Tango	Karen	Dallas
5	Hotel	Suzy	Houston	8	Charlie	John	Thorndale	Juliet	Ann	Fort Worth	Oscar	Ken	Cistern	Uniform	Sam	Caldwell
6	Oscar	Ken	Cistern	15	Delta	Jimmy	Beaumont	Kilo	Charles	Cotulla	Papa	Kimberly	Cameron	Victor	Robbie	Galveston
7	Whiskey	Benny	San Antonio	23	Echo	Luke	Schulenberg	Lima	Paul	Rowlett	Quebec	Amy	Abilene	Whiskey	Benny	San Antonio
8	Bravo	Ricky	Waco	2	Foxtrot	Mike	Paris				Romeo	Matthew	Laredo	X-Ray	Adam	El Paso
9	Juliet	Ann	Fort Worth	10	Golf	Ron	Shiner							Yankee	Mark	La Grange
10	Quebec	Amy	Abilene	17										Zulu	Dave	Round rock
11	X-Ray	Adam	El Paso	24
12	Mike	Lee	Cut n shoot	13
13	Foxtrot	Mike	Paris	6
14	Lima	Paul	Rowlett	12
15	Zulu	Dave	Round rock	26
16	Charlie	John	Thorndale	3
17	Kilo	Charles	Cotulla	11
18	Romeo	Matthew	Laredo	18
19	Victor	Robbie	Galveston	22
20	Delta	Jimmy	Beaumont	4
21	India	Carol	Bryan	9
22	Papa	Kimberly	Cameron	16
23	Uniform	Sam	Caldwell	21
24	Yankee	Mark	La Grange	25
25	Echo	Luke	Schulenberg	5
26	Golf	Ron	Shiner	7
27	November	Dustin	Flatonia	14
28	Sierra	Rex	Hutto	19
29
Main

Cell Formulas
Range	Formula
E3	E3	=IFERROR(INDEX($A$3:$A$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/(LEFT($A$3:$A$28)<="G")/($D$3:$D$28=ROWS(E$3:E3)),1)),"")
F3:G20	F3	=IF($E3="","",INDEX(B$3:B$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/($A$3:$A$28=$E3),COUNTIFS($E$3:$E3,$E3))))
H3:H20	H3	=IFERROR(INDEX($A$3:$A$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/(LEFT($A$3:$A$28)<="L")/(LEFT($A$3:$A$28)>="H")/($D$3:$D$28=ROWS(H$3:H3)+COUNTIFS($A$3:$A$28,"<H*")),1)),"")
I3:J20	I3	=IF($H3="","",INDEX(B$3:B$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/($A$3:$A$28=$H3),COUNTIFS($H$3:$H3,$H3))))
K3:K20	K3	=IFERROR(INDEX($A$3:$A$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/(LEFT($A$3:$A$28)<="r")/(LEFT($A$3:$A$28)>="m")/($D$3:$D$28=ROWS(H$3:H3)+COUNTIFS($A$3:$A$28,"<m*")),1)),"")
L3:M20	L3	=IF($K3="","",INDEX(B$3:B$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/($A$3:$A$28=$K3),COUNTIFS($K$3:$K3,$K3))))
N3:N20	N3	=IFERROR(INDEX($A$3:$A$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/(LEFT($A$3:$A$28)>="S")/($D$3:$D$28=ROWS(H$3:H3)+COUNTIFS($A$3:$A$28,"<s*")),1)),"")
O3:P20	O3	=IF($N3="","",INDEX(B$3:B$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/($A$3:$A$28=$N3),COUNTIFS($N$3:$N3,$N3))))
E4:E20	E4	=IFERROR(INDEX($A$3:$A$28,AGGREGATE(15,6,(ROW($A$3:$A$28)-ROW($A$3)+1)/(LEFT($A$3:$A$28)<="G")/(COUNTIFS($A$3:$A$28,"<"&$A$3:$A$28)=SMALL(COUNTIFS($A$3:$A$28,"<"&$A$3:$A$28),ROWS(E$3:E4))),1)),"")
D3:D28	D3	=COUNTIFS($A$3:$A$28,"<"&A3)+1

 

[Jeddo]

You mention that you got the copy/sort piece working.
Can you tell us how you are doing that now, and maybe we can see if we can help you build on that?

Also, are you opposed to a VBA solution, if we were to offer that?
And do you have any issue if we re-sort the original list alphabetically, or does it need to stay the way that it is?

A VBA solution would be perfect. It's what I have been building this on, but first time and slow learner. Realized what I needed was basically a program for a phone or address book, found a YouTube video on how to, and have been building on that as I learn. This is the Macro I'm using to copy the original list to another range and sort it alphabetically. Just haven't figured out how to break it up into groups from there.

Sub Copy_Last_Name_3()
'
' Copy_Last_Name Macro
'
Sheets("phonelist").Select
Range("BQ8:BW233").Select
Selection.Copy
Range("CA8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("CB8").Select
ActiveWorkbook.Worksheets("phonelist").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("phonelist").Sort.SortFields.Add Key:=Range( _
"CB9:CB233"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("phonelist").Sort
.SetRange Range("CB8:CG233")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("BT8").Select
End Sub



I have been able to use a search where I can get a group using the < or > sign before a letter. But I can't use both to get the groups in the middle. Here is that formula


Private Sub cmdContact_Click()

On Error GoTo errhandler:

Sheet1.Unprotect

Set DataSH = Sheet1

DataSH.Range("W8") = Me.cboSelect.Value
DataSH.Range("W9") = Me.txtSearch.Text

DataSH.Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("phonelist!Criteria"), CopyToRange:=Range("phonelist!Extract"), Unique:=False

ListBox1.RowSource = Sheet1.Range("outdata").Address(External:=True)

Exit Sub
errhandler:
MsgBox "No Data Found. Please Try Again"

Sheet1.Protect

End Sub


I know some things aren't labeled correctly, but it what I learned from the video and I'm still trying to learn the proper syntax. Thanks for any help.

 

[Joe4]

Does Fluff's formula-based reply work for you, or would you still like to pursue a VBA option?

If VBA, please answer this earlier question I posted:

And do you have any issue if we re-sort the original list alphabetically, or does it need to stay the way that it is?

It will become much easier to do if we are able to re-sort the original list.