I know how to separate data into different columns i.e. Marry Johnson into two separate columns; however, I have data in different rows that I need separated into different column. See screen cast [URL]
Using excel 2003 on windows 7 64 bit
I'm new to this forum and to VBA
First-off, I'm using Excel 2003 SP3.
The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg
G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc
The files contain the following data:
DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc
I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.
My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.
I have a separate xls file with criteria data in two columns:
FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc
The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.
the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!
Option Explicit
Sub CopyPaste()
'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).
'The data from the Results sheet is then copied to the Master sheet.
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
[Code]...
Running Excel 2003. I have a list in column B, of numerical codes. What I want to do is find the duplicates, and list the duplicates in column D. Is this possible?
View 11 Replies View RelatedIs it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
I'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
A
B
C
D
E
[Code]....
I'm using Excel 2010 on Win 7 Pro.
I have a large amount of data that is from an events file. When I enter it into excel, it almost maxes out the row count (excel 2010) The data basically lists Event, Date/Timestamp, Call ID, and a few other details. Im trying to get a total of specific events and if they were successful or not, so the other columns arent of concern. For instance, there is an event called searchbyphone, a later row in the data will include searchresult found or notfound. The only way to pair these up is by the ID number which is unique per call.
So in the example below, ID 123-45-789a was a search by phone and successful (found). The Date/Time field is down to milisecond. If this were just adding up all the various events and then totalling the searchresult found/notfound, it would be simpler. However, Im only interested in certain events and found/notfound is used for many other events.
Using the data below, what is the best way to pull out an event (say the total number of searchby phone) and then know how many of those were found and not found (when the information needed is on 2 seperate rows and only the ID would be the same?)
To through a wrench into it, there can be several events with the same ID becasue the ID is unique to a call - a caller can search by a few variables. There will be the same amount of found/notfounds though with that ID. (meaning as a caller, I can do a few searches and all of these will be in the event file with the same ID) In this case, the number of requests (events) will match the number of results (found/not found).
What Im trying to accomplish is to get a total of searchbyphone found and not found, So in this example I would have searchbyphone found = 1 and searchbyphone notfound = 0.
ID
Event
Date/Timestamp
123-45-789a
searchbyphone
10/7/2013 12:52:38
987-65-432b
searchbycity
10/7/2013 12:52:39
123-45-789a
searchresult.found
10/7/2013 12:52:41
874-35-123c
searchbyzip
10/7/2013 12:52:42
987-65-432b
searchresult.not found
10/7/2013 12:52:47
I have a notepad with lot of information's but when i try loading those info's to excel I am getting message "File Not loaded properly"
Is there a VB code which downloads it to excel and automatically moves to next tab and paste the rest.
I have an excel spreadsheet laid out as:
A B C D E F G H I J K L M N O
1 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
2 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
3 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
4 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
5 ** ** *** ** ** ** ** *** ** *** ** ** *** ** **
I need a macro that can automatically export the cell data for each row from Column A--> O into it's own seperate text file, and name each text file with the text in column A. So again... everything in row 1 from A to O would be copied and pasted into a text file called (A1 cell data).txt
once that first text file has created, I was hoping that there could be a loop to automatically close the first text file and move on to row 2 and do the same, copy all text in row 2 from A to O into a new text file titled (A2 cell data).txt, close and move on to row 3 etc etc.
this is a large list of approximately 8,000 records.
I have company asset data listed by item on wksht 1. Example:
Company A Computer 200
Company C Coffee Machine 21
Company A Car A123
Company B Computer 70
Company A Chair B14
Company B Desk C56
Company C Computer 59
Company C Desk C789
I want to sort and covert the column data on wksht1 to row data on wksht2. Example:
Company A Computer 200, Car A123, Chair B14
Company B Computer 70, Desk C56
Company C Computer 59, Coffee Machine 21, Desk C789
I'd like to do this via VBA/Macro(s) if possible.
I have to do this every month, so it would be nice to append the existing data to an "archive" file on wksht3 and then create this months data.
The data is imported each month via a text file with comma delimited fields which become the column data in the top example.
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
I have a worksheet with data in it. The data could be in any column or row. I am looking for data which contains the numbers 01. If I find such data, I want to move all the data in that particular cell it to column A.
My code(which is not working) is below. I'm using Excel 2003.
Code:
Sub delete_oldads()
'the code to find 01
Dim cel As Range
[Code].....
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
I am working with an excel spreadsheet. There is a column for "name", and then the next column is for medical condition.
If the same patient has multiple (say, 4) conditions, the rows for "name" are combined into a single cell(let's say, a cell going from row 2 to row 5, witha a single name in the combined cell) , with the next column having four different cells adjacent to the single cell (cell 2 has "bleeding", cell 3 has "fever", cell 4 has "diarrhea", and cell 5 has "rash"). Some patients have only single conditions, single cells.
Will this mess up COUNTS and COUNTIF?
Is there a way to split the combined cell2-5 for ''name" into four cells all containing the same name?
I have some code that works almost perfectly. I am trying to automatically delete rows in which no data exists in cells in a certain column.
the problem that I am having is that is seems to go into an infinite loop.
For example, if I delete row 5 and then iterate to the next row, row 5 becomes what used to be row 6 and now my code is on row 6...essentially skipping a row.
Here is an example data set (note that the set is for 20 rows):
1
3
6
15
36
37
here is the
Sub Macro2()
r = 20
Min = 1
Do While r >= Min
If Cells(r, 1) = "" Then
Cells(r, 1).EntireRow.Delete
Else
r = r - 1
End If
Loop
End Sub
I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.
ie. this is my data in column 1
1
2
3
4
5
6
7
8
would like to separate it into columns 2 and 3 as follows
1
.....2
3
.....4
5
.....6
7
.....8
I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
I am trying to separate two rows to two columns. How can I do this for +200 data. For example,
1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.
show me how I can do this for Excel 2010.
I need to populate sheet 1 of the spreadsheet attached.
I have tried several formula's but don't work and am getting desperate!
I need to count Column A of sheet2, when "Adverse SEN" occurs but only when there is an "x" in Column B of sheet 2 appears next to "Adverse SEN".
So basically i need to populate Sheet1 of the spreadsheet with the data is sheet2 of the spreadsheet.
I need a formula to calculate how many time an adverse SEN was - where there is an x - resubmitted, approved at meeting, delegate approval obtained, approval outstanding, rejected or approval not required.
I am using excel 2003, so please don't provide me countif functions.
In sheet 1, I have a list of data in A2:D21. In sheet 2, I have formulas in the same range that "paste link" the data. If, there is no data entered in any entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how?
View 2 Replies View RelatedRunning excel 2003 and have a number of sheets where i want buttons along the headings to sort a selection of rows.
For Eaxample one sheet i have data in range A6:AD135. i would like to assign a macro to various buttons on row 5 to sort by that column ie button in column b sorts all data in the range by column B.
I am running MS Excel 2003.
A user inputs a string of letters/symbols all meaning different things. These would typically for my case be L, R, +, -, F. These items correspond to certain behaviors. Essentially I'm working on a L-System solver for those of you who are well versed in math. For those of you who aren't, I'll explain a bit.
An L-system starts with a string, for example:
LFL+F+LFL
and then has one or more generators:
L:-RF+LFL+FR-
R:+LF-RFR-FL+
then at each iteration of the system, every L and R in the previous iteration gets replaced with that string. The +/- are incremental angles.
I need to be able to take each iteration and convert it into a series of rows with one character in each row. I need it to do this automatically, and automatically adjust to the proper length of rows (these strings can get thousands of characters long because each iteration of the system converts the Ls and Rs into longer strings containing more of all of those letters.) Once each of those rows are constructed, I need it to run some mathematical operations on each of these rows automatically.
For example, for each + or -, the value in an angle column should change by an increment given elsewhere in the sheet. For each F, the x and y coordinates should change from the previous iteration based on the angle (just a basic d+dcos(ang) or d+dsin(ang) where d is a distance multiplied by a scaling factor).
I have no experience with Macros or any type of scripting, so if I can keep this in an Excel formula, that would be ideal. I know a minimal amount of C++ and Python, but that's the extent of my programming knowledge.
I need a code that will go thru column C and delete the whole row if cell value is 78315, Also it needs to shift the the rows up so there is no empty rows wheneverything is done done. I used some codes but they take forever to go thru whole sheet.
View 4 Replies View RelatedI have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003
Here is the formula I am using now: =IF(Players!$E3="y",Players!$B3, " ") but I get blank rows for the player that are not playing.
I am using excel 2003. My sheet contains Times in column A and numbers in column B. I want to be able to count how many of those rows fall into time periods. Such as how many are from 8:00-9:00 AM. I do have the time column formatted as Time.
Ex:
A1: 3:40 PM
A2: 8:20 AM
A3: 8:55 AM
A4: 10:23 AM
etc.
B1: 23
B2: 34
B3: 12
B4: 35
etc.
So I want to be able to pull that between 8 and 9 AM there are 2 records.
The second part would be (in another cell) to pull the SUM of the numbers that correspond to the previous question. In the previous example it would be the SUM of all corresponding cells (Column B) that fall within the 8 and 9 AM range. Which would be 46 in this example.
Excel 2003 / WinXP. I have a list object with 5 columns, A - E, and all cells have a border round them. I need a macro which will:
1. insert a row below the current row (whatever cell happens to be selected)
2. then remove the left hand border of the cell in column B in the new row,
3. then select the adjacent cell in column C in the new row ready for data entry.
Obviously I cannot specify any absolute addresses in the code, and this is where all my attempts to record a suitable macro fall down.
a VBA problem I have and I do not know how to solve. I use Excel 2003.
I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.
There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.
I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.
My original macro (it deletes even locked rows):
Sub DeleteRows()
'
ActiveSheet.Unprotect Password:="123"
'
Selection.EntireRow.Delete
'
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowSorting:=True
End Sub
The macro found and modified (the original rows are commented):
Sub DelRows_2()
Dim rngDelete As Range
Do While rngDelete Is Nothing
On Error Resume Next
[Code]....
I have a seating plan for my students that has all sorts of data in it and I want to hide certain rows from students. However, they are not all in a group, they are spread out. So for example I have row 3 with student name, row 4 with their target grade, row 5 with their current grade, then row 6 is another student with data on 7 and 8. After that I might have a gap and then the next student on row 10 with data on 11 and 12. I want to toggle on and off the data and leave the student seats. I'm thinking that Subtotals would be the best way, but not sure how to implement it. Do I put row headings on the side? Also I haven't used subtotals since Excel 2003.
View 1 Replies View Related