The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

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

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - Data Sorting

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

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

Training information:

Excel - Data Sorting

Resolved · Low Priority · Version 2003

ReplyThu 6 May 2010, 14:43Delegate Julie said...

Excel - Data Sorting

Hi (me again),

I want to sort a list of numbers into pairs, i.e. £1,000 & -£1,000, £2,000 and -£2,000.

Basically, for each value I have a positive and negative which I want to keep together.

If I data sort by ascending I get all the negatives together followed by all the positives.

How can I sort my values into ascending order ignoring signage please?

Jules

For upcoming training course dates see: Pricing & availability

ReplyThu 6 May 2010, 16:07Trainer Stephen said...

RE: Excel - Data Sorting

Hi Julie
Thanks for your question.

This is fairly straightforward. Add an additional column, into which you place the ABS function. This returns the absolute value of a number, i.e. it removes the sign, so -200 becomes 200. You can then sort on this column.

Regards

Stephen

ReplyThu 6 May 2010, 19:21Delegate Julie said...

RE: Excel - Data Sorting

Thanks Stephen - now why didn't I think of that.

One more quick question if I may, when I do MATCH function, if there is no match a #N/A is returned. How can I return a text message such as "no match", instead of #N/A?

I have tried the following:

if(iserror(match(a2,a2:a100,0),"no match",match(a2,a2:a100,0))

Thanks Stephen.

Jules

Excel articles

· Jazzing Up Excel With Graphics
· Improve Performance Of Your Excel Macros
· Importing Useful Information From The Web To Excel
· How To Use Functions In Excel 2010
· Why Excel Has More Cloud Coverage

Please browse our web site to find out more about
advanced excel seminar training and other Microsoft training courses.

Excel tip:

Adding Rows or Columns in an Excel 2010 Worksheet

If you want to add a row to an Excel spreadsheet, these are the simple steps you should take:

With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert.

Follow exactly the same steps if you want to add a column to an Excel worksheet, right click on the column header, choose Insert and the new column will be inserted to the left of the selected column.

View all Excel hints and tips

InfoPath's Ideal Solutions For Information Issues

Giles wants to collate data on the work done by his company; Loxbury hospital staff are looking for an easy way to request equipment; Jeanette's IT department needs a simple and reliable means for staff to report faults. Microsoft InfoPath provides simple solutions for these and countless other information demands.

» Article: Infopath training london

I would like to know how you create headings that follow the page. so when you scroll down to the bottom you can still see what the headings of the colmns are.

» Forum post: Column headings

To summarise all the the key point from all slides onto one PowerPoint presentation,

1) click Slide Sorter ( on view menu bar).

2)Hold down the CTRL while you click the slides that have titles that you want to include in your summary slide.

Op...