Maintaining A Sorted List In 2003
Jan 27, 2009
I have an Excel sheet with a list of data, which is likely to change frequently and has to remain in sorted order.
Requiring that users of the spreadsheet maintain the data in this way (i.e. sort it every time it changes) is a really bad solution and I'd rather not use a macro if I can avoid it (too much stuff can go wrong).
Is there a good way of doing this? At the moment, I've implemented merge-sort within a sheet, which works, but is a heavyweight solution, taking 6 columns per merge-sort iteration (so 60 columns to sort 1024 rows, 96 columns to sort 65536 rows). I could try to compact it, but my head was starting to implode programming it even with as few as 6 columns.
There must be a better way of getting Excel to maintain an automatically sorted list.
View 11 Replies
ADVERTISEMENT
Oct 7, 2013
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies
View Related
Sep 19, 2006
I have a list of entries on one sheet. This sheet contains part numbers in random order. I would like to draw out the part numbers onto a new sheet, with no repeats and in numerical order. I want this to be done automatically without using a sort command. Also, I would like to avoid VBA if possible.
View 8 Replies
View Related
Jan 4, 2008
I want to copy the first 30 rows of a autofilter list can someone point me in the right direction?
View 13 Replies
View Related
May 1, 2009
Dependent Dropdowns from a Sorted List:
i have one file here..
how did they created validation here.
is there any macros?
i tried..but i didnt able creat like this.
i got this file in this link.
http://www.contextures.com/xlDataVal13.html
i saw this problem in this post..
http://www.excelforum.com/excel-prog...hierarchy.html
View 6 Replies
View Related
Jun 10, 2014
I am currently using a pivot to return and populate the required data but this does not refresh correctly and returns zero values every time this report is refreshed. I am therefore hoping that there is a way to calculate this using formulas .
The following applies:
The attached document has 2 tabs: Date AND Report
The Data tab feeds the report tab
The REPORT tab has 2 reports in it but doing the first one then I should be able to do the second one. If you look at the first table you will see the data that needs to be populated. I have added a highlighted (in YELLOW) row at the bottom of this table which shows you which columns populate what column
Requirement:
I am after something that will populate this table in a descending order - IE: the figures in the DATA tab Column BE should be sorted from highest to lowest. It must be filtered to the top 30 results only and then all the other columns must be populated.
Column H in the REPORT tab should be formatted to [h]:mm
Column F & G in the REPORT tab should be formatted to dd/mm/yyyy hh:mm
View 2 Replies
View Related
Jun 14, 2007
I have a long list of names in Column A sorted alphabetcially. I dont know how long the list is.
I wish to find the start and end row numbers of names that begin with a specific letter, for example if the letter is B I want the first row number that starts with B and the row number that ends with B. Assuming the letter I want to use is in C1, here is what I came up with:
View 12 Replies
View Related
Jul 19, 2006
I created a spreadsheet with several combo boxes that that is linked to other data.
When this data is sorted, the values in the combo boxes are automatically changed.
Is there any way to prevent the the values to change?
View 5 Replies
View Related
Feb 28, 2007
I have a table where alternate rows are formatted with interiror colorindex 15. Macros will result in 1 or more of the rows in the table being hidden (this can be consequtive rows).
Obviously, when a row is hidden (unless that results in 2 consequtive rows hidden) the alternating row format no longer works (ie either 2 grey rows or 2 nofill rows together)
I am struggling with the code to set the format when a row is hidden. I loop through the table to set format, fine if no row hidden. But when row is hidden everything I try to do to take account of the hidden row results in an endless loop when the hidden row is encountered.
View 7 Replies
View Related
Jan 1, 2007
I have a spreadsheet that has tons of entries for revenues and expenses. Some cells on the rows with revenues have a different format than these same cells on the rows with expenses.
The problem is that, when I sort the rows to organize them by date, Excel seems to only organize the values in the cells, screwing up all the formats, mixing the revenues format with the expenses format.
Is there any way to organize the information maintaining each cell's formats?
View 9 Replies
View Related
Feb 2, 2007
if i am copying cell A and pasting it into cell B, and cell B is shaded gray and cell A was plain white, how do I make sure the color of cell B wont change?
View 2 Replies
View Related
Oct 30, 2009
How to include a boolean OR in my SUMPRODUCT formulas.
Software: WinXP SP2 and Excel 2003 SP3
I'll try to type in some data in legible format:
......Col B.....Col C.....Col D.....Col E.......Col F
1...NW OH....WMA.....110%....NW OH...(result)
2...NW OH....SIS.......130%
3...SO FL......PRB........92%
4...NW OH....TRO......104%
I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.
So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.
I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.
I have tried:
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),CALC_PERC)
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),--CALC_PERC)
Also tried both the above formula w/o the "--" in front of any array.
=SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE={"WMA","PRB","SIS"}),CALC_PERC)
Again, the above was tried with and w/o the combinations of "--" in front of arrays.
=SUMPRODUCT(--(CALC_AFFIL=$E1),--ISNUMBER(MATCH(CALC_TITLE={"WMA","PRB","SIS"},0)),CALC_PERC)
Once more with and w/o "--" combinations.
View 7 Replies
View Related
Mar 2, 2009
Cell A1 could contain either % value (eg 50.0%) or could contain a numeric value (eg 50.0). (It's input is written by a macro that performs a sorting function, hence the mix of % and absolutes).
I need to copy the contents of A1 to cell B1 with a simple formula "=A1"
The problem is that Cell B1 will either show 0.5 or 50.0 depending on whether A1 is a % or number, respectively.
Doesn anyone know how I can output in B1 with the format from A1?
View 9 Replies
View Related
Oct 10, 2006
I need to round a number of values to 2 decimal places. Problem is this sometimes results in the total changing as the values after the 2 decimal places make up the remainder. I need to maintain the total. See attached..
Has anyone done this before? Probably something very simple but for some reason it's not coming to me. Well not without vba anyway..
View 6 Replies
View Related
Mar 20, 2014
I have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003
Here is the formula I am using now: =IF(Players!$E3="y",Players!$B3, " ") but I get blank rows for the player that are not playing.
View 3 Replies
View Related
Jun 14, 2013
Running Excel 2003. I have a list in column B, of numerical codes. What I want to do is find the duplicates, and list the duplicates in column D. Is this possible?
View 11 Replies
View Related
Aug 24, 2012
I am using Excel 2003.
I have a pivot table in sheet1 and references in sheet2 like
Code:
='Sheet1'!A1
and so on to copy the whole thing to make it the source data for a bubble chart.
Now, I want to convert the table in sheet2 into a list via Ctrl+L to be able to sort by names with a dropdown menu. Unfortunately, I have to copy all rows from 1 to 1000 to account for possible increases in the pivot table size. This results in blanks in the list and when I want to sort it, I have 990 blanks before the first data rows show up. Not very neat
View 1 Replies
View Related
Nov 12, 2012
Unfortunately we don't have 2010 at work so I don't have the luxury of the use of the duplicate function.
I'm using Excel 2003 and need to remove duplicate names from a list; what would be the best formula to do this.
I've done a countif to identify how many occurrences appear; any other formula if greater than to get to the object of how many staff I have in the list
View 2 Replies
View Related
Feb 24, 2014
I have data arranged in columns A-F. I am wanting to set it up so those groups are all based on the name in column A:
Name Location Quantity Notes Etc.
Joe Likes bread
Hates butter
Jane
Julio
Column A only takes up one space, while the other columns take up 3-4 spaces on average. I am looking to sort column A alphabetically while maintaing the spaces between so that the info doesn't become jumbled.
View 8 Replies
View Related
May 6, 2013
I have a spreadsheet with nested subtotals. i need to sort it based on one of the nested subtotals, but maintain the rows that comprise the subtotals, together with the subtotal.
is there a way to do this? I don't want a macro because then the whole project will be done as a macro. this is just a small part of what i am doing.
sample included. my goal is to sort by column F (ABS value) high to low so that rows 8-13 are together and maintain the subtotal and on top, then rows 34-36 are together and maintain the subtotal and are next, etc.
I am in Excel 2007.
View 1 Replies
View Related
Aug 7, 2008
I have a worksheet that gets autofiltered by the user. I need take the unhidden data and copy it to a new worksheet.
Range("H18").Select
Dim sh As Worksheet
Dim Cell As Range
Dim Txt As String
For Each Cell In Sheets("Panel Check List").Range("H18:H5000")
If Cell.EntireRow.Offset(1, 0).Hidden = False Then
Cell.Copy
Sheets("Query Results").Range("A6").Select
If IsEmpty(ActiveCell.Offset(1, 0)) = True Then
ActiveCell.Offset(1, 0).PasteSpecial
End If
End If
Next Cell
View 9 Replies
View Related
Jan 19, 2012
I can't seem to find a way to copy specific highlighted/selected cells in a filtered list column and paste them outside of Excel 2003. i.e. in Notepad.
It seems to copy all the data between what is selected.
My Filtered list shows rows 5, 28, 35, 40 and 56
If I selected A5, A28 and A40 and select copy
If I paste it into Notepad, it adds A5, A28, A35 and A40
However if I paste my selected copied cell into Excel it works perfect and only adds A5, A28 and A40.
View 2 Replies
View Related
Mar 6, 2012
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
Windows XP
Excel 2003
View 3 Replies
View Related
Mar 26, 2014
I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
Attached File : Productivity Report (HH).xlsm
View 8 Replies
View Related
Oct 28, 2011
I have a protected sheet with merged cells. I would like the user to be able to insert a row and have the formatting (including merged cells) duplicated on the new row. I've searched and seen some different options using VB but I'm clueless as to how to use those. Is there a simple way to do this?
Copying the row and using "insert copied cells" won't work because they get an error because of the sheet protection.
View 1 Replies
View Related
Aug 5, 2013
I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
View 3 Replies
View Related
Jan 10, 2014
I just upgraded to Excel2010 and noticed that if I calculate percentages resulting from other formulas, the result displays as a percentage with 15 decimal places and it ignores the format of the cell. My guess is that since formulas are being used in cells A1,B1, and C1, Excel is ignoring the format. When I hard code the numbers, I obtain the desired result.
Is there a way to change the default of 15 decimal places for formula results involving inputs that use formulas in their own creation? (outside of the Round function in each cell? Within Options?)
The data looks like this:
A1 = formula resulting in 1.76%
B1 = formula resulting in 1.90%
C1 = formula resulting in 1.69%
D1 = C1*B1/A1 resulting in 1.8218147382920100%
The desired result is 1.82%.
View 4 Replies
View Related
May 5, 2008
I'm trying to copy a vlookup to an entire column, I want to look up a value in the cell to the left and compare it to a given table; but when I copy it down it up dates the cell value, but it moves the table down by one row at every row so it's not finding most of the values the further down I go.... what am I doing wrong. I doubt I'd have to re write the formulas in all the 2500 cells I need to look up.
View 3 Replies
View Related
Nov 1, 2011
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
View 4 Replies
View Related
Feb 25, 2013
I am using Excel 2003 and I created an amortization schedule set up for an debt account. I am trying to pull the "Balance Due" from that schedule into another chart based on the current date (these are on two different sheets in a workbook).
For example, this is my 'Amortization Schedule':
Balance
Due
Interest
Rate
This Month's
Interest
This Month's
Payment
[code]....
And I am trying to pull the "Balance Due" from that schedule to place into this chart on my 'Debts' sheet: (based on the current date)
Name
Starting
Balance
Remaining
Balance
Interest
Rate
Minimum
[code]....
For example, if today were 1/15/13, I would want $3,796.34 from the schedule to go where the "x" is on the chart above. What formula would I use to accomplish this?
Also, on a side note, would there be a formula to have Excel pull the "Payment Date" from the schedule into the "Payoff Date" in the chart based on where the row has a $0 Balance Due?
View 2 Replies
View Related