Exracting records from a database

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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
ABCD
3AccountNameDollars
4500000John500
5500050Dave100
6500100Mike200
7500150Steve100
8500200Kay150
9500250Harry600
Sheet1


Here is the desired result:
Book2
ABCD
12AccountNameDollars
13500250Harry600
14500000John500
Sheet1


Any help you could provide would be much appreciated.

BA
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Bharat:

You can use AutoFilter Top10
see simulation ...
y021102h1.xls
ABCDEFGH
2
3AccountNameDollars
4500000John500
5500050Dave100
6500100Mike200
7500150Steve100
8500200Kay150
9500250Harry600
10
11
12AccountNameDollarsDataextractedusingAutoFilterTop10items
13500250Harry600
14500000John500
15500100Mike200
16
Sheet7
</SPAN>

Regards!
Yogi Anand
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Why not record a macro to first do the AutoFilter and then copy/paste to where ever you want to.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
ABCD
1
2
3AccountNameDollars
4500000John500
5500050Dave100
6500100Mike200
7500150Steve100
8500200Kay150
9500250Harry600
10
11
12AccountNameDollars
13500250Harry600
14500000John500
15500100Mike200
16
Sheet7a
</SPAN>

Regards!
Yogi Anand
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
When I copied the formula over I got error (#NA). What did I do wrong?
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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
ABCDEFG
3AccountNameDollars
4500000John500FALSE
5500050Dave100TRUE
6500100Mike200FALSE
7500150Steve100
8500200Kay150
9500250Harry600
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
ABCD
3AccountNameDollars
4500000John500
5500100Mike200
6500250Harry600
Sheet2
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,568
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top