I Use the following Macro to import data from a .txt(As attached) into excel. This results in the also attached Excel sheet. I am almost happy with it, but I don't want the Return_number4501856 Totals row in there. As a matter of fact I don't want any break between the different return numbers. Does anyone know how to add this to the Macro?
View 2 RepliesI need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
I have an Excel file with over 5000 rows all containing URLs
I only need the URLs containing a particular word in ther such as
if the URL contains ".edu" leave it, and if not, delete the data.
Is there a way to do this by VBA?
Due to work, I need to creat the captioned file for our company.
View 2 Replies View RelatedI am trying to create a macro that sorts rows based on value of 2 columns, but every time that a value changes in either of them to leave an empty row between them. Is that possible? In simple words i want to separate my rows with the empty row while sorting them out alphabetically based on two columns.
Please check my screen shots. Test 1 is my spreadsheet before the macro. Test 2 is how i need it to be after the macro. The two columns that i want it sorted out is First GDS then Rate.
Is there an easy way to revise the following code so that it imports ONLY the non-blank rows from files?
Sub ImportFiles()
Dim i As Integer, files As String, text As String
ChDir _
"C:Test"
Cells.Select
Range("A1").Activate
Selection.ClearContents
'Selection.QueryTable.Delete
Application. ScreenUpdating = False
i = 0
files = Dir("*.csv")
Do While files <> ""
Open files For Input As 1
Do While Not EOF(1)
i = i + 1
Line Input #1, text
Worksheets(1).Cells(i, 1) = text
Loop
I have a log that I regularly use to import text files, after each text file import it leaves a large number of rows blank that I have to delete before I add in the next text file import.
Any VBA method to detect this and remove the blank rows so that I dont have to keep checking to remove?
code to import a tab delimited text file with about 3 million rows so that it creates a new tab every time it hits the 1 million row limit?
View 3 Replies View RelatedI have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.
View 4 Replies View RelatedI am importing information from 20 stores by item. If a store doesn't carry an item it is not creating any data for that store but in turn it throws my whole template out of whack. The graph would be a repetitive 1-20. Can I create this pattern and import the information to match while creating blank cells when the data isn't available? I manually entered the left column and the items columns "B" on are imported.
excel store column test.xls
StoreSKUDescriptionMfg.#UPC
115170016GUN,BB BUCK 00000000210539256221051
225170016GUN,BB BUCK 00000000210539256221051
335170016GUN,BB BUCK 00000000210539256221051
445170016GUN,BB BUCK 00000000210539256221051
5
6
775170016GUN,BB BUCK 00000000210539256221051
885170016GUN,BB BUCK 00000000210539256221051
995170016GUN,BB BUCK 00000000210539256221051
10105170016GUN,BB BUCK 00000000210539256221051
11115170016GUN,BB BUCK 00000000210539256221051
12125170016GUN,BB BUCK 00000000210539256221051
14145170016GUN,BB BUCK 00000000210539256221051
15
16165170016GUN,BB BUCK 00000000210539256221051
17175170016GUN,BB BUCK 00000000210539256221051
18185170016GUN,BB BUCK 00000000210539256221051
19195170016GUN,BB BUCK 00000000210539256221051
20205170016GUN,BB BUCK 00000000210539256221051
21215170016GUN,BB BUCK 00000000210539256221051
5170016
Is there a way to do a left formula where you can have it pull the whole cell except the last character? Basically, I have a sentence that looks like this:
apples, oranges, pears,
And I want to drop that last comma. But the length of the each cell varies from row to row.
the below code adds a new row with data to the last blank row on the sheet how do I change it so that it leave the last row and inserts above it.
the last row is a sum of the columns ....
I have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
is it possible to have some sort of form that stays on top and is showing a total price as the user is working? It would have to move down the screen as the user moves down also. I dont have the workbook started, just needed to know if its possible and if so, of course, how to do it!
View 2 Replies View RelatedI'm looking to pull into Column B the last name only from the "full name" column (column C).
Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C. The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name. For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
Is there a better approach so I only get the last name?
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
this must be very simple but I dont see it.
= IF(A2 = 50;F2;A2)
this gives me circular reference. All I want is if the value is = 50 to change the cell value, otherwise to leave the old value intact, as it is.
If I write something like this:
= IF(A2 = 50;F2)
the cell is assigned FALSE, and the old value is overwritten.
How can I avoid setting to the FALSE value, without having the ciruclar reference error ?
I am writing a very simple Do While loop where VBA searches for the first nonempty cell in a column starting from cell C3. The error I get is "Overflow". I suspect that when all cells are empty it goes down the column till the last cell, which is around 65,000. How can I put a restriction on this loop? I need the search to be restricted to range C3:C95, because my data ends in row 95. In other words, the code essentially should do this: go from cell C3 to cell C95 and search for the first nonempy cell in this range. When you find the first nonempty cell, assign name "x" to this first nonempty cell.
View 6 Replies View RelatedOn the attached worksheet if the data in col F changes from FLT to AWF,col M will flag up "faw".I would like to leave a blank cell instead of false if the condition is not met. Col P is an example of the result im trying to achieve.
IF TEST.xlsx
How is it possible to leave a cell in a set color, if nothing is entered in the cell? And is it possible to merge it with it with existing formulas allready in the cell?
View 4 Replies View RelatedI have cells B5:AL22 I want to put 1, 0, or leave blank. I need the zero (0) to turn red with white letter, the 1 and blank cells to remain in the present format. I can get the 1 and zero (0), but I don't know how to get the blank cells to stay in regular format.
View 3 Replies View RelatedProbably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.
View 4 Replies View Relatedi have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered
View 3 Replies View RelatedIts probably very simple question, but i can not work it out.
I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.
Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).
How can i get these cells to read $0 or leave the cell empty?
The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.
Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.
I need to delete all characters BUT numbers (including decimals) in my sheet. Does anyone know how I can do this? I can do it either by using the find/replace menu, or use vba.
View 6 Replies View RelatedEvery time I think I've got this thing beat, they throw another curve at me!
If cell Q19 is blank, leave target cell blank
If cell Q19 is not blank, return the value of cell E$4
I have a worksheet (in a workbook with many worksheets) where some of the cells display the formula I put in after I leave the cell. I've made sure that the cell format is General before I type anything in the cell. I've inserted new rows, above where the problem is and have tried going to another cell in a different part of the work sheet but I have the same problem. I use the same formulas in two other worksheets. The formulas include other worksheets in them. example =IF(PlotMaster!B2="","",PlotMaster!B2). I can't figure out what is going on.
View 1 Replies View RelatedI have 7 teams (82 staff in total) staff who work for several production line. we currently record all leave on the wall calender. I want start recording these on a spreadsheet and I wonder if any of you have already designed a annual leave planner that I could have a copy?
Staff can request for 1/2 annual leave as we all full day. Each Team is listed on a seperate sheet and if a team has more than 2 person on leave, it will go red.
A
B
C
D
E
F
[code]....
the example is as per above
formulas for D and E column
criteria :
Column D : pro rate base on when hired
Annual Leave is calculated base on Jan 1 of every year.
* if analyst hired in the middle of the year, their leaves are pro rated base on Jan 1
Example :
Joined : 9/1/2008 : today is : 10/25/2013 :
Currently leave entitlement is : 12 days [Jan 1 to Sept 1] + 2.66 days [Sept 1 to Oct 25] = 14.66 days
Leave until 31st Dec 2013 : 12 days [Jan 1 to Sept 1] + 5.96 days [ Sept 1 to Dec 31] = 17.96 days
C4 - #01941 (ATF Cleaner)
I have asked this before as to a formula to get rid of the text and only leave the #01941. The formula I received is =LEFT(C4,FIND(" ",C4)-1) and it works fine.
My queston now is there a way to only leave ATF Cleaner? No ( ) around it?