assist me with editing this code so that even though my range may be set from A1:A3000 it would stop after finding and completing its task in the last cell in column A with data. This code will put dashes in a set of numbers and there is no certain amount of account numbers that could end up being in column A on any given month.
View 3 RepliesI have a simple problem and I'm sure someone out there has the simple answer i'm looking for. Here's my problem:
I have a column of cells with data in them (no formulas, just "hand-written" data). I would like to find the last cell in the column that has data, but without using VBA.
I tried to find help, and I thought I might be able to use the LOOKUP formula, but since I never used it before, I kinda hit a wall... is there any other formulas that might do the job? or do I absolutely have to use VBA?
After I find the last cell with data, I will use it in another formula in another cell... nothing too complicated, but I need (if possible) the data in the cell to be returned to the other cell for the other formula.
I know this will tell me how many rows I have:
[Code] .....
But I have a column of data with empty cells interspersed. How do I find the first cell in the column with data in it?
I have the following code. Is there any way to select a range once the last cell with data is found. I would like to be able to select whatever cell in column A is selected with the code below through E2.
View 4 Replies View RelatedIs it possible to have a marco that will look at the data in a cell/multiple cells and search a network drive for a folder named with the same data in a cell? For example, I used a marco to create a folder from the contents in cell A2. Can a macro be reversed in that if I have a spreadsheet with a list of data in A2,A3, A4 and so on, can it check a network drive to see if a folder has been created with the same data?
View 10 Replies View RelatedI am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
How would i write VB code that looks for the last row in my data table? I'm a total novice with VB but learning slowly..
View 9 Replies View RelatedLooking for a formula (not macro) that can do the following:
I have a spreadsheet and in one column, it has last names of clients. In the next column, it has full names of clients that are the same account, but different system with different descriptions for the name. I need to lookup the column that just has last name and see if that last name exist anywhere in the "Full name" column. If it does, then I need column C to say "Yes" or something along those lines.
The "full name" column isn't always in the same format and sometimes doesn't even contain the "last name." Sometimes the last name will be first and sometimes there's a comma and sometimes there isn't a comma. The spreadsheet attached shows a good short example of what I'm working with...
Name Spreadsheet Ex.xlsx
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
On my example sheet it shows how many cables and how the cable length per job. What i need is for the hour/job column to be populated from the Hours sheet by matching the column and row. e.g 4 cables at 40 metres would give me cell D7 (27.89), as 25 metres would be no good - so rounding up would be required.
View 2 Replies View RelatedI have a range that changes the data constantly, I have to watch that data changing. I am trying to work on a macro that copy that data and paste to another sheet. What would be the code to find the next empty row and paste my data there.
Data is in Sheet1, range A17:E32... and it needs to be pasted in sheet2 starting in F2.
My spreadsheet consists of two columns, column A holds a series of numbers whilst column B, again holds a series of numbers but with an added single letter on the end.
What I would like to do is find a function which will match the numbers and letter in column B with the corresponding numbers in column A and then place the result in column C ....
I have to detect all staff members with the passport expiration dates between two given dates, i.e.
A 12/4/2012
B 5/3/2012
C 7/3/2012
D 6/3/2012
E 9/12/2012
F 12/11/2012
G 11/16/2012
H 10/12/2012
etc
So, I need to have excel detect all staff members with the expiration dates between let's say 9/1/2012 and 12/31/2012.
There is a data where values of indicator (in this case Gini coefficient for different countries) is not available for all years - some recent ones are missing - see the picture. What is the most efficient way to find the most recent data in a row?
View 1 Replies View RelatedIs there a way to find data in an array similar to the way match, index or vlookup is used for data in cells of the spreadsheet?
Example, if I have a 2 dimensional array:
1 apple
2 orange
3 pear
4 banana
5 grape
and I want to know if the word "banana" exists in the second column of the array, is there code that would determine if it was in there at all, and is there code that would determine that it was item 4 in that array?
I have a set of data which are deal numbers
So week 1 i could have
Deal
ASD
ADF
AGF
Then next week i would get another list of deals
ASD
AHJ
ADF
AGF
AIG
Is there a formula i can use which well tell me if a new deal has been entered in the following week.
So in the above AHJ and AIG would be new deals
(i would simply copy the deals and put them in Column A and B and the formula to go in column c)
I am a basic user of Excel.
I have an Excel spreadsheet with data from the last 10 years.
In one column it has date in dddd,mmmm,dd,yyyy format and the second column with data for the dates.
I need to have Excel extract only Tuesdays with data that is a positive number (above 0).
Is there a formula, or other method of finding only these results from the whole speadsheet?
I have a problem on matching up two sets of data. One set has a start date/time, and an end date/time. The second set has a single date/time.
Set 1:
Process, Start Date/Time (dd/mm/tt hh:mm) and end Date/Time
e.g
Process 1, 01/01/2009 12:45, 01/01/2009 12:56
Process 2, 24/01/2009 13:45, 24/01/2009 14:02
Process 3, 24/01/2009 14:38, 24/01/2009 14:43
Process 1, 01/02/2009 12:55, 01/02/2009 12:56
Process 2, 08/02/2009 12:35, 08/02/2009 15:21
Set 2:
Event, Date/Time
e.g.
Event 1, 01/01/2009 12:55..................
Macros question - The user enters a word, e.g. Malaysia in a particular cell (always the same location) and I want the macro to be able to find the next cell with that word in it, but it appears that when using macros you can't paste anything into the find function, so was just wondering what to do!
View 1 Replies View RelatedThe list of data is shown in sheet1
In sheet2 I have shown the data that is copied
I have manually shown comments on 2 cells in sheet2 that shows detailed description on how was the total taken.
I need the comment on all the cells from D8 to G11 (not taking the total column) that shows on how was the total taken.
When any entry in sheet1 is inserted or deleted the comment in sheet2 should change accordingly.
I also do not want to freeze the cells D8:G11 as when new entries are made or deleted the columns would increase or decrease.
All blank cells I do not need any comment.
I have two spreadsheets of "leads"
One lead is a do not call list that has 4 separate columns of phone numbers,
The second list is a batch I purchased.
I need to make sure that the second batch does not contain the info from the first spread sheet.
I guess I could modify the 1st spreadsheet to just be one column of numbers, then I would be comparing one column to one column.
Is it possible, and does anyone know the formula to find someones name and add the data to the right of it? Example
A1 John B1 32:26:00 C1 500
A2 Bill B2 23:45:44 C2 452 D2 John E2 36:45:11 F2 520
I have many names and they are scattered throughout the sheet. I know how to add them if the are in a single column but not if they are scattered.
In my summary I would have the names ...
I have a table (Sheet 1) with four columns data, A,B,C and D. There are about 60,000 entries in them. In Sheet 2, I wish to enter a value in A1 which will be from A OR C columns of Sheet1 and get its corresponding value from B or D (Sheet1) in B1 (Sheet2) with the help of a formula. i.e. IF(A1, Sheet1!A60000:C60000, then B1 = B or D of Sheet1).
View 2 Replies View RelatedColumn A = Date
Column B = Numbers
Column C = results
As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).
Any way of finding particular data from a data set and deleting the data from that field without using conditional formatting or remove duplicates?
I have used a vlookup to find the words that I needed I now need to find where the are located in my original data set and delete these so I am left with data that if I perform a vlookup on it will not bring back any words as they will have been deleted.
I use this code below to copy information from various sheets on to one sheet,
PHP Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim xrow As Integer, xcol As Integer
[code]....
i use a command button to run the macro, but i would like something added that if someone else pressed the button and ran it again that it would check to see if it had duplicated the previous line and if it did it would delete it leaving just the one record for that week.
I have a column with lots of rows. I want to locate a specific one and insert some data into this row at specific columns. This is what i have come up with so far:
[Code] .....
have two worksheets. sheet1 has order information on it with orders, dates, customer names. sheet2 has customer name list. How can I (via vba) search through the order sheet and find the most recent order date for each customer in the customer name list. post that most recent date next to the customer name on sheet2.
View 4 Replies View RelatedI've written the following code to insert rows at certain points depending on certain conditions, looping through to the last row. However, as the last row number keeps moving, due to inserting rows, I am not sure how to alter the code to allow for this.
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
[Code]......
I have 2 sets lists which should be identical, but every day I get give one new file which I have to compare to the other one. I need to find new data and removed data in the new file.
It is a dynamic list, but I need to check every day that all the data that was there yesterday is also there today. If not then I need to be told which data is missing and which data is new (maybe thrrough a pop up box and a new sheet with a new and cnacles coloumn).
A Example of the data is below. On day1 everything matches. On day 2 Grapes is a new entry, so I need this flagged up.
On day 3, pears hsave disappeared, I need this flagged up.
What would be great is to have a messgae box pop up and say "2 new entiries and 3 deletions - Please refer top Changes tab". And then the new/deleted entries will be in a seperate sheet.
I have tried searching this on the site, but can't find anything (I can't beleive problem like this is not on the website) - If there is an article would you be able to point me to it.
Day1
orangeapple
pearorange
applepear
bananabanana
Day2
orangeapple
pearorange
applepear
bananabanana
grapes
Day3
orangeapple
pearorange
applebanana
bananagrapes
grapes