Determine Row Number Of Last Value In Column

May 15, 2009

Trying to do a simple workbook here, with hopefully no macros/VB script.

Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.

The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.

The cell I'm trying to create is part of a 'stats' section.

View 7 Replies


ADVERTISEMENT

Sumproduct: Determine The Number Of Unique Names In Column

Jan 22, 2010

I have 1500+customer names in column C7 of table. I'm looking for a SUMPRODUCT formula that achieves the following 2 logical goals. First, it needs to determine the number of unique names in column C7. It can't just count the rows because many customers appear multiple times in the range. So, I have been using the following formula and it works great:
=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&"")))

Second, I'd like the formula to tell me how many of the unique names in C7 correspond to public companies. Column C8 of my table indicates whether or not a company is public ("Yes") or private ("No"). So, I've been using the following formula and it seems to deliver the right result:

=SUMPRODUCT(
((Table[C7]<>"")/COUNTIF(Table[C7],Table[C7]&""))
*
(COUNTIFS(Table[C7],Table[C7]&"",Table[C8],"Yes")>=1))

But the added COUNTIFS statement seems very unwieldy and indirect to me. Why do I have to examine every instance of a particular company's name when the answer could be determined by looking at any instance of the name -- after all, each company in C7 is either public or private, and has the same corresponding value in C8 (whether "Yes" or "No") everywhere that its name appears in C7!

So that's my first question: is there a clearer, more efficient way to achieve the same result? My second question has to do with toggling the public company test.
What if I create a special cell, say A1, that will always contain either the word "On" or "Off"........

View 4 Replies View Related

Determine If Last Character Is Number Or Not A Number

Oct 23, 2008

I'm trying to determine if the last character of a cell is a number or not a number. So in a cell I may have a string like:

ZXC123

or

ZXC123A

If the last character is not a number I want the letter returned. So I thought I could use a formula like: =IF(NOT(ISNUMBER(RIGHT(D88,1))), RIGHT(D88,1),""). However for the string ZXC123 it still returns '3' so anyone have any ideas how I can do this?

View 2 Replies View Related

Determine If Number Is A Prime Number

Sep 25, 2009

I have a column of random number in Column A and need a formula to identify if any of the numbers in Col A is a prime number. If it is, then say "Prime" in the cell adjacent to the prime number in Col B. Eg if A12 has a value 17, then cell B12 should say "Prime".

View 9 Replies View Related

Determine If A Number Is Even Or Odd

Jan 20, 2007

Determine If A Number Is Even Or Odd. i would like to Determine If A Number Is Even Or Odd...?

View 3 Replies View Related

Determine Page Number

Apr 29, 2008

Is it possible to programmatically determine the page number on which a specific cell will print?

View 14 Replies View Related

Determine Row Number Of A Specific Value

May 13, 2007

I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.

View 9 Replies View Related

Determine Next Number In Order

Dec 21, 2007

We have list for entries that is shared among few people. In that list, we manually add number for the entry. So if I input 99 then, next person should add 100 and so on. I would like to automize this, so say If I use this, then no one else can use it in the mean time and then when number is input, it should disallow if same number is input and it should automatically enter the next number.

View 3 Replies View Related

Determine If Range Is Divisible By A Number?

Aug 24, 2012

I have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.

All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...

My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.

4.1 Mod 4 returns 0
.5 Mod 4 returns 0

I just need it to return zero if the number is evenly divisible by 4.

I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.

View 3 Replies View Related

How To Determine Number Of Pages To Print Out

Sep 2, 2013

I have client sheets that need to be printed at the end of each month. Sometimes I need 1 sheet, sometimes more. I have a macro which pops up a userform in which I can select the client I want to print and the month. I would like to be able to have the macro determine how many pages need to be printed so I get all the pages and do not end up with any blank pages. Is this possible?

View 1 Replies View Related

Determine If Cell Is Number & Round

Jul 27, 2007

It's been awhile since I've done this...and I tried searching for the solution but to no avail.

I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:

Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

View 6 Replies View Related

Determine If Cell Value Is In Number Array

Feb 16, 2008

1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"

Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.

View 2 Replies View Related

Determine If Whole Number Exists Between Minimum And Maximum Value?

Feb 14, 2014

I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:

A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE

View 7 Replies View Related

Determine Number Of Files In Dynamic Directory?

Feb 6, 2014

is it possible if I have a directory like the following

[Code].....

with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?

View 2 Replies View Related

How To Determine If Number Formatted Cell Is Empty And Not 0

Feb 21, 2014

if the active cell is formatted as number and I write

x = Cells(Row, Column).Value

and the cell is empty or blank, x will be set to 0.

How can I determine that the cell is blank and not actually 0 ?

View 9 Replies View Related

Determine Number Of Workdays Per Week In Given Month

Dec 6, 2013

Is there a formula I can use to determine the number of work days in each week for a given month. The work week would start Sunday, with Friday and Saturday as days off. Each week would be in their own row.

i.e.

January

Week 1: 2 days
Week 2: 5 days
Week 3: 5 days
Week 4: 5 days
Week 5: 3 days

[Code] ..........

View 4 Replies View Related

Determine Whether Cell Contains A Number Formatted As Text

Jan 2, 2009

How can I interrogate the contents of a cell to determine whether or not it contains a number formatted as text? I intend to run a .value = .value over each such cell to convert it to a number format, but I only want to do this to relevant cells.

View 9 Replies View Related

