Exracting records from a database

[baggarwal]

Hi All:

I was wondering if anyone knew how you can extract the top three records from a database without using pivot tables. What I want to do is display the two records that contain the highest dollar amounts.

Here is the database:

Book2
	A	B	C	D
3	Account	Name	Dollars
4	500000	John	500
5	500050	Dave	100
6	500100	Mike	200
7	500150	Steve	100
8	500200	Kay	150
9	500250	Harry	600
Sheet1

Here is the desired result:

Book2
	A	B	C	D
12	Account	Name	Dollars
13	500250	Harry	600
14	500000	John	500
Sheet1

Any help you could provide would be much appreciated.

BA

 

[Yogi Anand]

Hi Bharat:

You can use AutoFilter Top10
see simulation ...

y021102h1.xls
	A	B	C	D	E	F	G	H
2
3	Account	Name	Dollars
4	500000	John	500
5	500050	Dave	100
6	500100	Mike	200
7	500150	Steve	100
8	500200	Kay	150
9	500250	Harry	600
10
11
12	Account	Name	Dollars	DataextractedusingAutoFilterTop10items
13	500250	Harry	600
14	500000	John	500
15	500100	Mike	200
16
Sheet7

</SPAN>

Regards!
Yogi Anand

 

[baggarwal]

This doesn't solve my problem. The database will be in a separate tab somewhere in a workbook. Then there will be a separate spreadsheet somewhere else that will extract data from the main database spreadsheet.

What I need is the top 3 records from the database to be copied over to another location.

I want to setup a rule that would do this but I'm not sure how?

Does someone know how to do this?

BA

 

[Barry Katcher]

Why not record a macro to first do the AutoFilter and then copy/paste to where ever you want to.

 

[Yogi Anand]

Hi Bharat:

You can use the LARGE function to find the Largest, second largest, ... anf INDEX/MATCH functions for other data. See the worksheet simulation

y021102h1.xls
	A	B	C	D
1
2
3	Account	Name	Dollars
4	500000	John	500
5	500050	Dave	100
6	500100	Mike	200
7	500150	Steve	100
8	500200	Kay	150
9	500250	Harry	600
10
11
12	Account	Name	Dollars
13	500250	Harry	600
14	500000	John	500
15	500100	Mike	200
16
Sheet7a

</SPAN>

Regards!
Yogi Anand

 

[baggarwal]

When I copied the formula over I got error (#NA). What did I do wrong?

 

[Yogi Anand]

Hi Bharat:

Do the range names match in the source formula and where you copied to? these not matching could be one of the reasons, and there could be other issues. How about posting your source data, the source formula and the target where you copied it to -- that may help in deciphering what is the problem.

Regards!
Yogi Anand

 

[Juan Pablo González]

You can use Advanced Filter to do what you want. You can also get the results in another sheet as you stated.

Use something like this:

Book1
	A	B	C	D	E	F	G
3	Account	Name	Dollars
4	500000	John	500				FALSE
5	500050	Dave	100				TRUE
6	500100	Mike	200				FALSE
7	500150	Steve	100
8	500200	Kay	150
9	500250	Harry	600
Sheet1

Select Sheet2, assuming that's your destination sheet. Go to Data | Advaced Filter, and choose:

Copy To Another Location
ListRange: Sheet1!$A$3:$C$9
CriteriaRange: Sheet1!$G$3:$G$6
Copy to: Sheet2!A3 (Or wherever you want it)

I got this results:

Book1
	A	B	C	D
3	Account	Name	Dollars
4	500000	John	500
5	500100	Mike	200
6	500250	Harry	600
Sheet2