Pull data using criteria from large data set

[Estee28]

Hello,

I would like to get some help please.

I am attempting to pull data for the sales team from a large data set using the following criteria; CNR over 90, probability of 100 and only names from the specialist column. As the department is placed under the specialist by default for unassigned booked deals. I would also like the formula to take into account the two different departments. Please see below for the example and desired result.

[TABLE="width: 949"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Pipeline No.[/TD]
[TD]GMM[/TD]
[TD]Deal Banker[/TD]
[TD]Department[/TD]
[TD]Specialist[/TD]
[TD]Member Name[/TD]
[TD]Product Name[/TD]
[TD]Probability[/TD]
[TD]CNR[/TD]
[TD]AUM[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]North[/TD]
[TD]Rob Donald[/TD]
[TD]Dept blue[/TD]
[TD]John Doe[/TD]
[TD]Candy world[/TD]
[TD]Product 1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]8[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]South[/TD]
[TD]Bob Howard[/TD]
[TD]Dept blue[/TD]
[TD]Richard Blackwood[/TD]
[TD]Toms wheels[/TD]
[TD]Product 2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]12[/TD]
[TD]Terrible[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]East[/TD]
[TD]Yewande Adebayo[/TD]
[TD]Dept green[/TD]
[TD]Tony Brown[/TD]
[TD]Healthy Treats[/TD]
[TD]Product 3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]31[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]West[/TD]
[TD]Lucy Ball[/TD]
[TD]Dept green[/TD]
[TD]Lisa Lopez[/TD]
[TD]Kats cars[/TD]
[TD]Product 4[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]9[/TD]
[TD]Great[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]North[/TD]
[TD]Olu Baker[/TD]
[TD]Dept blue[/TD]
[TD]Dept blue[/TD]
[TD]Foodie[/TD]
[TD]Product 5[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]46[/TD]
[TD]Perfect[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]South[/TD]
[TD]Bob Howard[/TD]
[TD]Dept green[/TD]
[TD]Jessica Hamilton[/TD]
[TD]Worldly cusine[/TD]
[TD]Product 6[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]78[/TD]
[TD]Excellent[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]East[/TD]
[TD]Hannah Watson[/TD]
[TD]Dept green[/TD]
[TD]Dept green[/TD]
[TD]Tom Hanks.LTD[/TD]
[TD]Product 7[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]60[/TD]
[TD]Amazing[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Dept Blue[/TD]
[TD][/TD]
[TD="colspan: 3"]Dept Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banker[/TD]
[TD]Rob Donald[/TD]
[TD]Bob Howard[/TD]
[TD][/TD]
[TD]Banker[/TD]
[TD]Yewande Adebayo[/TD]
[TD]Lucy Ball[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Specialist[/TD]
[TD]John Doe[/TD]
[TD]Richard Blackwood[/TD]
[TD][/TD]
[TD]Specialist[/TD]
[TD]Tony Brown[/TD]
[TD]Lisa Lopez[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]North[/TD]
[TD]South[/TD]
[TD][/TD]
[TD]Region[/TD]
[TD]East[/TD]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD]Product[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AUMs $MM[/TD]
[TD]$8MM[/TD]
[TD]$12MM[/TD]
[TD][/TD]
[TD]AUMs $MM[/TD]
[TD]$31 [/TD]
[TD]$9 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CNR $M[/TD]
[TD]$100M[/TD]
[TD]$96M[/TD]
[TD][/TD]
[TD]CNR $M[/TD]
[TD]$45 [/TD]
[TD]$30M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client[/TD]
[TD]Candy world[/TD]
[TD]Tom Wheels[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD]Healthy Treats[/TD]
[TD]Kats cars[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Deal[/TD]
[TD]Bad[/TD]
[TD]Great[/TD]
[TD][/TD]
[TD]Deal[/TD]
[TD]Good[/TD]
[TD]Great
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

At the moment I have the headings on the right e.g. "Banker, Specialist...." and I would like to pull in the rest of the data. The concern that I have is that my data table goes across (2 examples in each dept above) so I am unable to drag down the formulas. I welcome all suggestions, please let me know if you require further information.

Kind regards,

Esther

 

[tonyyy]

Esther,

Have you explored or considered using a PivotTable? Seems it may be suitable for your data set.

Cheers,

tonyyy

 

[Estee28]

Esther,

Have you explored or considered using a PivotTable? Seems it may be suitable for your data set.

Cheers,

tonyyy

Hello Tony,

Thank you for responding to my post. I experimented with the pivot table function and got exactly what I wanted. Thank you very much for your help!!!

Kind regards,

Esther

 

[tonyyy]

You're very welcome, Esther. Glad it worked out...