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

 

[Jerry Sullivan]

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

Yes...that is confusing.

If you want to find the top 10 values from Column BK without duplicates and list them in Column AK, this would work....

Excel Workbook
	AK
1	Top 10 Unique
2	$3,000
3	$2,900
4	$2,800
5	$2,400
6	$2,300
7	$2,000
8	$1,900
9	$1,800
10	$1,600
11	$1,000
Sheet2

Excel 2007

Cell Formulas
Range	Formula
AK2	=LARGE('Input File'!$BK:$BK,1)
AK3	=LARGE('Input File'!$BK:$BK,INDEX(FREQUENCY('Input File'!$BK:$BK,AK2-1),2)+1)

Please let me know if I misunderstood what you are trying to do.

 

[Aladin Akyurek]

See also my post in:

http://www.mrexcel.com/forum/showthread.php?t=550439

 

[gldurand]

Hi JS411

I have the LARGE Function under control. What i am trying to accomplish is to search the source file for the TOP 10 values... That is easy.. Whast you provided helps a little, but I also want to bring with it 7 other fields (in the same row) to another worksheet.

Another issue is that i only want to write those values if a certain field (Column AK) is not duplicate

Any opther help would be greatly aprreciated

 

[gldurand]

OK, here is a sample of my data

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Opportunity number	Total Opportunity Value	Product	Customer name	Client Name	sales stage	Confidence	%	close date	Opportunity Description	RANK
2	XXXXXXX-1	$ 1.00	A	ABC	G. Durand	A1	In the Call	25	01/01/1900	aaa	10
3	XXXXXXX-1	$ 1.00	B	ABC	M. Reidy	A1	In the Call	25	02/01/1900	bbb
4	XXXXXXX-2	$ 2.00	A	GHI	G. Durand	B2	In the Call	50	03/01/1900	ccc	9
5	XXXXXXX-2	$ 2.00	B	GHI	G. Durand	B2	Not in the call	50	04/01/1900	ddd
6	XXXXXXX-3	$ 3.00	A	MNO	M. Reidy	C3	In the Call	25	05/01/1900	eee
7	XXXXXXX-4	$ 4.00	A	PQR	G. Durand	D4	In the Call	25	06/01/1900	fff	8
8	XXXXXXX-5	$ 5.00	A	STU	M. Reidy	E5	In the Call	75	07/01/1900	ggg
9	XXXXXXX-6	$ 6.00	A	STU	G. Durand	A1	In the Call	75	08/01/1900	hhh	7
10	XXXXXXX-6	$ 6.00	B	STU	G. Durand	A1	In the Call	75	09/01/1900	iii
11	XXXXXXX-8	$ 8.00	A	BCD	G. Durand	B2	In the Call	25	11/01/1900	kkk	6
12	XXXXXXX-9	$ 9.00	A	HIJ	M. Reidy	C3	Not in the call	25	12/01/1900	lll
13	XXXXXXX-10	$ 10.00	A	KLM	G. Durand	D4	In the Call	25	13/01/1900	mmm	5
14	XXXXXXX-11	$ 11.00	A	NOP	G. Durand	E5	Not in the call	25	14/01/1900	nnn
15	XXXXXXX-12	$ 12.00	A	NOP	G. Durand	E5	In the Call	25	14/01/1900	nnn
16	XXXXXXX-13	$ 13.00	A	NOP	G. Durand	A1	In the Call	25	14/01/1900	nnn	4
17	XXXXXXX-14	$ 14.00	A	NOP	G. Durand	B2	In the Call	25	14/01/1900	nnn	3
18	XXXXXXX-14	$ 14.00	B	NOP	G. Durand	B2	In the Call	25	14/01/1900	nnn
19	XXXXXXX-15	$ 15.00	A	NOP	G. Durand	C3	In the Call	25	14/01/1900	nnn	2
20	XXXXXXX-16	$ 16.00	A	NOP	G. Durand	D4	Not in the call	25	14/01/1900	nnn
21	XXXXXXX-17	$ 17.00	A	NOP	G. Durand	E5	In the Call	25	14/01/1900	nnn
22	XXXXXXX-18	$ 18.00	A	NOP	G. Durand	B2	In the Call	25	14/01/1900	nnn	1
23	XXXXXXX-19	$ 19.00	A	NOP	M. Reidy	A1	At risk	25	14/01/1900	nnn
Sheet1

I want to be able to list on another sheet the top 10 of Column B

IF Column A is duplicate only take first occurence
IF Column E = G. Durand
IF Column F = "A1" or "B2" or "C3" or "D4"
IF Column G = "In the Call"

So my result would be the row RANKED Highest to Lowest on another spreadsheet

I hope this clears up my request...

Any assistance would be greatly appreciated by complex formula or VBA Code

Thanks again

 

[Jerry Sullivan]

Thanks for posting that. It helped clarify a few things, but raised some other questions.

I want to be able to list on another sheet the top 10 of Column B

IF Column A is duplicate only take first occurence
IF Column E = G. Durand
IF Column F = "A1" or "B2" or "C3" or "D4"
IF Column G = "In the Call"

1. I'm confused trying to relate your last post to your OP. Is Column A the same as AK in your OP? Are the other 3 criteria new? How does Column BK relate?

2. Can the values in Column B be duplicated in your results? It's hard to tell because the duplicates align with the duplicates in Col A in your example.

