Excel 2007 :: Find Last Value In Column Regardless Of Number / Text?
Nov 1, 2013
I've used the below, for example in Column E, the actual data starts in row 4, so I can't use the full E:E syntax:
=IF(ISERROR(INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000))),
INDEX(Data!E$4:E$65000,MATCH("z",Data!E$4:E$65000)),
INDEX(Data!E$4:E$65000,MATCH(9.99999999999999E+307,Data!E$4:E$65000)))
The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)
However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.
Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
View 9 Replies
ADVERTISEMENT
Aug 18, 2013
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead.
View 5 Replies
View Related
Mar 2, 2012
I am trying to find a way to see if a range of cells contain (not exactly, just contains) the text in another cell.
For example:
A1 Fred Flintsonte 2012 B1 Barney Rubble C1 Barney Rubble
A2 Barney Rubble 2012
If a cell in range A1:A2 contains text in B1 (CASE INSENSITIVE), then C1 equals B1
I've tried entering this formula in C1, but it does not work:
=IF(ISNUMBER(SEARCH(B1,A1:A2)), B1, "")
View 5 Replies
View Related
Nov 15, 2012
I have a text box within a chart tab which is populated with text values from a worksheet within the workbook. That part works fine however i want to be able to achieve the following example:
Current Text: Component Name - Notes
if there are notes against the component name i want the name to remain in black and the notes to change to blue
Aim: Component Name - Notes
If there arn't any notes then for the component name and the "-" to change to a light shade of grey
Aim: Component Name -
There are 26 components, each on their own lines in the textbox so im thinking either a loop where the cell reference is variable eg. ("CY" & i) or using the .find with a variable.
My code so far is:
Code:
Sub Chart_Notes_NTCA()
Dim i As Long, j As Long, Counted As Long, Total As Long
Dim Ans As String, Notes As String
Dim Cht As Chart
[Code].....
View 9 Replies
View Related
Feb 13, 2012
I am using excell 2007 and 2010
I need to find the average numbers on sheet 1 column D. Column D contains numbers as well as Blank cells. The info will be presented on sheet 2 in a monthly view. In other words i need to ssearch awithin a date range and determin the average of those numbers. This works on some months but return a DIV/0 erro most of the time.
=AVERAGEIFS('2N Stats'!$I:$I,'2N Stats'!$E:$E,">=2/1/2011",'2N Stats'!$E:$E,"
View 2 Replies
View Related
Jul 16, 2014
I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)
Part #|Inventory ID
1743|213,221
1864|10,40
1948|1170,1180
5265|100,104,107,10004
Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.
When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.
Here's what I'm left with:
Part # Inventory ID
1743 213,221
1864 10,40
1948 11,701,180
5265 10,010,410,710,004
The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.
When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.
I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.
How to best retain the format of my Inventory IDs.
View 6 Replies
View Related
Feb 7, 2012
I'm using excel 2007, here's what I need.
I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?
View 2 Replies
View Related
Sep 23, 2013
I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select
[Code] .....
View 2 Replies
View Related
Jan 4, 2014
Please find attached a daily copy of a spreadsheet that is used to monitor train running times.
The columns on the left - "Serv No, Serv Start Date,Train No:, Scheduled Arrival, Scheduled Depart" are provided to us automatically and the underlying cell formatting/formula cannot be changed. This information is cut directly from a daily report sent to us the previous day. The cell formatting for the start date is custom - dd-mm-yyyy hh:mm.
The sheet is usually locked out with user access only to the left hand side to cut and paste train times in and the input columns on the right hand side. User has no requirement to adjust any details in the left had column apart from to add additional trains at the bottom is need be. I have left the loaded sheet blank and complete with all underlying cell formatting, conditional formatting and formulas intact for you to play with.
Both sheets essentially do the same thing so whatever formula changes are made to the empty sheet can be adapted for the loaded sheet.
For simplicity, I will focus on the empty sheet. What I am mucking around with is having the user input the train arrival time in the "Actual Arrival" column - (column F)
What I am looking at then making occur is the spreadsheet automatically filling the adjacent columns with the appropriate text and calculations.
To do this excel needs to compare column the number (cell input is text formatted) as a number with the arrival time in column G. It then needs to calculate if the train is Early (16 minutes or more before scheduled arrival), on time (+/- 15 minutes either side of scheduled arrival) or late (16 minutes or more after scheduled). It then needs to automatically place the letter "E", "O" or "L" in column K and the time differential in column L.
My problem is two fold.
Firstly, I cannot seem to make the spreadsheet automatically enter a letter into column K depending on the above conditions.
Secondly, I have had limited success in having the spreadsheet compare column J with column F and working out the differential. However, this only works if the time remains linear. IE only if the train runs on time or late. It gives an incorrect time differential if the train is early. I will attach this spreadsheet tomorrow as an add on to this post.
DAILY TRAIN RUNNING.xlsx
View 3 Replies
View Related
Feb 19, 2014
I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
View 14 Replies
View Related
Jan 22, 2013
Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
View 7 Replies
View Related
May 23, 2014
file storage
1. Look at the "Days so far" section
2. Ignore cell C1
3. All the other cells in that row are shaded blue. Look at these.
4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.
5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.
6. For example, we begin in C1 with a count of 12 days so far - this was manually entered
7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.
8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.
9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.
P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.
View 5 Replies
View Related
Jul 14, 2014
Using Microsoft Excel 2007, VBA
I am trying to display an error message if a range is missing '0' or '100', or is empty.
I figured out how to display error when the selected range is empty, but I cannot figure out how to find if a particular value exists within a range and return an error if there isn't.
View 2 Replies
View Related
Nov 21, 2012
I have a table with names of employees in Column A, the data for each employee is written in columns B, C and D.
I'd like to be able to type in this data in cells E12-E14 and have Excel bring up all of the names in the table that are associated with this data, and preferably separate them with commas.
I'm working with Excel 2007, without VBA/Marcros.
Please see the example file: find_name_example.xlsx
View 7 Replies
View Related
Apr 16, 2013
I have several sheets I use for MI and most have similar amounts of data in, ie, a tab for each month with out 15-20 columns and upto 10,000 rows, then a couple of summary sheets with filtering and calculations in.
One of my sheets is currently 16Mb, when the others are about a third of that, with similar amounts of data.
Is there a way to find what is causing the extra space to be taken in this sheet?
Or does this seem about right for the amount of data?
I'm using 2007.
View 3 Replies
View Related
Feb 25, 2014
I'm running Microsoft Office 2007.
I have 2 separate tables.. both a list of contacts of sorts.
In one table I have a list of all my clients (table A), in the other I have a list of "preferred" clients (table B).
The contact details etc that are stored in table A are more comprehensive to those in table B.
Effectively, I want to search for all of my preferred contacts from table B and filter for them in table A.
Then ideally I will sort alphabetically and just cut and paste the details into my preferred client spreadsheet.
I had a look online and there was something about creating a "relationship" but I don't think excel 2007 allowed me to do it. I certainly hadn't heard of powerview or powerpivot that they were using!
View 6 Replies
View Related
Jun 22, 2012
If I have a cell which begins with a few spaces and then has a character, how do I use the find function to see how many spaces until some character is present in the cell. The * does not work with this.
Excel 2007
B
4
a
Sheet1
View 6 Replies
View Related
Sep 26, 2012
I'm running Excel 2007 on Windows 7.
I've got a crude code which searches for multiple alphanumeric data. The code is below:-
Code:
Cells.Find(what:="1kp", _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
[Code]...
I want to make this code more efficient by searching for either 1kp, 2kp or 3kp in one process & 'doing the same thing' at one time. How do I achieve this?
Explained slightly differently, the code should look something like this:- Find 1kp or 2kp or 3kp. Then do something with the found results.
View 1 Replies
View Related
Jul 29, 2013
I am wanting to find rows that with unique values in the first column, and then take those rows and display them in a more readable format. The section titled input is an example of information that would be pulled from a query into Excel. The Desired Output section is how I would like the info to be displayed. If this is possible without VBA then that is even better.
Excel 2007
A
B
C
D
[Code].....
View 2 Replies
View Related
Aug 15, 2013
Why does this code no longer work? It gives me the error code Cannot find project or library and MyMsg = is highlighted in blue. This worked in 2003, but does not seem to like 2007 version of excel.
Sub Send2()
'This is the "Send to XX" button
MyMsg = "Did you remember to name and save this file to your computer?"
Response = MsgBox(MyMsg, vbYesNo, Attention)
Select Case Response
Case Is = vbNo
[Code] .....
View 9 Replies
View Related
Dec 2, 2013
I am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
View 2 Replies
View Related
Nov 27, 2011
Autofill column B with sequential values based on whether value in column A changes its value.
I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.
This is what I imagine.
A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"
I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.
I'm using MS Excel 2007 in Windows 7.
View 2 Replies
View Related
Jan 9, 2014
I have two spreadsheets. one contains a master list of computer names. the other contains a list of computer names to be removed from the master list (exception list).
I need to go through the master list and remove any computer names that are on the exception list- for example, if 'computer1' is in the exception list, i have to find and remove 'computer1' from the master list.
The exception list is quite long, and I want to automate this process if possible. not sure how to achieve this.
I'm using Excel 2007 Standard.
View 4 Replies
View Related
Jan 23, 2014
Following the tips doesn't allow to remove a character.
I exported email addresses from Outlook and they have the following character ' in front of the email address.
Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"
So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.
View 4 Replies
View Related
Aug 9, 2013
In excel 2007 i have three sheets.
In sheet1 : Cell : "D3" : I have find out & show maximum date with two criteria (i.e. code & series) from all sheets.
Also in cell : "E3 : I have find out maximum date with two criteria from particular one sheet only.
I have mentioned comments in attach file.
In sheet name 1112 & 1213 : I have a lot of data approx 40000.
Column A : Invoice no
Column B : Invoice date
Column C : Code
Column D : oano
Column E : Name
Column F : city
Column G : distric
Column H : Series
In yellow highlighted cell i required formula.
View 5 Replies
View Related
Feb 20, 2012
Using Excel 2007 and conditional formatting, how would I find duplicates across all fields in the table, similar to the "remove duplicates" tool that in default mode selects all the fields or offers the option to select fields of choice. I need to look at all the fields.
I've tried concatenation, but only with a limited number of fields; larger amounts slow the process to a crawl. If my table is 10,000 records with 15 fields, what process would I use to highlight the duplicates. How about something in VBA?
View 3 Replies
View Related
Jun 14, 2013
Macro code to find several column names in row 1 (which can be anywhere in the work sheet) and delete them e.g. date, month, code, amount etc.......
Excel version 2007
View 4 Replies
View Related
Dec 9, 2011
I am trying to auto fill one cell with one of several names using different values from another cell. Ex: when one of six numbers (100, 101, 102, 103, 104, 105) in input in A2, I want B2 to display a name (Joe, Tom, Steve, Fred, Tyler, Todd) that corresponds to that number. Cells in columns A and B will be formatted as text. Using Excel 2007.
View 9 Replies
View Related
Jan 3, 2012
We were looking at a list of about 100 FedEx tracking numbers, each 30 characters in length, in an otherwise blank Excel spreadsheet. All tracking numbers in column A.
I used the Conditional Formatting - Highlight Duplicates feature to find any duplicate tracking numbers. It highlighted numerous. We sorted the column smallest to largest to look at two duplicates back to back, but found that there were actually no duplicates. Excel was highlighting values that had the *first 25* characters the same, but it didn't matter what the remaining characters were. For example:
1234567890123456789012345XXXXX and
1234567890123456789012345YYYYY
were highlighted as duplicates of each other.
Excel only looks at the first 25 characters when analyzing duplicates (at least in this specific function). That in and of itself blows my mind. Tracking numbers seem like an abundantly common type of data, and to think that Excel would fail to analyze them in such a simple manner seems impossible to me.
It gets slightly more confusing though. If you back a character off the end of the values, it NO LONGER sees them as duplicates. So...
1234567890123456789012345XXXX and
1234567890123456789012345YYYYY
are NOT highlighted as duplicates while
1234567890123456789012345XXXXX and
1234567890123456789012345YYYYY
ARE highlighted as duplicates. So there's some funny business going on about values of the same character length. Maybe Excel first looks at character length before analyzing specific characters. If length doesn't match, it doesn't even bother to analyze specific character values. That seems like a very strange programming choice though.
View 2 Replies
View Related
May 22, 2012
I have a column (A) with numbers like
0
15
30
45
100
115
130
145
200
215
230
245
300
315
330
345
400 and so on up tp 2345
I need to change this into time as;
00:00
00:15
00:30
00:45
01:00
01:15
01:30
01:45
02:00
and so on up to 23:45
Is there any formula which can do this?
View 4 Replies
View Related
