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.

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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...