3. What are the seven columns to be copied? There are 11 shown.

 

[gldurand]

Hi JS411

Please forgive me, but we should probably forget the original posting, as i tried to create an example using columns A-B-C-D etc..... I will modify Formulas or VBA code to reflect REAL DATA because my source file is 60 columns wide.

Answers to questions: Is Column A the same as AK in your OP? Are the other 3 criteria new? How does Column BK relate?

Yes column A is the same as AK in my original Post, BK is now Column B in my example... The criteria was always requested, but was more specific

2. Can the values in Column B be duplicated in your results? It's hard to tell because the duplicates align with the duplicates in Col A in your example.
Yes Column B can be duplicates and are always aligned to column A, as these are the same records, but broken out by Product. Ie: Opportunity # (Column A) 2 records that show the same Total Opportunity Value (Column B) because the value is overall but with 2 different Products (Column C)

3. What are the seven columns to be copied? There are 11 shown.

Column A
Column J
Column D
Column F
Column H
Column I
Column B

Thanks for all your assistance

Glenn

 

[Jerry Sullivan]

See also my post in:

http://www.mrexcel.com/forum/showthread.php?t=550439

Aladin,
Thank you so much for your citation. Your example of using Index & Match with Small is very instructive.

Building on your example, I was able to get so close to solving this, however I'm stumped in trying to incorporate the "IF Column A is duplicate only take first occurence" requirement without the use of a helper column.

Here is the best I was able to do...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Top N	10								TRUE
2	Top N Adjusted	10								FALSE
3	Idx	Opportunity number	Opportunity Description	Customer name	sales stage	%	close date	Total Opportunity Value		TRUE
4	21	XXXXXXX-18	nnn	NOP	B2	25	14/01/1900	$ 18.00		FALSE
5	18	XXXXXXX-15	nnn	NOP	C3	25	14/01/1900	$ 15.00		TRUE
6	16	XXXXXXX-14	nnn	NOP	B2	25	14/01/1900	$ 14.00		TRUE
7	15	XXXXXXX-13	nnn	NOP	A1	25	14/01/1900	$ 13.00		TRUE
8	12	XXXXXXX-10	mmm	KLM	D4	25	13/01/1900	$ 10.00		TRUE
9	10	XXXXXXX-8	kkk	BCD	B2	25	1/11/1900	$ 8.00		FALSE
10	8	XXXXXXX-6	hhh	STU	A1	75	1/8/1900	$ 6.00		TRUE
11	6	XXXXXXX-4	fff	PQR	D4	25	1/6/1900	$ 4.00		TRUE
12	3	XXXXXXX-2	ccc	GHI	B2	50	1/3/1900	$ 2.00		TRUE
13	1	XXXXXXX-1	aaa	ABC	A1	25	1/1/1900	$ 1.00		TRUE
14										TRUE
15										TRUE
16										TRUE
17										FALSE
18										TRUE
19										TRUE
20										TRUE
21										TRUE
22										TRUE
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=COUNTIF(Sheet2!B2:B23,"<="&SMALL(Sheet2!B2:B23,B1))
B4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(B$3,Sheet2!$A$1:$K$1,0)),"")
C4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(C$3,Sheet2!$A$1:$K$1,0)),"")
D4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(D$3,Sheet2!$A$1:$K$1,0)),"")
E4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(E$3,Sheet2!$A$1:$K$1,0)),"")
F4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(F$3,Sheet2!$A$1:$K$1,0)),"")
G4	=IF(N($A4),INDEX(Sheet2!$A$2:$K$23,$A4,MATCH(G$3,Sheet2!$A$1:$K$1,0)),"")
J1	=ISNA(MATCH(Sheet2!A2,Sheet2!A$1:A1,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've tried variations of the CSE formula below for H4, but I haven't found a way to get that first expression to evaluate to the equivalent of Range in Column J.

{=LARGE(ISNA(MATCH(Sheet2!A$2:A$23,Sheet2!A$1:A1,0))*
(Sheet2!E$2:E$23="G. Durand")*(Sheet2!F$2:F$23={"A1","B2","C3","D4"})
*(Sheet2!G$2:G$23="In the Call")*Sheet2!B$2:B$23,ROWS($H$4:H4))}

Any help would be greatly appreciated

Glen, Hope this helps!

 

[gldurand]

Hi Jerry

I dont mind the helper column, it works great in my mock up example... when i convert it into my real data i am getting a #N/A as a result in Cell H4....


I checked all the values and each formula character by character and everything seems to be ok.

I have prepared my actual SOURCE file of my real data with existing rows and titles..
Is there any way possible I can maybe forward you the spreadsheet and you can check out my formulas in the "Live" version.

It would expedite things, and i have a deadline fast approaching...

Send me an address so I may send you a gift certificate for STARBUCKS or something....

Thanks so much

 

[Jerry Sullivan]

Glen,

I'm glad to hear this helped. If you are going to keep a helper column, You might want to locate it on the same sheet as the source data since there is one cell per row. I just cut and paste this data into the report sheet so it would show up on a single screen shot.

I could take a look at your file, but I won't be able to get to that until tonight. I'll send you a PM with my email.

Thanks for offering to send a gift, but this is an all-volunteer board so I can't accept that. Your appreciation is more than enough thanks.