"Sort by" Formula

[Melaccio]

Hi everyone,

I am tring to integrate in my formula some data more to make an automatic order in the cell but I don't have no clue how to solve it.

For example if I want to match two column with this formula =IF(ISERROR(VLOOKUP(C2,$A$2:$A$6,1,0)),"",C2), the result will be like this:

Table array 1 lookup value subject 1 subject 10 subject 2 subject 5 subject 5 subject 3 subject 9 subject 4 subject 1 subject 1 subject 5 subject 2 subject 2 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

However, if I want that the formula will give me an automatic order, like this:

<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

subject 5

subject 1

subject 2

<tbody>
</tbody>

What should I integrate in more?

Thank you in advance guys!

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​
1​	Table array 1​		lookup value​		List​
2​	subject 1​		subject 10​		subject 5​
3​	subject 2​		subject 5​		subject 1​
4​	subject 3​		subject 9​		subject 2 ​
5​	subject 4​		subject 1​
6​	subject 5​		subject 2​
7​

<tbody>
</tbody>

Formula in E2 copied down
=IFERROR(INDEX(C$2:C$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$2)+1)/ISNUMBER(MATCH(C$2:C$6,A$2:A$6,0)),ROWS(E$2:E2))),"")

M.

 

[Melaccio]

Maybe...

	A​	B​	C​	D​	E​
1​	Table array 1​		lookup value​		List​
2​	subject 1​		subject 10​		subject 5​
3​	subject 2​		subject 5​		subject 1​
4​	subject 3​		subject 9​		subject 2 ​
5​	subject 4​		subject 1​
6​	subject 5​		subject 2​
7​

<tbody>
</tbody>

Formula in E2 copied down
=IFERROR(INDEX(C$2:C$6,AGGREGATE(15,6,(ROW(C$2:C$6)-ROW(C$2)+1)/ISNUMBER(MATCH(C$2:C$6,A$2:A$6,0)),ROWS(E$2:E2))),"")

M.

Hi Branco,

Thank you for your quick answer.
The formula seems working but if I want to add a new table array, what should i do?

For example:

Table array 1		table array 2		lookup value
subject 1		subject f		subject n
subject 2		subject p		subject 5
subject 3		subject n		subject 9
subject 4		subject l		subject 1
subject 5		subject z		subject 2

<colgroup><col span="3"><col span="2"></colgroup><tbody>
</tbody>

I tried to add in the formula a second match like this:

=IFERROR(INDEX(E$2:E$6;AGGREGATE(15,6,(ROW(E$2:E$6)-ROW(E$2)+1)/MATCH(E$2:E$6,A$2:A$6,0),MATCH($E$2:$E$6,$C$2:$C$6,0)),ROWS(G$2:G2)),"")

but I receive as a result only the subject 1.

Do you have any clue?

Thank you again

 

[Marcelo Branco]

Expected results?

M.

 

[Melaccio]

Expected results?

M.

I expect that formula will match by ordering all the criteria of the column "vlookup" between the two table arrays.
So in my case it should be:

