Question description:
This user has given permission to use the problem statement for this
blog.
Hi. I have a random list of names. Last name in one column, First name in another column. I need to start another sheet that will reference this random list and return an alphabetized list.
Solved by M. Y. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
01/12/2017 - 07:50
Hello :)
User
01/12/2017 - 07:50
Hi There
Excelchat Expert
01/12/2017 - 07:51
You would like to lookup the names in Sheet1 and return the names in Sheet2 alphabetized?
User
01/12/2017 - 07:51
CORRECGT
Excelchat Expert
01/12/2017 - 07:51
Or is there information that goes with the names also?
User
01/12/2017 - 07:51
...correct
Excelchat Expert
01/12/2017 - 07:51
Is there information that goes with the names?
User
01/12/2017 - 07:51
there are several columns worth of information that goes with the names
Excelchat Expert
01/12/2017 - 07:52
Okay, this will require VBA.
Excelchat Expert
01/12/2017 - 07:52
if you want it automated that is.
User
01/12/2017 - 07:52
If there is an easy way to incorporate the return of all info than no problem. Otherwise I'll just create a vlookup for the other columns
Excelchat Expert
01/12/2017 - 07:52
I can give you steps on how to manually alphabtize.
Excelchat Expert
01/12/2017 - 07:52
So the list doesn't need Alphabetized?
Excelchat Expert
01/12/2017 - 07:52
If I can do a lookup without it?
User
01/12/2017 - 07:53
my aim is to have the "results" sheet return an alphabatized list from a random list
Excelchat Expert
01/12/2017 - 07:53
Okay, then yes, this will require VBA.
User
01/12/2017 - 07:53
I'm fine with it just referencing a single column and returning a single column if that's easiest
Excelchat Expert
01/12/2017 - 07:53
First, I will show you the index/match = lookup function.
User
01/12/2017 - 07:53
I"m not familiar with a VBA
Excelchat Expert
01/12/2017 - 07:54
Then, we will need to discuss the VBA piece.
User
01/12/2017 - 07:54
souncs good
Excelchat Expert
01/12/2017 - 07:54
For that, I will ask that you repost for the VBA session.
User
01/12/2017 - 07:54
*sounds
Excelchat Expert
01/12/2017 - 07:54
Once I finish with the lookup piece that is.
Excelchat Expert
01/12/2017 - 07:54
Okay, so do you know index match?
User
01/12/2017 - 07:54
great.
User
01/12/2017 - 07:54
i do not know index match
Excelchat Expert
01/12/2017 - 07:55
No worries. I will show you :)
Excelchat Expert
01/12/2017 - 07:56
Okay, can you see the excel to the right?
User
01/12/2017 - 07:57
i can
Excelchat Expert
01/12/2017 - 07:58
Great, I created sheet2 tab. All the data will soon be transfered to Sheet2 using index/match.
User
01/12/2017 - 07:58
copy that
User
01/12/2017 - 07:58
...to sheet 1?
Excelchat Expert
01/12/2017 - 07:59
Sheet 1 to Sheet 2.
Excelchat Expert
01/12/2017 - 07:59
I noticed that the names were revered.
Excelchat Expert
01/12/2017 - 07:59
reversed.
Excelchat Expert
01/12/2017 - 07:59
I have since adjusted them.
User
01/12/2017 - 07:59
ah, okay. i see that
Excelchat Expert
01/12/2017 - 08:01
This is comlpete.
Excelchat Expert
01/12/2017 - 08:01
Please see Sheet 2.
Excelchat Expert
01/12/2017 - 08:01
=index('Sheet 1'!$B$2:$C$11,match($A2,'Sheet 1'!$A$2:$A$11,0),match(B$1,'Sheet 1'!$B$1:$C$1,0))
Excelchat Expert
01/12/2017 - 08:01
That is the formula.
User
01/12/2017 - 08:03
that's great. Thanks!
User
01/12/2017 - 08:03
I'm trying it now
Excelchat Expert
01/12/2017 - 08:03
Okay, sounds good.
Excelchat Expert
01/12/2017 - 08:06
How's it working?
User
01/12/2017 - 08:06
If I want tohis to look at the full column can I substitutel $B$2:$C$11 with $A:$C?
Excelchat Expert
01/12/2017 - 08:06
Yes.
Excelchat Expert
01/12/2017 - 08:07
This was just a quick example.
User
01/12/2017 - 08:07
ADo you know, I this might be a problem
Excelchat Expert
01/12/2017 - 08:07
but you can very well lock the column and lookup all data.
Excelchat Expert
01/12/2017 - 08:07
What is that?
User
01/12/2017 - 08:07
i have a blank sheet that I'm trying to get to return a list in alphabetical order
User
01/12/2017 - 08:07
so it's not really referencing a second list
Excelchat Expert
01/12/2017 - 08:08
Okay, not's not a problem, I can work around that.
Excelchat Expert
01/12/2017 - 08:09
Now, it is pulling the reference name from Sheet 1.
User
01/12/2017 - 08:09
but it is not alphabatizing the list
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
