Approach to sorting and index/match

[RonOliver]

Hi.
Let me paste these two sheets:

Sheet1...

Book1
	A	B	C	D
1	FIRST NAME	LAST NAME	ID NUMBER	E-MAIL
2	Michael	Walker	123456789	mike@gmail.com
3	Sarah	Walker	234567891	walker.sarah@gmail.com
4	John	Timmons	345678912	john@gmail.com
5	Ashley	Bjornsdottir	456789123	bjorn@gmail.com
6	Andrew	West	567891234	west@hotmail.com
7	Les	Johnson	678912345	johnson@hotmail.com
Sheet1

and Sheet2...

Book1
	A	B	C	D
1	LAST NAME	FIRST NAME	DATE OF BIRTH	E-MAIL
2	Bjornsdottir	Ashley	456789123	Bjorn@Gmail.Com
3	Johnson	Les	678912345	Johnson@Hotmail.Com
4	Timmons	John	345678912	John@Gmail.Com
5	Walker	Michael	123456789	Mike@Gmail.Com
6	Walker	Michael	123456789	Mike@Gmail.Com
7	West	Andrew	567891234	West@Hotmail.Com
Sheet2

Cell Formulas
Range	Formula
A2:A7	A2	=IF(AND(ISBLANK(Sheet1!A2:A7)),"",PROPER(SORT(FILTER(Sheet1!B2:B7,Sheet1!B2:B7<>""))))
B2:B7	B2	=IFERROR(PROPER(INDEX(Sheet1!$A$2:$A$7,MATCH(A2,Sheet1!$B$2:$B$7,0))),"")
C2:C7	C2	=IFERROR(PROPER(INDEX(Sheet1!$C$2:$C$7,MATCH(A2,Sheet1!$B$2:$B$7,0))),"")
D2:D7	D2	=IFERROR(PROPER(INDEX(Sheet1!$D$2:$D$7,MATCH(A2,Sheet1!$B$2:$B$7,0))),"")
Dynamic array formulas.

The second swaps first and last name and sorts everything by last name. However, by using index/match, if two people have the same last name, I'm only getting the first instance where it matches. Can you please let me know if you would try a different approach? This is a reduced version of my sheet, which has a lot more columns, so not all approaches will work. I'm just asking if there's something I'm doing fundamentally wrong.

Thanks everyone!

 

[Alex Blakenburg]

Show us the results you are getting and what it should look like.

 

[RonOliver]

Oh, nevermind. I had miscalculated the range. It works now. Sorry for the hassle.

Thanks so much! I love the formula, but now I don't know how to use UPPER(), LOWER(), etc. in different columns. There's also a column that should display a date of birth, but it displays it as a five digit number. Tried changing the cell format, but I can't get it to work. I might need helper columns, but that's gonna require a complete overhaul.

 

[RonOliver]

Show us the results you are getting and what it should look like.

For example, the e-mail column should always appear in LOWER(), the ID in UPPER() (in case there are letters in the ID number), etc.

 

[alansidman]

Use Power Query in Excel for the Web - Microsoft Support

import, refresh, authenticate, and manage data sources, and shape data into a table that fits your requirements.

support.microsoft.com

 

[Alex Blakenburg]

If you don't want to go the Power Query route, which is something that would be well worth your time to learn, then something like this might work for you.

=LET(fltr,FILTER(SORTBY(Sheet1!A2:D7,Sheet1!B2:B7,1,Sheet1!A2:A7,1),Sheet1!B2:B7<>""),
     fName,PROPER(CHOOSECOLS(fltr,1)),
     lName,PROPER(CHOOSECOLS(fltr,2)),
     ID,UPPER(CHOOSECOLS(fltr,3)),
     eMail,LOWER(CHOOSECOLS(fltr,4)),
     HSTACK(lName, fName, ID, eMail))

 

[RonOliver]

If you don't want to go the Power Query route, which is something that would be well worth your time to learn, then something like this might work for you.

=LET(fltr,FILTER(SORTBY(Sheet1!A2:D7,Sheet1!B2:B7,1,Sheet1!A2:A7,1),Sheet1!B2:B7<>""),
     fName,PROPER(CHOOSECOLS(fltr,1)),
     lName,PROPER(CHOOSECOLS(fltr,2)),
     ID,UPPER(CHOOSECOLS(fltr,3)),
     eMail,LOWER(CHOOSECOLS(fltr,4)),
     HSTACK(lName, fName, ID, eMail))

Thank you from the bottom of my heart. This is exactly the solution I was looking for.

I guess I have no other choice but to learn Power Query next time.

 

[Alex Blakenburg]

You're welcome. Glad we could help.