<style><!--table {mso-displayed-decimal-separator:"\,"; mso-displayed-thousand-separator:"\.";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

subject n

subject 5

subject 1

subject 2

<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

To match multiple table array I am using this formula:

=IF(ISERROR(VLOOKUP(E2,$A$2:$A$6,1,0)),IF(ISERROR(VLOOKUP(E2,$C$2:$C$6,1,0)),"",E2),E2)

but since you show me a better way how to make order with one table array I would like to know how to use this procedure with one table array more.

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​
1​	Table array 1​		table array 2​		lookup value​		List​
2​	subject 1​		subject f​		subject n​		subject n​
3​	subject 2​		subject p​		subject 5​		subject 5​
4​	subject 3​		subject n​		subject 9​		subject 1​
5​	subject 4​		subject l​		subject 1​		subject 2​
6​	subject 5​		subject z​		subject 2​
7​

Formula in G2 copied down
=IFERROR(INDEX(E$2:E$6,AGGREGATE(15,6,(ROW(E$2:E$6)-ROW(E$2)+1)/(ISNUMBER(MATCH(E$2:E$6,A$2:A$6,0))+ISNUMBER(MATCH(E$2:E$6,C$2:C$6,0))),ROWS(G$2:G2))),"")

M.

 

[Melaccio]

Try

	A​	B​	C​	D​	E​	F​	G​
1​	Table array 1​		table array 2​		lookup value​		List​
2​	subject 1​		subject f​		subject n​		subject n​
3​	subject 2​		subject p​		subject 5​		subject 5​
4​	subject 3​		subject n​		subject 9​		subject 1​
5​	subject 4​		subject l​		subject 1​		subject 2​
6​	subject 5​		subject z​		subject 2​
7​

<tbody>
</tbody>

Formula in G2 copied down
=IFERROR(INDEX(E$2:E$6,AGGREGATE(15,6,(ROW(E$2:E$6)-ROW(E$2)+1)/(ISNUMBER(MATCH(E$2:E$6,A$2:A$6,0))+ISNUMBER(MATCH(E$2:E$6,C$2:C$6,0))),ROWS(G$2:G2))),"")

M.

Thank you a lot Branco. The formula it's working perfect.
However when I am trying with long table array the formula is including the duplicate, for example in this case:

Table array 1		table array 2		lookup value	List
subject 1		subject f		subject n		subject n
subject 2		subject p		subject 5		subject 5
subject 3		subject n		subject w		subject 1
subject 4		subject l		subject 1		subject 2
subject 5		subject t		subject 2		subject e
subject 6		subject a		subject e		subject e
subject 7		subject b		subject e		subject 6
subject 8		subject c		subject 6		subject 4
subject 9		subject d		subject 4
subject 10		subject e		subject s
subject 11		subject e		subject w

<colgroup><col span="3"><col span="4"></colgroup><tbody>
</tbody>

Do you know how to complete the formula by excluding the duplicate?
From data-remove duplicate I can do it manually, but I am trying to make in automatic.

Thank you again for your help and sorry if I am annoying you.

 

[Marcelo Branco]

To make things easier I suggest you remove manually the duplicates in lookup value column. But if this is not possible, try...

	A​	B​	C​	D​	E​	F​	G​
1​	Table array 1​		table array 2​		lookup value​		List​
2​	subject 1​		subject f​		subject n​		subject n​
3​	subject 2​		subject p​		subject 5​		subject 5​
4​	subject 3​		subject n​		subject w​		subject 1​
5​	subject 4​		subject l​		subject 1​		subject 2​
6​	subject 5​		subject t​		subject 2​		subject e​
7​	subject 6​		subject a​		subject e​		subject 6​
8​	subject 7​		subject b​		subject e​		subject 4​
9​	subject 8​		subject c​		subject 6​
10​	subject 9​		subject d​		subject 4​
11​	subject 10​		subject e​		subject s​
12​	subject 11​		subject e​		subject w​

Formula in G2 copied down
=IFERROR(INDEX(E$2:E$12,AGGREGATE(15,6,(ROW(E$2:E$12)-ROW(E$2)+1)/(ISNA(MATCH(E$2:E$12,G$1:G1,0))*(ISNUMBER(MATCH(E$2:E$12,A$2:A$12,0))+ISNUMBER(MATCH(E$2:E$12,C$2:C$12,0)))),1)),"")

M.

 

[Melaccio]

To make things easier I suggest you remove manually the duplicates in lookup value column. But if this is not possible, try...

	A​	B​	C​	D​	E​	F​	G​
1​	Table array 1​		table array 2​		lookup value​		List​
2​	subject 1​		subject f​		subject n​		subject n​
3​	subject 2​		subject p​		subject 5​		subject 5​
4​	subject 3​		subject n​		subject w​		subject 1​
5​	subject 4​		subject l​		subject 1​		subject 2​
6​	subject 5​		subject t​		subject 2​		subject e​
7​	subject 6​		subject a​		subject e​		subject 6​
8​	subject 7​		subject b​		subject e​		subject 4​
9​	subject 8​		subject c​		subject 6​
10​	subject 9​		subject d​		subject 4​
11​	subject 10​		subject e​		subject s​
12​	subject 11​		subject e​		subject w​

<tbody>
</tbody>

Formula in G2 copied down
=IFERROR(INDEX(E$2:E$12,AGGREGATE(15,6,(ROW(E$2:E$12)-ROW(E$2)+1)/(ISNA(MATCH(E$2:E$12,G$1:G1,0))*(ISNUMBER(MATCH(E$2:E$12,A$2:A$12,0))+ISNUMBER(MATCH(E$2:E$12,C$2:C$12,0)))),1)),"")

M.

Hi Branco,

Thank you again for your answers.
I tried this formula but it's not working.
I also put each column in different sheet:

Sheet1: Table array 1
Sheet2: Table array 2
Sheet3: Lookup value
Sheet4: List

I use the formula that you show me by changing the criteria and put all the column since I need all the column A for all sheet but excluding the A1 cell.
The formula it's like this:

=IFERROR(INDEX(Sheet3!$A:$A,AGGREGATE(15,6,(ROW(Sheet3!$A:$A) ROW(Sheet3!$A$2)+1)/(ISNUMBER(MATCH(Sheet3!$A:$A,Sheet1!$A:$A,0))+ISNUMBER(MATCH(Sheet3!$A:$A,Sheet2!$A:$A,0))),ROWS(Sheet3!A1))),"")

The results it's only lookup value for all the column A in the Sheet4.
Do you have any ideas?

 

[Marcelo Branco]

You are changing the criteria, the location of the data and where the results should be shown many times. Difficult to understand exactly what you really want.

Please, try to show exactly where are the data (worksheet, rows and columns) and we should avoid using references to entire columns, like A: A, for the sake of performance.

M.