I need help sorting my list on my dashboard!

[mcalice]

The following formula works to pull the name I want from a large list of various client names.


IF($AH3="0",IF($AH3>0,IF(O3>=$AJ$15,IF($AG3=0,INDEX($P4,MATCH($AJ$10,$T$3:$T$500,0)),""),""),""),"")


My problem is that the list comes into my Dashboard on the same Row number as it is on the original list.

I need my dashboard to have the names sorted in alphabetical order starting at the first row.


Any help would be greatly appreciated!

 

[Luke M]

First, for readability at least, the formula reduces to:

=IF(AND($AH3>=0,$O3>$AJ$15,$AG3=0),INDEX($P4,MATCH($J$10,$T$3:$T$500,0)),"")


This is a bad formula, as the first arguement in your INDEX function should be an array, not a single cell, so I'm not quite sure what is the output. Overall, it seems like you're getting a list of things that meet a criteria, but they're out of order, and you have blanks. You might try looking here:

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


For examples on how to sort the list. Does that help?

 

[mcalice]

This formula pulls in a client name, from column P if all of the "IF" statements are true.

One of the IF statements refers to the Account Manager's name.


Once this client name is pulled into the appropriate place on my dashboard, the following columns are completed using a VLookup.


I'm trying to figure out how to pull in, and sort, only those client names that belong to the specific account manager that I'm reporting on. But as you mentioned, I can't get them to sort.


I've tried the link your provided, and I can sort the client names, the problem is, when I apply the formula to pull only the clients I need on the dashboard for a particular Account Manager, I've got the gaps again. I just can't seem to figure out how to pull only certain client names out of the entire list and have them fill in line by line.

 

[mcalice]

and by the way, thank you for pointing out how to reduce the formula! That is very helpful, and as you mentioned, much easier to read!

 

[Luke M]

Can you use your formula to first create a list of desired names plus blanks, and then use the sorting formula (somewhere else) to sort the data correctly? I don't think you can do both steps in 1 single formula w/o using some VB.

 

[mcalice]

Just wanted to report back that I found a solution that worked for what I needed! At least most of the way! Sometimes

----

Anyway it should be possible to solve your problem using MATCH and INDEX.

These two functions can be combined to produce a rather more versatile version of the function VLOOKUP.

Assuming your data is in Sheet1 and you are moving it to Sheet2

Sheet2!D1 : enter a particle number.

Sheet2!E1 : =MATCH(D$1,Sheet1!A:A,0)

Sheet2!E2 : =MATCH(D$1,OFFSET(Sheet1!A$1,E1,,10000),0)+E1

Replicate E2 down.

Sheet2!A1 : =INDEX(Sheet1!A:A,$E1)

Replicate across to column C and then down as required.


thanks to Mark at this link I ran across while searching the web for more possible solutions...

http://www.excelforum.com/excel-general/613246-separate-data-into-columns-based-on-the-value-of-one-column.html


----


I'm now working on the second half of my problem, which will be to pull an individuals entire "Team" information in as well as the individuals.

This is very challenging, but super exciting to find answers!


Thanks to all the great Excel users out there that are willing to share their knowledge!

 

[mcalice]

Just wanted to report back that I found a solution that worked for what I needed! At least most of the way!

----

Anyway it should be possible to solve your problem using MATCH and INDEX.

These two functions can be combined to produce a rather more versatile version of the function VLOOKUP.

Assuming your data is in Sheet1 and you are moving it to Sheet2

Sheet2!D1 : enter a particle number.

Sheet2!E1 : =MATCH(D$1,Sheet1!A:A,0)

Sheet2!E2 : =MATCH(D$1,OFFSET(Sheet1!A$1,E1,,10000),0)+E1

Replicate E2 down.

Sheet2!A1 : =INDEX(Sheet1!A:A,$E1)

Replicate across to column C and then down as required.


thanks to Mark at this post...

http://www.excelforum.com/excel-general/613246-separate-data-into-columns-based-on-the-value-of-one-column.html


----


I'm now working on the second half of my problem, which will be to pull an individuals entire "Team" information in as well as the individuals.

This is very challenging, but super exciting to find answers!


Thanks to all the great Excel users out there that are willing to share their knowledge!

 

[mcalice]

Just wanted to report back that I found a solution that worked for what I needed! At least most of the way!

----

Anyway it should be possible to solve your problem using MATCH and INDEX.

These two functions can be combined to produce a rather more versatile version of the function VLOOKUP.

Assuming your data is in Sheet1 and you are moving it to Sheet2

Sheet2!D1 : enter a particle number.

Sheet2!E1 : =MATCH(D$1,Sheet1!A:A,0)

Sheet2!E2 : =MATCH(D$1,OFFSET(Sheet1!A$1,E1,,10000),0)+E1

Replicate E2 down.

Sheet2!A1 : =INDEX(Sheet1!A:A,$E1)

Replicate across to column C and then down as required.


is it ok to post a link to this forum to give credit?


----


I'm now working on the second half of my problem, which will be to pull an individuals entire "Team" information in as well as the individuals.

This is very challenging, but super exciting to find answers!


Thanks to all the great Excel users out there that are willing to share their knowledge!