Is it possible to group data together so that it stays together when I sort
it alphabetically? I'd like to make a list for example, Police, Fire and
Ambulance stations. In cell a1 would be Police followed in b1, b2, b3, etc
the names of the stations. Then in c1,c2,c3 etc the relevant telephone
numbers. Then in a 4 would be Fire, followed by b4, b5, b6 etc the names of
the fire stations and then their tel numbers in c4, c5, c6 etc.The same
applies to Ambulance. Then I'd like to sort col A and keep col b and col c
together under the proper emergency service
Hope you can underst...Checking for duplication on rows
In a particular spreadsheet, I would like to verify if the same name is
listed on multiple rows. The name is entered in multiple columns on the
same row, that is acceptable, but I would like to know if the name is
entered on multiple rows regardless of the column. How can I check
Karmen's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30972
View this thread: http://www.excelforum.com/showthread.php?threadid=509495
Presume you're checking some...Keeping Row 1 At Top When Scrolling?
How do i make it so that the top row, row 1, stays at the top of my sheet no
matter how far i scroll down the page?
Select cell A2 and then go to the Window menu and choose Freeze
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"KMc" <email@example.com> wrote in message
> How do i make it so that the top row, row 1, stays at the top of
my sheet no
> matter how far i scroll down the page?
> Ma...Count Rows When Using Auto-Filter
Using the auto-filter function I need to count the number
of rows returned each time I select a new value. I can do
a COUNTA (–1 to allow for the header row), on the column
that counts all the rows but when I use the filter I still
get the total count not a count of the reduced quantity by
Can anyone help?
use the subtotal function with a first argument of 3
as an example.
Make sure the column you do the counta on will have values in all rows.
"Nick" <firstname.lastname@example.org> wrote in message
news:15...Ranking/Sorting more than 3 row's
I have a spreadsheet with 742 rows (of company names).
I have 5 columns of criteria for the companies.
Each column is a different criteria about each company, and has a numerical
rank for each company. 1-~75 (some columns/criteria have no rank for a
I want to create an overall rank of the companies based on how they perform
across the 5 rows. I want the companies with the most 1's at the top and then
those with 1 & 2's and then those with 2 & 2's etc.
Is there a way to sort or rank this spreadsheet of companies?
I would do a sum of the inverses of each of...Hidden Rows
I came accros a spreasheet were the first 8 rows are hidden.
I can select and edit the cells by entering the cell reference into th
Name Box. But I cannot make them visible - the rows have a height o
15. And selecting all the cells then selecting unhide does not work.
Protection has not been applied.
Does anyone have any ideas on how this was done? and how can I mak
michael.a7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3302
View this thread: http:...Switch row with column?
Hello guys, im very new to excel, and im trying to learn. I cant seem
to figure out how to switch my chart around. I have a list of
companies on the top, and down the left i have a list of locations.
How can i make the locations go on top, and the companies down the
jason57gizmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30700
View this thread: http://www.excelforum.com/showthread.php?threadid=503627
Depending on the number of rows you're using--since t...Calculate difference in rows in a query
I have a query that calculates how much revenue is claimed by month per job
but then I have to take the new month less the prior month. The problem is
the data is in row format. I don't know how to subtract February from
January, March from but don't know how. I know how to do it in Excel but not
Access. Please help...
Order Month JTD Clm Variance
101026521 January $511,525 $0
101026521 February $511,525 $0
101029438 January $1,238 $0
101029438 February $3,713 $2,475
101033168 January $21,465 $0
101033168 February $51,460 $29,995
101034011 Janu...excell sorting question
does anyone know how to do a sort for how close a list of numbers com
to a designated cell with a index number in that cell??
sort by being the same or the closest to "44"
results should be:
Message posted from http://www.ExcelForum.com
You can add a helper column...
In the column next to that one (assume A1:A5) enter
=ABS(44-A1) and drag down to fill the series.
Select both columns and choose Data -> Sort and sort
according to the helper column.
"twosix >" <<twosix...Sorting by Text field of numbers
I am using Access 2003.
I have created a database to help me files by "Lot#'s". Our lot numbers for
one of our products is all over the place so I created the Lot # field as a
text field. Ex: 02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.
Why does access put - 1000272925 before 10-06-02-03-05? Does this look right
My thinking is that "10" comes before "1000272925". What affect does the
"-" have in sorting the numbers. Would I be better off to put a space
instead of the "-"? Can anyone help me ...Insert blank rows
Rather than using the control button to select every other row, which can be
very time consuming when you have more than 100 rows, I am trying to figure
out how to auto-insert blank rows between pre-populated rows of information.
Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and
insert module and paste the code in on the right . Change the sheet name to
your sheet and run the code
Dim ws As Worksheet, x as long
Set ws = Sheets("Sheet3")
lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = ...Help with counting rows
I have a datafeed that comes to me everyday via email , every day the
feed had slighty different rows as it has different updated products
i.e. somedays 999 , 1001 etc. What i need to do is run a macro so when
the excel file arrives to count the number of rows and then add the
letter "y" in a 3 new columns . At the moment i have to do it manually
is this easy to do
iLastRow = Range("A1").End(xlDown)
Range("B1").Resize(iLastRow,3).Value = "y"
(remove nothere from email address if mailing direct)
<plm11111@h...Insert row, table to table
As I cannot use INSERT INTO with values specified to WHERE.
I have created an interim temporary table where the record contains two text
strings.Now I want to be able to take those text strings and append to the
correct table where the record is set by an ID number.
First_Name = Fred
Last_Name = Jones
Its a new contact so it is too be added to a customer with an ID = 1375
Now I can't use the following sql statement
strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & "&...Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I
then want to duplicate the existing row and insert it in the line below.
See answers in your other post. Pls don't multi post
"Mindie" <Mindie@discussions.microsoft.com> wrote in message
> Is there a way to insert a row based on a formula. For example, If A5=25,
> then want to duplicate the existing row and insert it in the line below.
...insert 'x' number of rows in excel
I'm looking to insert 'x' no. of rows into an excel document where 'x' is
the result of a 'countA' function.
Is this possible?
only possible with VBA. Would this be feasible for you?
>I'm looking to insert 'x' no. of rows into an excel
document where 'x' is
>the result of a 'countA' function.
>Is this possible?
Yes VBA is feasible
"Frank Kabel" wrote:
> only possible with VBA. Would this be feasible for you?
> >-----Original ...Preserving formulas when using sort function
If I have a two columns of data say, A and B and the are added together in
rows in Column C eg
A1 + B1 = C1
A2 + B2 = C2 etc
If I then move the contents of cell A1 and put it in say A23 the value of C1
does not change because the C1 become A23 + B1.
However, if I sort Column A then the value of C1 changes and the formula
appears to be wrong because it now takes the new value of cell A1
How can I make sure that when the sort takes place that wherever the value
in A1 ends up it is still added to B1 to create a value in C1?
Any help appreciated
Why aren't you moving th...Sort numbers with text in ascending order
I am having a difficult time sorting the following list:
MY List How Excel Sorts I Need
2148-00 1472-00 171B-00
1480C-00 1480B-00 171C-00
1480B-00 1480C-00 1472-00
1472-00 171B-00 2148-00
171C-00 171C-00 1480B-00
171B-00 2148-00 1480C-00
I have tried formatting numbers as text and other options. I am at my
wits end now. Is this sort even possible? Please help.
Where is the logic to your sort please?
If you can identify that then a sort may be possible! ...Excel opening a "phantom" worksheet at random times
I have a user who will have a phantom Excel spreadsheet, entitled Object,
open at random times while she is trying to open another spreadsheet.
This Ojbect spreadsheet will stay open, and she can open other spreadsheets,
but the only thing that will open is the Menu bar, toolbars, and the status
bar. The file itself doesn't open, the space is empty and the user can see
When this happens, the user has to shut down her machine, not a restart,
before she can continue on in Excel.
...Offset, sum down to the first blank row
can anyone write the formula to sum down to the first blank row it encounters?
In the sample data below I want the xx to sum only to 60
Assume the word sum is in cell A1 and the xx is in cell B1.
p.s. would this formula be considered "volatile" and therefore "risky"....if
so, what's the risk. thx.
"Tami" <Tami@discussions.microsoft.com> wrote in message
> can anyone write the formula to ...Numbers won't sort correctly.
I have a column of numbers that were imported from a web site and they won't
sort correctly. I have tried reformatting the cells as general numbers but
that doesn't change anything. I discovered that numbers below 100 have a
space or invisible data in front of the number. If I remove it, the number
then shifts to right alignment in the cell and will sort as a number. Is
there any way I can remove these spaces from all numbers at once instead of
going down the column row by row?
Type a 1 into a blank cell on your sheet. Copy the cell. Mark the range of
your data...Report Writer Dexterity Error
I get a "Dexterity Runtime has encountered a problem and must close" error
when I try to run one particular modified report. The error occurs when I
add a sort function in Report Writer. When I remove the sort, the report
runs - without error. I am on GP 9.0.
...How to create a random number between a range?
I need to generate random whole numbers between the range of 21 and 90.
I started with this:
with the cells formatted to zero decimal places.
how do I limit the lower number?
Any help here will be appreciated.
Thanks in advance
=RAND()*(b-a)+a taken from the Excel help file. So in your case
RAND()*(90-21)+21. If you want integers only then =INT(RAND()*(90-21)+21
In Excel 2007, = RANDBETWEEN(21,90)
"Dave" <dave@accessdatapros> wrote in message
>I need to generate random whole numbers...Moving Column Sums to Rows
What is the simplest way to enter a sequence of values such as =SUM
(A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?
What is the simplest way of converting simultaneously such values to
I could only find a slow and clumsy method by putting the formula in a row,
right dragging, then using F4 on each individual cell before using paste
special with transpose.
> What is the simplest way to enter a sequence of values such as =SUM
> (A1:A100),=SUM (B1:B100),=SUM (C1:C100), etc. into a COLUMN?
=SUM(OFFSET($A$1,0,ROW()-1,1,100...Generate random sequence based on probability
I have a list of events and the probability that each will happen. Example:
Event A 12%
Event B 20%
Event C 2% ...and so on.
My list is about 20 entries long. What I want to do is to have Excel
randomly determine what event will happen in what order, based on the
percentage chance that it will happen. So I want 20 cells in another column
with the results looking something like this:
Event B ...and so on. And be able to recalculate upon refresh.
Thanks in advance!!
Assuming the list is in A1:A20 - In cell C1 type:
=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,ROW()),$...Excel won't allow access to some rows!--- Very strange!!
I have a strange problem with one of my spreadshteets. I am not able
to access or see any rows 1 to 31. It is as if the "hide" function is
in effect. I have tried the "unhide" function, but no use. It is as
if I am locked out of these rows! I don't have any "freeze" function
active with respect to the unaccesible rows.
Anyone here knows what is going on? thanks
When you try and unhide select the whole sheet by clicking the border above
and to the left of cell A1 and then try unhide rows and then also try row
Hope this helps