Select LARGE / IF CRITERIAS / WRITE to another worksheet

[gldurand]

Hi Experts

I have a challenge that needs some VBA code or complex formula.

Challenge: I want to write certain fields of the 10 largest values from one worksheet to another

Requirements:

I want to search COLUMN BK from "INPUT FILE" for the 10 largest $$ amounts (There could be duplicates)

I want to copy TOP 10 values to another worksheet as long as Column AK is not DUPLICATE.


Output to new worksheet should be the following

COLUMN AK - COLUMN AS - COLUMN A - COLUMN H - COLUMN T- COLUMN BP - COLUMN BK

Confusing.....Work with me please, i really need to figure this out. I would prefer formulas, nut it might not be possible. So VBA code would be welcome and I would use CONTROLS to run them

Please help

Thanks for all your precious time

 

[gldurand]

That is excellent... If I have to put the helper column in the source, it should no tbe a problem...

As far as all-volunteer board, you have gone further than i could ever expect.. I feel real bad that you are spending so much time with me... I will certainly have to "PAY IT FORWARD".

Looking forward to your PM... I will send it to you right away
No Problem looking at it tonight... It will be a late night for me anyway.. Thanks my friend

Glenn

 

[Jerry Sullivan]

Hi Glenn,

When we applied the formulas I previously posted to your actual data set we discovered they were incorrectly handling cases in which you had duplicate values in your Total Opportunity Value column. That error wasn't apparent from your example data, because there were no duplicates.

I'm pretty sure the revised formulas shown below will return the correct values.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Top 10
2
3	Idx	Opportunity number	Opportunity Description	Customer name	sales stage	%	close date	net new / renewal	IN YEAR VALUE	Total Opportunity Value
4	21	XXXXXXX-18	nnn	NOP	B2	25	14	Renewal	18	18
5	18	XXXXXXX-15	nnn	NOP	C3	25	14	Renewal	15	15
6	10	XXXXXXX-8R	kkk	BCD	B2	25	11	Net New	8	14
7	15	XXXXXXX-13R	nnn	NOP	A1	25	14	Renewal	13	14
8	16	XXXXXXX-14	nnn	NOP	B2	25	14	Renewal	14	14
9	12	XXXXXXX-10	mmm	KLM	D4	25	13	Renewal	10	9
10	8	XXXXXXX-6	hhh	STU	A1	75	8	Net New	6	6
11	6	XXXXXXX-4	fff	PQR	D4	25	6	Net New	4	4
12	3	XXXXXXX-2	ccc	GHI	B2	50	3	Net New	2	2
13	1	XXXXXXX-1	aaa	ABC	A1	25	1	Net New	1	1
TOP 10

Excel 2007

Cell Formulas
Range	Formula
B4	=IF(N($A4),INDEX('Source Data'!$A$2:$L$23,$A4,MATCH(B$3,'Source Data'!$A$1:$L$1,0)),"")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

I modified the Total Opportunity Values for items XXXXXXX-8R and XXXXXXX-13R to be $14.00 to test it with duplicates. With the formulas posted earlier item XXXXXXX-8R would have appeared on the list 3 times.

This solution also removes all helper columns (except the Idx column in the Top 10).

Happy Victoria Day my friend!