Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel london - Question

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

excel london - Question

Resolved · Low Priority · Version Standard

Reply Mon 5 Mar 2007, 17:02Delegate Yanti said...

Yanti has attended:
Excel Advanced course

Question

Hello, I am trying to organize the information taken form more than one table into one, ranking only the entries different than zero into descending order.

From lists such as the below (note every continent is organized in a separate excell sheet)

EUROPE

italy 2%
england 0%

ASIA

thailand 1%
China 0%
Japan 6%

AMERICAS

US 7%
Canada 0%
Chile 5%

I need to be able to authomatically retreive the data different from ZERO, and organize them in descending order into a new excel spreadsheet.
as per below

US 7%
Japan 6%
Chile 5%
italy 2%
thailand 1%

Can you please let me know how to get this done, as I am trying but can't come up with anything./
cheers
yanti

For upcoming training course dates see: Pricing & availability

Reply Mon 5 Mar 2007, 17:17Trainer Amanda said...

RE: question

Hi Yanti

Thanks for your question.

You can do this by:

1. Clicking in one of the two columns that contains your data, then go to Data - Filter - AutoFilter.

2. Click the dropdown arrow about the column with the % showing and select Custom.

3. Choose 'does not equal' from the box on the left, then type 0 into the box on the right (i.e. you are applying a filter so you can see only rows where the % is not 0).

4. Click OK.

5. Copy and paste the filter results into a new spreadsheet.

I hope this works for you - let me know by reply if you have any problems.
Amanda

Reply Mon 5 Mar 2007, 17:28Delegate Yanti said...

RE: question

Hi Amanda,
I was hoping to inser a formula in the summary table in order to retreive authomatically all the info and organize them.

as I have very many tables, I can't work on each and everyone, it would take me ages.

is it possible to achieve it with a macro or something similar?

cheers
yanti

Reply Wed 7 Mar 2007, 11:17Trainer Garry said...

RE: question

This is a Visual Basic question which you really need to come on a course to understand the functions of doing this

Excel articles

· Some Mouse Click Tips In Excel
· Excel: Save Time With AutoFill
· Microsoft Excel Training Advanced Courses - Making Data Validation Work For You
· How To Ensure You Always Make A Complete Data Recovery
· Compare Different MS Excel Training Methods

Excel tip:

Checking if a calculation adheres to Order of Precedence

When writing formulas you must make sure that results will be calculated as you intended.

Excel adheres to the standard order of precedence for calculations. It calculates percentages, exponents, multiplication, and division in this order before calculating addition and subtraction.

For example, =7+5*3 results in an answer of 22, not 36.

To force a calculation to be completed before another calculations, place the section in parentheses: =(7+5)*3 will result in 36.

To check how excel is evaluating a formula, click on the cell and select the 'Tools' menu, select 'Formula Auditing' and click 'Evaluate Formula'

In the dialog box click on 'Evaluate' to watch as each part of the formula is successively calculated.

View all Excel hints and tips