A formula which results in a list of names based on specific criteria

[Eftychia]

Hi all!

How can we have a list as a result in excel?
In order to help you understand the issue I made the attached file. In the sheet "data" you can see a list of names where we have results for 2 KPIs (KPI1 & KPI2, values available: T, MT, ML, B).
In the sheet "matrix" we have the matrix of these 2 KPIs and I need a formula in order the cells C5:F8 to be filled with the names matched. For example in cell C5 I need the list of names (of sheet "data") that have T in both KPIs etc.

Is this something easy to do?

Any help will be appreciated

[Glenn Kennedy]

You can't get it in exactly the format uou want without a bit of VBA.

Please Login or Register  to view this content.

Here's the code that needs to be inserted in a module (right click sheet name, view code, INSERT, MODULE:

The formula (array entered) is
=concatall(IF(data!$B$3:$B$64=matrix!$B5,IF(data!$C$3:$C$64=matrix!C$4,data!$A$3:$A$64,""),""),", ")

where the big bold bit at the end defines the separator between each answer.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

DON'T FORGET - open this file as macro enabled...

[JohnTopley]

Another VBA solution

Please Login or Register  to view this content.

[Eftychia]

Ouao! This is great! Thank you!

To do this in another file I need to change something in the code or the code is to define the formula concatall only?

[Eftychia]

John thank you too!
I have the same question for you... Change the code is needed for another file where I have more names for instance?

[JohnTopley]

Yes you will need to change code: If you post file with your complete list then I will amend code.

If you keep the "MATRIX" file in the same rows/columns but are just extending row 4 and column B then you need to change:

Dim matrix(1 To 4, 1 To 4) As String ..... from 4 to number of items

ws2.Range("c4:f4") extend to maximum column (you could default to Z for example)

ws2.Range("c5:f8") to new range of MATRIX

[Glenn Kennedy]

In my case, no. As long as the code is in a module, it should be fine. The code doesn't need to be adjusted.

[Eftychia]

The matrix will be the same. In the sheet data I may have changes, since agents come and go all the time and the number of names will be different.
I added names till agent71 and press run and it worked fine

[Glenn Kennedy]

Glad to have helped. thanks for the Rep.

[JohnTopley]

If you are only adding/deleting agents, then no change is required.

[Eftychia]

Thank you both for the help! I really appreciated!

[Glenn Kennedy]

But of course the FORMULA will need to be adjusted...

=concatall(IF(data!$B$3:$B$64=matrix!$B5,IF(data!$C$3:$C$64=matrix!C$4,data!$A$3:$A$64,""),""),", ")

[Eftychia]

Yes. that I figured out... The vba part is the black box for me..
Thanks again Glenn!

[Glenn Kennedy]

Just checking... some people (me included)... use the term "code" (mistakenly) when they mean "formula"...

[Eftychia]

And how they say the code?