Determine Number Of Logical Pixels Per Inch

Jan 18, 2009

How can we determine Number of Logical Pixels per Inch based on the system configuration using API and return the value to Cell A1 of Sheet1.

View 9 Replies View Related

Determine Row Number Of Active Cell In CurrentRegion

Sep 16, 2008

I'm trying identify the active cell row number in a current region for viRows below. Currently I'm getting the active cell row number on the total worksheet.

With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Range(ActiveCell.Row).Count
End With

View 2 Replies View Related

Determine Row Number For Data Found With Vlookup In Vba

Apr 5, 2007

DETERMINING ROW NUMBER FOR DATA FOUND WITH VLOOKUP IN VBA

I have a user form with a combobox (called STKCODE) that calls up a vlookup routine to search for this item in a named range on a worksheet and retrieve related data to display on the userform. All this works fine.

What I would like to know is how to easily determine the row number on the worksheet, representing the row the data is stored in, if thats at all possible....

View 9 Replies View Related

Determine Next Invoice Number Stored In Range

Oct 9, 2007

I am trying to add a number to invoices (invoice numbers) on a UserForm that is used to make and/or modify scheduled service records for a cleaning business. We fill out the schedule well in advance of when the jobs are scheduled to be done, some customers are on a set schedule such as once a week, everyother week or once a month. Some just call in when they need cleaning. Each day has 27 rows set aside for possible customers (most are not used). We make out as many invoices in advance as is possible and fill-in the rest as they call-in. This leaves a lot of gaps between days

I have an auto-advancing formula tha takes a helper column to give me invouce numbers when there is a customer listed in column B and blanks space where there is no customer listed. This does make for volitile invoice numbers.

Formula in row 3 of the invoice coulmn "A": =IF(B3="","",H3)
Formula in row 3 of helper column "H": =IF(B3="",H2,H2+1)

The helper column only advances if there is a customer listed in column B of that row otherwise it repeates the last number. The invoice column only displays this helper number if there is a customer listed in column B of that row. I can't figure out how to get this to work on a UseerForm with vba.

View 8 Replies View Related

Determine Version Number Via Macro Code

Apr 17, 2008

This is probably a simple question, but I couldn't find the answer by searching...

I have a macro that I wish to run which calls one of two different macro's depending on if the user has Excel version 2007 or if they have an earlier version.

This is to overcome 'Save as PDF', which only works in Excel 2007.

View 3 Replies View Related

How To Determine Number Of Pages In Word Document Using EXCEL VBA

May 31, 2014

I'm trying to copy the content of a word document (File A) to another (File B) using Excel VBA. File A has about 100's of pages (not sure of the number as it varies) and this needs to be split to different files, each having 15 Pages.

Below is my code, where I'm able to select the content of the first Page and paste it in the target folder, but not sure how to determine the number of pages in word using excel VBA.

Note: Copying the content should be done, page wise only.

[Code] .....

View 1 Replies View Related

Counting In Excel - Determine Number Of Times Certain Item Is In Columns

Feb 7, 2014

If I have 1,000 entries in a column is there an easy way to determine the number of times a certain item is in the columns. For example if field A1 is "qwerty" I wan to see how many times "qwerty" or field A1 appears in the entire column?

View 2 Replies View Related

Automatically Determine A Dollar Figure Based On The Number Of Points

Dec 8, 2008

I have a spreadsheet that I am trying to automatically determine a dollar figure based on the number of points a cell has. A if the first set of a goal, B is their actual goal and C is the actual number of points. I am trying to determine the dollar figure for D. For each point between A and B the person gets .50cents. For every point above B and up to C the person gets 1.50. In the case below if the person doesn't reach B than they would only get the 50 cents from A up to C.

A= 2537, B=3252 C=2820

I have attached my spreadsheet and everything is perfect except on one point. I can't figure out how to tell the spreadsheet if it is a negative number to give me zero.

View 7 Replies View Related

Determine A Specific Date Based On A Number Of Business Days?

Feb 11, 2010

I'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.

View 5 Replies View Related

Formula To Determine Number Of Standard Deviations Based On % Of Population

Sep 14, 2005

The following are a few of generally accepted values: ...

View 8 Replies View Related

Create Formula That Will Determine Number Of Orders For Specific Customer?

Aug 29, 2012

I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.

Here is what I was trying to use, but it was coming up with "0" for everything:

=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"

View 1 Replies View Related

Determine Used Column And End Of Row

Sep 14, 2012

Right now in my code I'm using the below section to determine the column in which to search for dupes, and the last column of data for that range. However, I make the user select only part or one column through IF statements above, so there's no need for the user to input this information. I'd like to replace the choice part with and automated routine that determines the DupeCol from the column section selected, and determines the rowend from the last used cell to the right in the rows in that column selection. But I don't know how to do that and keep the values as strings.

VB:
Dim RowEnd As String, DupeCol As String
Dim LastRow As Long
DupeCol = InputBox("Type in the letter of the column you want to look for duplicates in.", "Fill in the Info")
RowEnd = InputBox("Type in the letter of the last column you want colored.", "Fill in the Info")

View 4 Replies View Related

Determine Which Column Has A Filter?

Jan 30, 2014

I would like to determine using VBA which Column or Range-of-Columns have a filter applied.

I need to change filters, depending what part of the sheet the user is looking at.

Not sue why, but in my IE browser, I cannot Paste code anymore .

Code:

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved