Sorting and coloring cells, formula or macro help needed

I have a problem that i don't know how to solve. I wrote it all in my excel 
file that needs to be done.

File is here http://www.sendspace.com/file/q4jc71

Anything that isn't clear, pls ask me. I need this really badly, because my 
tables sometimes have thousands of those records. 
0
Utf
4/3/2010 7:55:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
703 Views

Similar Articles

[PageSpeed] 1

HTH:
http://excel.tips.net/Pages/T002581_Sorting_Data_Containing_Merged_Cells.html
Micky


"domyrat" wrote:

> I have a problem that i don't know how to solve. I wrote it all in my excel 
> file that needs to be done.
> 
> File is here http://www.sendspace.com/file/q4jc71
> 
> Anything that isn't clear, pls ask me. I need this really badly, because my 
> tables sometimes have thousands of those records. 
0
Utf
4/3/2010 7:58:01 PM
Anyone could help me pls? Its very important for me to have this :(

"domyrat" wrote:

> I have a problem that i don't know how to solve. I wrote it all in my excel 
> file that needs to be done.
> 
> File is here http://www.sendspace.com/file/q4jc71
> 
> Anything that isn't clear, pls ask me. I need this really badly, because my 
> tables sometimes have thousands of those records. 
0
Utf
4/4/2010 10:39:01 AM
Sorry...
My here above reply was misplaced.
Micky


"domyrat" wrote:

> Anyone could help me pls? Its very important for me to have this :(
> 
> "domyrat" wrote:
> 
> > I have a problem that i don't know how to solve. I wrote it all in my excel 
> > file that needs to be done.
> > 
> > File is here http://www.sendspace.com/file/q4jc71
> > 
> > Anything that isn't clear, pls ask me. I need this really badly, because my 
> > tables sometimes have thousands of those records. 
0
Utf
4/4/2010 12:21:01 PM
Hello Zagreb, this is Tel-Aviv calling... (-;
I followed your request to color the entire rows even though I don't se a 
reason to color 256 cells in one row.
1) Select the range A2:IV40
2) 'Format' > 'Conditional Format...' > declare 2 Conditions (shown in the 
picture) you do not need to declare White because White is your default 
background.
*** I'm sure my Hebrew interface will cause no trouble for ou to understand 
the procedure ***
http://img179.imageshack.us/img179/3283/nonamej.png
As for the second part of your question - try "Advanced Filter".
Micky


"domyrat" wrote:

> Anyone could help me pls? Its very important for me to have this :(
> 
> "domyrat" wrote:
> 
> > I have a problem that i don't know how to solve. I wrote it all in my excel 
> > file that needs to be done.
> > 
> > File is here http://www.sendspace.com/file/q4jc71
> > 
> > Anything that isn't clear, pls ask me. I need this really badly, because my 
> > tables sometimes have thousands of those records. 
0
Utf
4/4/2010 12:49:01 PM
Well..., for the second part of your question and only if I understood you 
correctly - take a look at the picture.
[I changed the date to 3 days a go in order to present what the results were 
on Apr. 1st.]
After having all those TRUE/FALSE you can "Auto-Filter" column E to what 
ever you need.
If I misunderstood that part - I'm sure you will be able to adjust the 
formula to your needs.
http://img69.imageshack.us/img69/6987/nonameu.png
Micky


"domyrat" wrote:

> Anyone could help me pls? Its very important for me to have this :(
> 
> "domyrat" wrote:
> 
> > I have a problem that i don't know how to solve. I wrote it all in my excel 
> > file that needs to be done.
> > 
> > File is here http://www.sendspace.com/file/q4jc71
> > 
> > Anything that isn't clear, pls ask me. I need this really badly, because my 
> > tables sometimes have thousands of those records. 
0
Utf
4/4/2010 2:18:01 PM
Reply:

Similar Artilces:

Where's the Sort by New Message option?
Not sure when the layout of the newsgroups changed but I can't seem to find the ability to sort by new message in a thread instead of sorting by just the original thread date. That was hugely helpful as I could follow along and see what threads were still active. If you're gonna hang around in the newsgroups (any newsgroups), you may want to start using a newsreader. Microsoft Outlook Express can do it for you. Saved from a previous post: You may want to connect to the ms newsserver directly: If you have Outlook Express installed, try clicking on these links (or copy and paste i...

Sorting
Hi, is it possible to use variable for sorting? Something like: declare @sort varchar(10) SET @sort='column1 DESC' SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table Is dynamic SQL only option? Thank you, Simon On 15/04/2010 11:28, simon wrote: > Hi, > > is it possible to use variable for sorting? > > Something like: > > declare @sort varchar(10) > SET @sort='column1 DESC' > > SELECT ROW_NUMBER() OVER (ORDER BY @sort)AS rowId, * FROM table > > Is dynamic SQL only option? You can use CASE to he...

How to sort account list?
I'm using MS Money 2004 Small Business. In the account list, my accounts are catagorized by and in this order: - bank accounts - credit accounts - investment accounts - loans and liabilities - asset accounts TOTAL BALANCE In the above view, I cannot easily determine totals for assets and liabilities. The following view does this. - bank accounts - investment accounts - asset accounts SUBTOTAL -loans and liabilities SUBTOTAL TOTAL BALANCE How do I get the above view? Thanks, Brett I don't use Small Business. But you can run a Net Worth report to get total assets and ...

Excel 2007: How to change fill color in Theme Colors
I have just converted to Excel 2007 from Excel 2003 and am still getting used to all the changes. In Excel 2003, one of the standard fill colors was Light Yellow and I have used it in thousands of spreadsheets. Excel 2007 does not have Light Yellow in the standard Theme Colors list. I can select it from Custom Colors but do not want to do that every time or rely on picking it from Recent Colors. In Excel 2003, you could go to Tools/Options and change the colors that were presented in the dropdown fill color box. Can this be done in Excel 2007? Thanks in advance, David Wow. This is ...

Diagram show blank as zero value when based on a formula
When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart will show the blank value "" as zero even if you whant to plot gaps or plot the empty cell interpolated. Yes, I know Excel say empty cell and the cell is not empty when there is a formula. Is there anybody who have found a "work around"? I often have formulas that generate zero in the result but I want to plot the result as if it was empty. I found the reply myself in a question from another user similar to this issue. I will use NA() instead of "" Thank you that works! &q...

DO we need to format the path?
I am writing a macro in which I am passing the name of a file to a function. If I pass just the name of the file everything goes fine, but if i give the ull path, it shows me error. i.e . pass("temp.xls") - > works fine But pass("c:\temp.xls") -> shows error :"Run time error 9, Subscript out of range" DO we need to format the path in some special way or ??? Regards, Jatz -- JAtz_DA_WAY ------------------------------------------------------------------------ JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&...

high light the selected cell
I have a very big spread sheet. Each row goes across 15 cells. How can I have the row outline in a different color to show what row i'm on and once I tab to the cell I need how do I get the colunm outlined in a different color Try Chip Pearson's customizable RowLiner add-in for more positve viewing of selected cell. http://www.cpearson.com/excel/RowLiner.htm Note: does not work on a protected sheet. Gord Dibben MS Excel MVP On Tue, 8 Sep 2009 14:39:25 -0700, Frank <Frank@discussions.microsoft.com> wrote: >I have a very big spread sheet. Each row goes across 15 cells....

calander colors
After moving my Exchange 5.5 from a NT 4.0 PDC to a Win2K member, the colors associated with my calander objects dont print correctly. I am working with the printer's vendor for support, but there doesn't seem to be any logical answers since the issue is only with the Outlook calander. Any ideas..... ...

Track changes to cell formats
Is there any way (or workaround) to track changes made to cell formats, such as number of decimals displayed, bolding, etc. I really need to know which formats have been changed. Any help you can give me would be greatly appreciated. Trish I think I'd use another worksheet that was formatted exactly the way I wanted it. (maybe hide that sheet). Then I could compare that base sheet's format with the other sheet's current format. Trish wrote: > > Is there any way (or workaround) to track changes made to > cell formats, such as number of decimals displayed, > ...

Color documents are printing in black and white only. Why?
I've recently upgraded to the newest Publisher version and I hate it. I have been unable to print any document in color...no matter what it looks like on screen, in a print preview, and when I print, it is in greyscale/black and white. Help! Check your settings within the printer setup. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Bobbyesox" <Bobbyesox@discussions.microsoft.com> wrote in message news:CDE46E92-EC6D-40F2-8244-FA71F7C4D275@microsoft.com... > I've recently upgraded to the newest Pub...

Query with IIF Expression Need Help
Hi ALL, I have a query which has the following fields: ROOMS RESNAME ARRIVAL DEPARTURE I have added another field in my query named ROOMS IN as an expression where I want it to return TODAY by looking at another table which has only one record which is the running date table name:RUNDATE and field DATE and then looking at the arrival date and adding two days if applicable to return to the expression field LINEN "TODAY" LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS") it returns 2 days to the other records which is fin...

SUMIF with criteria being a specific cell
I want the criteria to be a designated cell, is that possible? Say A1 thru B7 contain: cat 1 cat 2 hat 3 hat 4 bat 5 cat 6 rat 7 in D1 put: cat and then =SUMIF(A1:A7,D1,B1:B7) correctly displays 9 -- Gary''s Student - gsnu200909 "CSB" wrote: > I want the criteria to be a designated cell, is that possible? Yes. It would be handy if you gave some details, but you could have something like this: =3DSUMIF(A:A,">"&C1,B:B) This will add up the values in column B where column A values are greater than the item in C1. H...

HELP: Add textbox to a frame at run time
Hi All, How can I add text boxes on the form at run time based on what user inputs in a textbox? For eg: I have a textbox "Total Students" , so Once I input a number in that textbox, I want to have that many textboxes on the form during run time. If I input 4 I want four textboxes to be displayed on the form. Thanks in advance ...

Search and replace macro needed
I need a macro that will replace a space with a non-breaking space <b>for the selected text only</b> Hi Greg, Why do you need a macro, when you can use a simple Find/Replace operation? Select the desired range then: Find: ' ' Replace: '^s' Click 'Replace All'. -- Cheers macropod [Microsoft MVP - Word] "GregNga" <GregNga@discussions.microsoft.com> wrote in message news:FBBBB44D-6B2D-4445-9DFA-50EBC8BF20FE@microsoft.com... >I need a macro that will replace a space with a non-breaking space <b>for the > se...

Formula to return the name of a tab?
We have a spreadsheet set up that pulls the minimum value from several different tabs. We are looking for a formula or a way to display, in the next column, which tab that minimum value came from. Does anyone have any ideas? You might check the Help files for information on the SHEETNAME function. Ed "Amanda" <anonymous@discussions.microsoft.com> wrote in message news:54F7CB4F-A621-4DB0-9963-56F0406EE8F1@microsoft.com... > We have a spreadsheet set up that pulls the minimum value from several different tabs. We are looking for a formula or a way to display, in the next...

We Need a United Group of Users Re POS Screen
I was just using our POS today and realized that some items have been scanning without the correct taxes set to the item. We all need to voice this concern to microsoft to update the POS screen. The tax colunm should actually show the tax code so that the cashier call see which taxes are being applied to each item and should print a tax code next to item on sales receipt. If someome makes a mistake enter something it Manager then our cashiers are the next line to catch the mistake. But, they need to be able to see it. The check mark on the screen says that taxes are being applied...

Row names will not move with sort
Each row in my summary table in Sheet1 is hyperlinked to a corresponding table in Sheet2 with additional details. I need to move the rows in my summary table (Sheet1) and preserve the links to and from the details tables (Sheet2). I found that I can move rows in Sheet1 with cut & insert and the hyperlinks are maintained. I can travel from a row in summary table (Sheet1) to details table (Sheet2) and back, even after the corresponding row (Sheet1) was moved. But if I sort the rows in Sheet1 the links from Sheet2 back to corresponding rows in Sheet1 lead to presorted locations. Apparen...

sorts not sorting
OK- Thanks to everyones help I now i have a list i can work with... but there are a few problems.... the zips are all 5 digit and now i have to sort them... the problem is, THEY WONT SORT CORRECTLY!!!!!!! The numbers are not in order... some are but a lot are not... is there anyway to fix this???? i need to sort them by zip code but cant seem to get them to do it correctly.. any ideas??? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You are going to be far better of...

import- number- sort?
I imported this data into a spread sheet- I tried to format it to number, but when I sort it- I get : 37.08 4.33 4.79 4.8 42.56 5.94 Whats up with this? Thanks Brent Hi Brent, Install the TrimAll macro, select the column and invoke the macro from Alt+F8 http://www.mvps.org/dmcritchie/excel/join.htm#trimall Directions to install and use a macro at http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro I expect that you have spaces or CHAR(128) "Required Blank" characters in you data you can check is =LEN(A1) --- HTH, David...

fix cell references
I often have to fix and then unfix cell references using the $ sign to fix and then find and replace to unfix. Is there a menu option to fix column / fix row / fix reference (and unfix). If there is I cant find it. Hi AK not AFAIK, however F4 is the shortcut key for this and takes you through a cycle of A1 - column & row relative $A$1 - column & row absolute A$1 - column relative & row absolute $A1 - column absolute & row relative would using a mixed absolute / relative reference solve your problem? Cheers JulieD "AK" <AK@discussions.microsoft.com> w...

macro in excel to copy from excel to word
i have a database in excel...which i would like to take more advantag of. I also have some forms in Word that I was hoping to be able to get the to be automatically completed using the excel database. Initially thought this would be quite simple.... just record the macr then modify it to suit. This using simple copy from excel and paste into word document, o pasting into form..field..values. I have done a little before with excel macros - to a level of 'usin excel programming for dummies' book. But really do not know where t start now. Have seen this: "Dim appWD As Word.Appl...

date formula #2
Hi i am trying for a way to work out how i can formulate the date, ie. if today is 17 November 2003, i want to import data from date 1 Novemeber 2003 how would i do this and what formulae would i use? any idea ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com =today()-1 GB "jaggosai" <jaggosai.x32sz@excelforum-nospam.com> wrote in message news:jaggosai.x32sz@excelforum-nospam.com... > > Hi i am trying for a way to work out how i can formulate the dat...

Data and Color calculations
I need to find a way to get total for a particular name. I work in a vineyard and we want to track how many of each vine we have. we have our rows and blocks of vines in a spread sheet. I now have to sort by name then count how many of each vine we have. I also assign the vines a color based on when they will be harvested. Can excel do the above type of calculation based on sum or total of a particular color Thanks so much for any help Ron redwards(at)ap.net Hi Ron, Try something like this modified to suit your ranges. Does not count Conditional Formatting colors. Dave McRitchie has a...

Cell.Find
Can anyone suggest an alterantive to Cell.Find or help me with the correct code please. Error message is: Active method of Range class failed The Cell.Find is run in spreadheet X Cells.Find(What:=SCN, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate SCN is a value in spreadsheet Y I have tried being specific with Workbooks("Y").Cell.find etc etc but get error: Object doesn't support this property or method Need to lookup a value in spreadsheet Y and retieve a value in the cell next to the ...

sorting macro #3
I need to sort on 8 cells in a row and on 1500+rows. I can do it one at a time but I know there must be an easier way. Can ANYONE HELP? C3:J3 I need these cells sorted and then down to C1532:J1532 I did create a macro to sort after I selected them but still one at a time. Does it have to be a macro? I can edit a macro but don't really know how to program them. Yes, it has to be a macro. The following should do it. This macro loops through all the entries in Column C, and in each row it sorts the values in Columns C:J. HTH Otto Sub SortRows() Dim rColC As Range Dim i As...