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!

 

[dermie_72]

See if this helps:
finding the nth match on an index match

 

[alansidman]

A power query means to the end

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    MQ = Table.NestedJoin(T2, {"LAST NAME"}, T1, {"LAST NAME"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"FIRST NAME", "LAST NAME", "ID NUMBER", "E-MAIL"}, {"FIRST NAME", "LAST NAME.1", "ID NUMBER", "E-MAIL"}),
    RC = Table.RemoveColumns(ET,{"LAST NAME"}),
    RD = Table.Distinct(RC, {"FIRST NAME", "LAST NAME.1"})

in
    RD

 

[Alex Blakenburg]

Since you are already using Filter & Sort the output is going to be a dynamic array, so why not keep going with that approach ? See below.

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	Sarah	234567891	Walker.Sarah@Gmail.Com
7	West	Andrew	567891234	West@Hotmail.Com
Sheet2

Cell Formulas
Range	Formula
A2:D7	A2	=PROPER(CHOOSECOLS(SORTBY(FILTER(Sheet1!A2:D7,Sheet1!B2:B7<>""),Sheet1!B2:B7,1,Sheet1!A2:A7,1),2,1,3,4))
Dynamic array formulas.

 

[RonOliver]

Since you are already using Filter & Sort the output is going to be a dynamic array, so why not keep going with that approach ? See below.

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	Sarah	234567891	Walker.Sarah@Gmail.Com
7	West	Andrew	567891234	West@Hotmail.Com
Sheet2

Cell Formulas
Range	Formula
A2:D7	A2	=PROPER(CHOOSECOLS(SORTBY(FILTER(Sheet1!A2:D7,Sheet1!B2:B7<>""),Sheet1!B2:B7,1,Sheet1!A2:A7,1),2,1,3,4))
Dynamic array formulas.

Tried this but it doesn't work. Also, I'd like some cols to be UPPER() and some others to be LOWER(). There might also be gaps in the table (someone's e-mail missing, for example), so I don't know if that approach would work. Thanks anyway!

 

[Alex Blakenburg]

You didn't say it doesn't work because you want different columns treated differently, so what does doesn't work mean ?
It worked fine on your sample.

 

[RonOliver]

A power query means to the end

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    MQ = Table.NestedJoin(T2, {"LAST NAME"}, T1, {"LAST NAME"}, "Table2", JoinKind.FullOuter),
    ET = Table.ExpandTableColumn(MQ, "Table2", {"FIRST NAME", "LAST NAME", "ID NUMBER", "E-MAIL"}, {"FIRST NAME", "LAST NAME.1", "ID NUMBER", "E-MAIL"}),
    RC = Table.RemoveColumns(ET,{"LAST NAME"}),
    RD = Table.Distinct(RC, {"FIRST NAME", "LAST NAME.1"})

in
    RD

I'm not familiar with power queries. Will this work on Excel online?

 

[RonOliver]

You didn't say it doesn't work because you want different columns treated differently, so what does doesn't work mean ?
It worked fine on your sample.

I tried copying and pasting, trying to make it fit, but it returned a #VALUE! error for some reason. I don't know why.

I guess, because some columns need to have a different behavior, I need to use different formulas for each column, which was my original approach. I'm afraid that's something I didn't say at the beginning, because I didn't realize it was very important. Having gaps (missing e-mail, missing ID, etc.) is only going to make things more complicated when sorting, but I'm not very well versed in Excel, so I don't know.

 

[Alex Blakenburg]

Handling one issue at a time does it work if you use this:

=PROPER(CHOOSECOLS(FILTER(SORTBY(Sheet1!A2:D7,Sheet1!B2:B7,1,Sheet1!A2:A7,1),Sheet1!B2:B7<>""),2,1,3,4))

 

[RonOliver]

Handling one issue at a time does it work if you use this:

=PROPER(CHOOSECOLS(FILTER(SORTBY(Sheet1!A2:D7,Sheet1!B2:B7,1,Sheet1!A2:A7,1),Sheet1!B2:B7<>""),2,1,3,4))

This would work if none of the cells were blank, but there might be blank cells.