PDA

View Full Version : MS Excel, possible to Sort Column by BOLD entry



John W
17-08-2002, 02:24 PM
Hell there

I've a spreadsheet, that contains both Home & Work related expenses.

Currently the Work Related entries are in Bold face type.

Is it possible to Sort by Bold type?

Thanks....John.

robo
17-08-2002, 04:34 PM
John
I really don't like your chances. Bold isn't something that I've ever tested for with If statements. You could do just about anything - start or end the descriptions with a W or four Ms and a silent Q - but bold isn't good.
Maybe someone will know but I can't see how it can be done.
Excel doesn't usually use logical expressions that are concerned with how a cell looks, only it's contents and value.
robo.

antmannz
17-08-2002, 06:45 PM
I think you'd probably have to write a macro to look at the attributes of the cell formatting to achieve what you want to do.

-=JM=-
17-08-2002, 07:17 PM
I'll look into it and see if there is anything that may be able to do that.

But I've just spent a whole term going through a book on Excel and did didn't see anything that would do something like that.

Is there anything else in the sheet that would separate a home expense to a work expense.


As a rule for next time. Try and keep your business related stuff separate from the home related stuff.

godfather
17-08-2002, 07:35 PM
I am sure a VB macro could be written, if you are a VB expert, but easist may be to put a 'W' in a cell to the left of the work entries and use SUMIF(

-=JM=-
17-08-2002, 08:08 PM
Would you be able to explain/show what the layout is at the moment and what the desired layout is.

Using edit|Find you can find (and select all) cells that have a particular format to them.

So if all the work expenses are formatted at the moment in Arial, 10, bold. It is relatively easy to copy (or cut) out those entries and put them somewhere else.

But yes a macro would be the most efficient way if anyone knows how to write one to do this.

Heather P
17-08-2002, 09:25 PM
How many rows are involved?

If it's hundreds....

If it's a managable amount insert a column. If the work expenses are less than the home expenses then run down this column putting something in it on each of the bold rows (like w for work)

You can then use an if statement in a futher empty column to extract only work expenses.

=IF(B2="w",F2,0) - If the cell B2 has a w in it put the content of F2 in this cell. If the cell B2 doesn't have a w in it put a 0 in this cell.

robo
17-08-2002, 09:33 PM
Yep, that's what I would do.
Bolds are handy, but just don't do much useful. I had a similar problem with a bunch of things where I bolded the duplicates, but then couldn't sort them to one end.
robo.

parry
17-08-2002, 11:44 PM
Hi, I agree with the previous postings that sorting by another criteria is required. I could write some code that could do this for you but there are quite a few things to think of in this situation and I would need considerable more information on exactly what you require. Sometimes things you think should be easy takes quite a bit of code to achieve :-)

For example, the first step would be to determine if the cell was bolded and then apply some logic to this to outsort from other cells. So if they are bolded then what - sort highest to lowest with bolded first?

If I had to write the code for this I would have the code insert another column as Heather suggested then put an identifier in this column if the cell in the same row was bolded and then loop the code down through all the cells in your selection. The next step would be to run criteria to sort by the new column first then your existing column/s and then after the sort operation delete the inserted column.

You are best to do this manually if you only have a few records and ideally use this other criteria to sort by. The sort functionality is limited to only 3 columns but you can be clever and concatenate cells for sorting if needed.

cheers
Parry

mikebartnz
18-08-2002, 12:01 AM
Ok you could write a macro to do this but I don't see the point as it would be far better to separate Work from home especially if you don't have the skills to write a macro.
Work on one sheet and Home on another with the combined on yet another

parry
18-08-2002, 12:20 AM
Correct. Was pointing out that it can be done and trying to outline that it is not a simple thing. Code is only good if you want to do it repeatedly as well (unless you have thousands of records) - this sounds like a one off operation.

You can always have two separate sheets for work and home then combine details into another sheet if needed.

:-)

John W
18-08-2002, 12:26 PM
Thanks for all the replies.

The reason Work & Home expenses are together, to collect Hotpoints on my personal Visa card.

Luckly all the suppliers have the same Visa Entry details on the Downloaded statement. So, Ive been sorting by Name, then Bolding the Work related items.

Once they are sorted by name, I can easily add anothe column to designate Wk or Hm expense, which is probably the way Ill go.

Cheers....John.