Returning multiple values from multiple criteria

[CheekyDevil]

Hey all,

I'm in desperate need for a code that will allow the return of multiple values from a line of cells (of which I can then CONCATENATE) using a given criteria.

For context, my headings are:

A1 = Date B1 = Agent C1 = TL D1 = Type E1 = Ref# F1 = Comments

What I need is a code that allows me to enter a TL name in a cell (say L1 to keep it out of the way), and return all the agents and corresponding data from A to F that have this TL in the data range (A1:F5000)

Then (if possible) if I can further narrow it down to return the result of all items from a particular agent (name entered in K1), for example, all data from Dave (agent) in Allen's team (TL).

I've tried using an INDEX + MATCH combination but as it turns out I'm not at this level to understand this formula just yet HAHA

Thank you for any help you can provide

 

[Aladin Akyurek]

Try to post a small sample that Excel can read along with the expected result(s).

 

[CheekyDevil]

Sure thing, I tried to do this using a table on the initial post but I failed.... miserably

I really hope this helps (I've used an excel sheet but not sure on the pasted format)

[TABLE="class: grid, width: 2188"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1
[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]K1[/TD]
[TD]L1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]AGENT
[/TD]
[TD]TL[/TD]
[TD]TYPE[/TD]
[TD]REF[/TD]
[TD]NOTES
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Logan[/TD]
[TD]Niel[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8/02/2018[/TD]
[TD]Logan
[/TD]
[TD]Niel [/TD]
[TD]Coaching[/TD]
[TD]2131[/TD]
[TD]data about logan 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/02/2018[/TD]
[TD]Paige
[/TD]
[TD]Craig [/TD]
[TD]Feedback[/TD]
[TD]5215641651[/TD]
[TD]data about paige [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]**DESIRED RESULT**
[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/2018[/TD]
[TD]Vicky
[/TD]
[TD]Toddy [/TD]
[TD]Coaching[/TD]
[TD]157891[/TD]
[TD]data about vicky [/TD]
[TD][/TD]
[TD][/TD]
[TD]Agent Name[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/02/2018[/TD]
[TD]Jordan
[/TD]
[TD]Dylan [/TD]
[TD]Live Escalation[/TD]
[TD]15156[/TD]
[TD]data about jordan
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ref#[/TD]
[TD]Type[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD="align: right"]16/02/2018[/TD]
[TD]Amy
[/TD]
[TD]Niel [/TD]
[TD]Feedback[/TD]
[TD]47851[/TD]
[TD]data about amy [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2131
[/TD]
[TD]Coaching[/TD]
[TD]data about logan 1[/TD]
[/TR]
[TR]
[TD="align: right"]20/02/2018[/TD]
[TD]Ali
[/TD]
[TD]Toddy [/TD]
[TD]Feedback[/TD]
[TD]1789465[/TD]
[TD]data about ali [/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]364872334[/TD]
[TD]Call Review
[/TD]
[TD]data about logan 2[/TD]
[/TR]
[TR]
[TD="align: right"]21/02/2018[/TD]
[TD]Debi
[/TD]
[TD]Niel [/TD]
[TD]Feedback[/TD]
[TD]4178451[/TD]
[TD]data about debi [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/02/2018[/TD]
[TD]Logan
[/TD]
[TD]Niel [/TD]
[TD]Call Review[/TD]
[TD]364872334[/TD]
[TD]data about logan 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So from A1 to F5000 is going to be data entries from a user form (simply mentioned as this is going to be my 'data pool'). What I am developing is a tool for the agents Team Leaders to pull the data from this pool to narrow down the results to only their agents, and then further still to an individual agent.

In summary, if only the TL name is populated, show all data within the range A1 to F5000 (or greater) with all the agents in their team, and if an individual name is selected, narrow these down to only the data from this agent.

In all entries, the TL of the agent is going to be in the adjacent cell next to the agent name (I,e Agent is in A1, their TL will be in B1)

Again I really hope this clarifies my situation as I'm not the best at explaining myself

Thank you for your time and potential assistance

 

[Aladin Akyurek]

Thanks for the sample and expected results...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][/tr][tr][td]

1​

[/td][td]DATE[/td][td]AGENT[/td][td]TL[/td][td]TYPE[/td][td]REF[/td][td]NOTES[/td][td]Logan[/td][td]Niel[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

8/2/2018​

[/td][td]Logan[/td][td]Niel[/td][td]Coaching[/td][td]

2131​

[/td][td]data about logan 1[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

13/02/2018​

[/td][td]Paige[/td][td]Craig[/td][td]Feedback[/td][td]

5215641651​

[/td][td]data about paige[/td][td]**DESIRED RESULT**[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]

15/02/2018​

[/td][td]Vicky[/td][td]Toddy[/td][td]Coaching[/td][td]

157891​

[/td][td]data about vicky[/td][td]Agent Name[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

16/02/2018​

[/td][td]Jordan[/td][td]Dylan[/td][td]Live Escalation[/td][td]

15156​

[/td][td]data about jordan[/td][td]Ref[/td][td]Type[/td][td]Notes[/td][/tr]
[tr][td]

6​

[/td][td]

16/02/2018​

[/td][td]Amy[/td][td]Niel[/td][td]Feedback[/td][td]

47851​

[/td][td]data about amy[/td][td]

2131​

[/td][td]Coaching[/td][td]data about logan 1[/td][/tr]
[tr][td]

7​

[/td][td]

20/02/2018​

[/td][td]Ali[/td][td]Toddy[/td][td]Feedback[/td][td]

1789465​

[/td][td]data about ali[/td][td]

364872334​

[/td][td]Call Review[/td][td]data about logan 2[/td][/tr]
[tr][td]

8​

[/td][td]

21/02/2018​

[/td][td]Debi[/td][td]Niel[/td][td]Feedback[/td][td]

4178451​

[/td][td]data about debi[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]

27/02/2018​

[/td][td]Logan[/td][td]Niel[/td][td]Call Review[/td][td]

364872334​

[/td][td]data about logan 2[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In K6 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($B$2:$B$9=$K$1,IF($C$2:$C$9=IF($L$1="","?*",$L$1),ROW($A$2:$F$9)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

 

[CheekyDevil]

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

 

[markmzz]

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

Hi!

Try this small modification in Aladin's formula:

=IFERROR(INDEX($A$2:$F$9,SMALL(IF($C$2:$C$9=$L$1,IF($B$2:$B$9=IF($K$1="",$B$2:$B$9,$K$1),ROW($A$2:$F$9)-ROW($A$2)+1)),ROWS($K$6:K6)),MATCH(K$5,$A$1:$F$1,0)),"")

Markmzz

 

[Aladin Akyurek]

Thank you very much for the help there Aladin. I sincerely appreciate it, however I regret to advise it has not yet performed as intended. I can play around with the code and see if I can get it to work as I think it may need to have the references expanded (from F9 to F-ongoing lol) but I think I can work with it as it is

Again I really thank you for taking time to do that for me It's been a real head-scratcher for a while now

What did I miss if I'm understanding your comment correctly?

 

[CheekyDevil]

Hey thank you both very much for helping here!

Not too sure why, but I copy and paste the code to K6 (ensuring I CTRL + SHIFT + ENTER also to make it an array formula) and dragged it down, however it returns blank results, even when I copy and paste the names in the references to ensure accuracy.

I do have a sample sheet I can send but cannot work out how to upload it to this thread... (I'm awaiting the "NEWBIE" remarks hehe)

I suppose the easiest way of explaining this is I need it to "auto filter" the invalid results from A2 to F~ (as this will be an ever expanding data set) and only show the corresponding agents data in the row.

 

[Aladin Akyurek]

Hey thank you both very much for helping here!

Not too sure why, but I copy and paste the code to K6 (ensuring I CTRL + SHIFT + ENTER also to make it an array formula) and dragged it down, however it returns blank results, even when I copy and paste the names in the references to ensure accuracy.

I do have a sample sheet I can send but cannot work out how to upload it to this thread... (I'm awaiting the "NEWBIE" remarks hehe)

I suppose the easiest way of explaining this is I need it to "auto filter" the invalid results from A2 to F~ (as this will be an ever expanding data set) and only show the corresponding agents data in the row.

Both?

Here is the workbook which implements the set up I described: https://www.dropbox.com/s/mf3owhflrpg171j/CheekyDevil%20Returning%20multiple%20values%20from%20multiple%20criteria.xlsx?dl=0

Hope this helps.

P.S. Note that the set up expects an agent in K1 while L1 can be empty if so desired.

 

[CheekyDevil]

Thanks Aladin, yes both you and markmmz

That has worked perfectly! Downloading your workbook has allowed me to see the code working, and then updating it to Marks amendment has done exactly what I wanted it to do

Once again I really cannot thank you enough for all your assistance. I'm expected to have this report functional by the end of the week and could not have met this without your help. You guys are simply amazing and thank you for being so patient with me!