Finding a certain row from rows of intervals?
Got a question, mostly curious, and doubting this could be done withou
Macros or similiar, but hey, I'll give it a go here, someone might kno
Say you have two columns with a number of rows, each row contains a
interval in the form of "min-max", and you want to find the value t
the right of the correct interval, like so;
1 1-7 1
2 8-25 2
3 26-29 3
4 30-40 4
5 41-57 5
Enter Number: [A number that you enter]
Number Returned: [Calculates corresponding number from the interval]
Now, if you enter number 34, it should return "4" (...copy rows with values
First step is no problem:
in Sheet1 col AI this formula is inserted with VB to add age groups
7-15";OM(OCH(Age>15;Age<26);"c. 16-25";OM(OCH(Age>25);"d. 26-";""))))
Col is autofilled to last used row.
In next step I loop thru these rows to find certain rows and copy them to
Sheet2. Problem is that formula on each row is then copied. Instead I need it
to be values only!
Needs to work in Excel 2003 and 2007. Sheet1 can have >20 000 rows
Any s...Adjust row heights of non-contiguous rows.
Looking to adjust row heights of a 185 row w/sheet.
First I use: Rows("4:184").RowHeight = 12.75
I expect this should work quickly and efficiently.
I don't quite know what to do with the "exceptions" rows.
I could use: Rows("6:6, 9:9, 14:14, 18:18, 24:24, 30:30,
ETC......").RowHeight = 3.75
But there are over 30 rows that need to be referenced. The double row
references become cumbersome and don't really add to the clarify of the code.
I'd like to use something like:
SRow = Short Rows
Short ...Specifying Cells to Count?
I am having trouble adding in Excel... I have a list of IDs in A:A, and
a list of numbers in B:B. For example:
Now, I need to calculate the number of instances of 0.1 corresponding
to the value of 1 in A:A, the number of instances of 0.1 corresponding
to 3 in A:A, and so on. I've tried FREQUENCY(), COUNTIF(),and a few
I know the number of instances that 1,2 and any other number shows up
in A:A, and I think I can use COUNTIF() -- however, I can't find a way
to dynamically specify the number of...count number 12-17-07
Is there a way to show a count of records with the same part numbers.
Create a new query in design view.
Add the table with the part numbers.
Click on the Totals button (looks like a "sum of" button from Excel).
Add the [PartNumber] field ... leave the aggregation as "GroupBy".
Add the table's primary key field ... change the aggregation to "Count".
Microsoft Office/Access MVP
"Jeff Klein" <jklein@nospam> wrote in message
> Is there a way to show a c...Signature #4
I'm trying to make signature in outlook express but I
don't have the ability to make it in a good format. I can
see others is sending me theire mails with a very nice
signature and formatted one. How can I do this in outlook
Express.,? Is it applicable or I have to use the outlook
to have it.
I appreciate your reply.
>I'm trying to make signature in outlook express
Ask in a newsgroup that deals with Outlook Express.
3290 Patterson Ave. SE, MS 1B3
Grand Rapids, MI 49512-1991
Brian.Tillman is the name, smiths-aerospac...auto expand rows
I have workbook with two sheets. It's an order form that needs to
automatically insert rows as each row gets populated.
Sheet 1 is the "order build" sheet in which a user will enter a part
number and that part's description, price, & 8 other parameters appear.
I am successfully using vlookup to pull data from the 'data sheet".
| part number -|---Qty --|---Price -|---Ext. Price--|-- Description |
| B345 ...Expand and Shrink Rows and columns to hide information.
I have a spreadsheet with rows 1 & 2 and columns numbered 1 & 2. In column 1
I have the ability to click a plus sign (+) to expand information in hidden
rows. Along the top I also have the ability to click a plus (+) sign in
column 1 to expand hidden columns. It appears that column 2 denotes those
rows to be hidden by a "."
Ok how do I set this up?
Data>Group and Outline
Gord Dibben MS Excel MVP
On Tue, 5 Jan 2010 07:29:02 -0800, jf1847 <email@example.com>
>I have a spreadsheet with rows 1 & 2 and columns number...SumProduct/SUMIF? #4
why the double hyphen?
what does this do? when I take these out the formula it returns zeroes
when I leave them in it works....? can someone explain this or give m
a link to a web site that does, thanks
kkondrat1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=600
View this thread: http://www.excelforum.com/showthread.php?threadid=26650
"kkondrat1" <kkondrat1.1...Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count
the number of cells in this one column that contains dates. How can I do
If the only cells that are in that column that are numeric (like dates), you can
(Dates are just numbers formatted nicely in excel.)
> Column J contains dates in some cells, and text in others. I need to count
> the number of cells in this one column that contains dates. How can I do
...Fill Down #4
I have created a macro that sorts our bank download into each
individual bank account!
On this download i have to put in a custom formual into cell I2 and
this has to be copied down to the end of the data, however each day the
amount of data changes and the problem i am having is that excel
remembers the cursor position not the keys pressed ie End and Down
arrow to get to the bottom of the list!
Is there any way I can get excel to remember the keys i press in Vba?
ieEnd and Cursor Arrows?
-----------------------------------------------------------------------...insert rows automatically
I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
I use a code which was previously posted that creates a box asking how
many rows I need. This works fine but the problem is that people often
input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.
Here's the cod...Freeze header row at top of each page and exclude it from sorting
In Excel XP on Windows XP, I need to freeze the first row (header row)
so that it will always appear at the top of every page and its data will
not be included in sorting. Only data below the first row should be
sorted. How do I do that? At present whenever I sort by any column, the
header row gets sorted along with the other data. That is unacceptable.
"classified"@" <""(classified\"@)"> wrote in message
> In Excel XP on Windows XP, I need to freeze the first row (header row)
> so tha...counting
I HAVE A SPREADSHEET THAT HAS SEVERAL ENTRIES THAT ARE
THE SAME EX COLUMN 1 IS TELEPHONE NUMBERS COLUMN 5 TELLS
ME IF ITS A B( BUSINESS) H(HOME) C(CELL) I WANT TO INSET
A MACRO AT THE END OF EACH COLUMN THAT WOULD ADD HOW MANY
TELEPHONE NUMBERS ARE IN THE COLUMN THAT ARE UNLIKE ( AS
IN COUNTING EACH DIFFERENT ENTRY AS 1) AND ON THE COLUMN
5 HOW MANY UNLIKE B,H,C GO WITH COLUMN 1 WITHOUT HAVING
TO COPY THE TWO COLUMNS TO ANOTHER SPREADSHEET - FILTER
ADVANCE UNIQUE RECORDS ONLY - I NEED A MACRO THAT I CAN
ADD AT THE END OF THE COLUMNS THAT WOULD DO THIS
Can you repost ...Pivot table problem #4
I am encountering some problem with pivot table's data
after i refreshed the pivot table.
I have a template with grouping and setting done, however,
when i paste new data into the raw data worksheet and
refresh the pivot table, the grouping in my pivot table
dissapear. But, when i check from the drop down box, i can
see my grouping but it doesn't appear in my pivot table.
Anyone know why?
Could it be that in the new data that you add to your base
data table there are items which fall outside of the
groupings you have set?
I have had problems with this in the...Haveing problems inserting a row
Here is the problems
I am working off of 3 different workbooks that are linked together
Every time that I insert a new row, the forumlas do not update. I
there a way for me to insert a new row so that it will change th
forumlas?? I am hopping this could be done?
thanks for any help you can give me
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
On Thu, 4 Dec 2003 22:0...Counting Weeks or Days
How can I enter two dates and have Excel calculate the number of weeks or
days between them?
Thanks for any help.
Begin date in A1, end date in B1...........
this will give you days difference (format cell for general)
and this will give you weeks..........
Vaya con Dios,
"Kobra" <firstname.lastname@example.org> wrote in message
> How can I enter two dates and have Excel calculate the number of weeks or
> days between them?
> Thanks for any help.
Chuck wrote &l...Removing duplicate rows
I have inherited a spreadsheet which has lots of duplicated rows. However,
some information is missing for certain fields. For example one row will
Firstname (Jim), Lastname (Jones), Phone (1213111) and the duplicate will
just have say the Firstname (Jim) & Lastname (Jones).
What I would like to do is remove the second record from the spread sheet.
There are many instances of this throughout.
Is there a way that this can be achieved?
Thanks for any help
You could use a helper column to do a counta of all the active cells in each
row, and then sort and delete all rows that...From Row to Column?
Hello all, I have a large spreadsheet of data, unfortunately when I set it
up originally I put the data into rows (I.E. left to right) I'm trying to
correct that now, Is there any way I can highlight a row of data and move it
into a colum, in the same order it is in the rows?
Yep, select all the cells in your row, and copy then select the first cell
in your destination column and select "Paste Special" then transpose in the
Paste Special dialog and ... job done!!
"confused man" <email@example.com> wrote in message
news:9cadnVInq98Y6ZjcRVn-u...Deleting rows meeting criteria
I know that this question has been asked but I can't seem to fit the answers I have found with my problem (because of my ignorance)
I need to delete all rows that have the words Employee, Spouse or Child. Then I want to delete all empty rows. I have a macro that deletes the empty rows but if I could do it all in one, it would be better
Thank you very much
Vickie, see if this will work for you
' This macro deletes all rows on the active worksheet
' that have Employee, Spouse, Child, or is blank in column A.
Dim rng As Range, cell As Range, del As Range
Set...missing cell rows (jumps from 2 directly to 9)
I wonder why a excel file I received have some rows invisible. It
jumps directly from 2 to 9 and no 3 to 8? It's not hidden as hide is
It could mean that the worksheet is filtered. Do you see arrows on any of the
"header" cells (row 1???)
Look at the row numbers. Are they grey or blue?
If they're blue, it's autofilter.
Try this (xl2003 menus):
I'm not sure what "hide is grayed" means, but if it's not autofilter, then try
F5 (same as Edit|goto)
and hit enter
Then ...Contracts in CRM 3.0/4.0
In CRM 3.0, the contracts you can create all depends on that they have been
invoiced BEFORE allotments are made to the contract.
In a service organisation, it's needed to create contracts that can be
invoiced after allotments are made to it.
The customer calls in and has a problem with whatever.
A Case is created and related to the contract.
Activities are created to the case and completed.
The Case is Solved and the allotments are made to the contract.
At the end of the month, the contract and the allotments are invoiced to the
How can I set this up?
Best Rega...Formula to count text and alert me if a text appears more than twi
I have a column of text and I need to be alerted if the same text appears
more than twice in the column. The column changes on a regular basis. e.g.
"ALERT AN ITEM APPEARS MORE THAN TWICE"
Select the column (say it's Col D), use Data/Validation, change Allow Any
Value to Allow Custom, enter =COUNTIF(D:D,D1)=1, then choose an appropriate
error message on the error tab if you wish. This won't change any already
existing duplicates, but you can find those by tools/auditing/show auditing
toolbar, then click the "circle Invali...Add totals at end of the report with unknow number of rows with VB
I have a report that I download into Excel each day. The amount of rows vary
each day. I want to add to an existing macro code that will add at the end
of the report (regardless of how many rows there are), in column the text of
"Total" and then add the sum of column B (currency) and place that total in
column B on the same row as Total. On the next row I would like to add the
text "Items" in column A and then have the count display in Column B. I just
don't know the code to show this because of not being able to designate what
row it will display on. I coul...Refreshing the displayed rows
How do I get excel to refresh the displayed rows when a filter is applied?
If I create a filter that say show COL1 > 2
it should show me 3 and 4
If I replace 3 with 0
It then shows me 0 and 4
But I would like to "refresh" so it only shows me 4
How can I do this?
The following code can be pasted onto the worksheet module (right-click
the sheet tab, choose View Code, paste where the cursor is flashing)
It will filter column 1, using the existing filter criteria.
Private Sub Worksheet...