On Sheet1 in Range A1:A10, I have the following data: O O M3 M O O M M D9 O (in reality, the data covers the range K38:AN90, but I'm simplifying my requirement here)
On Sheet2, in Range A1:A10, I have the following data: O O O O A1V O O O O D1V
I need some VBA code that will loop through each cell on Sheet2 and where it comes to any containing the letter 'V', it will replace the data on corresponding cell on Sheet1 with that data.
In this example, after running the code, Sheet1 will read: O O M3 M A1V O M M D9 D1V.
The only other caveat here is that I need to be able to return to the original data on Sheet1 but I'm thinking I'll add some code to toggle back by making a back this up on a separate sheet so I can immediately reinstate original data on Sheet1.
I want to avoid 'Copy/Paste' and if at all possible so prefer to use Range("X").Value = Range("Y").Value etc.
I am building a rather good size userform. It is getting really lengthy code-wise because I want certain things to be enabled and disabled on the form as they click on certain options.
I know I can hard code the lines but so I am trying to come up with some coding elements where I will not have to add 20+ lines of code for everytime I want to add a new feature.
Here is one of the functions that I have, There is going to be several of these:
Private Sub chkMore2_Click() If chkMore2 = True Then cboProtocol2.BackStyle = fmBackStyleOpaque cboApplication2.BackStyle = fmBackStyleOpaque txtLowPort2.BackStyle = fmBackStyleOpaque [Code] .......
So If I click chkMore2 ... all of the 2nd elements will be editable. If I click chkMore3 ... then all of the 3rd elements will be editable, etc. etc.
So I tried to play with this code example:
Private Sub CheckBox1_Click() If CheckBox1 = True Then Call Changeit(1, "yes") Else Call Changeit(1, "no")
[Code] ........
Excel 2007 does not like the .concatenate element.
I have data in columns A:D with the headings Proposal, Sort_Code, Seq, Comm, and data in row 2:???, it is in the format below, / is to show a new column, I want to look at the proposal column (A) and the sort_code column (B) and then take all the data that is associated with it in the comm column(D) and combine it in column E on the first row of the proposal number. A Formula or macro would work for me.
I have two sets of data for students. One set contains all the students with certain test scores, taking up columns A to N. The other set contains about 80% of those same students with a different set of test scores. Students are sorted by ID numbers. How do I combine the second set into columns O to V so that student ID numbers match and it inputs the rest of the student data into the sheet.
I know there will be some blanks because not all students will be there but I need them to match up, even with the blanks.
Is it possible too do this if they are on the same computer. Lets say I want all the data on page3 in one workbook, data on page2 in another and have it show up in another workbook. If this can be done can it delete cell entry that match?
The data in each worksheet is from different programs that I need to combine. The first worksheet contains student mailing address information and the second details of payments for a specific purpose. Both worksheets has common data that is the student number. We have around 960 students and I can have anywhere between 400 and 20 students paying within any given month.
Below is an example of what I need, Sheet3 being the end result. I don't care if it is easier to combine both sheet1 and sheet2 completely and need to delete columns or move anything around (that takes no time).
I have tried to research how to do this but as I have no programming experience I have been unable to get my head around it.
I am trying to do something that would look at column B and for each value that is the same in any row in column B, it would look to column A and add that value into a text string of all those values in column C separated by commas.
example, If A1=37294 A2=37295 A3 = 37296 A4=37297 A5 = 37298 and B1 = abc B2=ABC B3=ABC B4=XYZ and B5=XYZ I want C1 to result in 37294,37295,37296. And I want C2 to result in 37294,37295,37296 and C3 to result in 37294,37295,37296 and C4 to result in 37297,37298 and C5 to result in 37297,37298.
I am trying to copy raw data from two worksheets (Sheet2 = "550 Report (raw data)" & Sheet3 = "305 Report (raw data)"), that I cannot edit, over to a new worksheet (Sheet1 = "Exception Management") that I can edit. I have been successful in coding Sheet2 to come over correct, but am having problems with Sheet3 because of a few issues:
1st: Column A on both sheets contain a unique alpha-numeric identifier that can be used to identify records that can be combined. Sheet3 will sometimes contain multiple records for a single Sheet2 record.
2nd: Matching rows from Sheet3 will always be done based on Sheet2 column A (So I need to search through Sheet3 column A based on all values in Sheet2 column A).
3rd: The dataset is MUCH larger than my example files. Sheet2 normally contains 700+ rows of unique identifiers with 56 columns of data. Sheet3 is pretty much as-is, except ususally 1/10th the number of rows as Sheet2.
I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.
I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls
My workbook contains information about investigations. Each investigation gets a number and a line in the workbook. Many infos are stored: dates, subject, ...
A dynamic table contains all those informations, which allows me to manipulate data pretty easily.
Issue: For one of the characteristics of the investigation (let's say names), several results might have to be entered. For example, one investigation can have 3 different names, the next one only 2 and another one can have 6.
This causes a problem regarding the manipulation of the data.
Right now I have 10 columns "name 1" "name 2" .... and only those who need to be filled are filled. I can't however efficiently manipulate the data. For example, George could have been entered in "name 1" for the investigation 1 and in "name 2" for another investigation. Using a pivottable, to track George I'd have to select him in all 10 columns.
It seems to me the best choice would be to somehow index the names into a single column with each name linked to its investigation number..
With the index function I have 2 issues:
- It starts by going down the 1st column, then goes to 2nd, ... where I'd want all names of 1 row then names of 2nd row.
Solved this with this formula:
I turned my "names" columns in a ranged name "Names"
but - When fields are empty I get 0s. When I try to remove them with an if statement, it'll just give me empty cells. I'd prefer to skip the empty cells.
Also, I don't know how to 'link' them to my investigation number...
I have this (dynamic) table:
investigation number name 1 name 2 name 3 001 George Paul 002 Chris George Suzy
And I want something like this:
Investigation number Names 001 George 001 Paul 002 Chris 002 George 002 Suzy
I have 500 rows of data with 6 columns. Columns A,B,C,D,E are text, and column F is values.
I'm looking for a formula where I can say for any of these rows where the data in columns A through E match each other in those rows, combine them into one row of said text, with column E values totaled. I thought I could use SUMIFS for this, but I only know how to define SUMIFS with the criteria being a specific cell/text/value for which it should search on. In my example, the data in the cells can be anything, I just need rows to be "collapsed" to one row for like data.
Is it possible to combine datas from over 200 cells (all cells are from the same row) in one cell? I have tried CONCATENATE function and e.g. =A1&B2&... but is there shorter formula for this?
combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
I have attached a spreadsheet. I have a data set with ID's in column A and data in columns to the right. If an ID has only one entry then there will only be a value in column B if an ID has 2 entries then there will be a value in Columns B and C, if they have 3.......... And so on....
In my attached file I have created a macro that filters each column and copies the values to another sheet. If an ID had 5 entries (B-F) I would want to have 5 rows for that ID. The attached example macro does what I want but the real data has over 50 columns. Is there a more efficient way of doing this?
I have a workbook with two sheets that I need to combine some of the data from. Sheet1 is a template to be used for translation purposes on Sheet2. I would like to put Sales Rep Info in column C on Sheet2 (has only Sales Rep ID) using Sheet1 as a translation table (shows Sales Rep ID and Sales Rep Info). I can do this with a vlookup function but would like to know how to do it in a macro so I can use on other more complex workbooks. Here is a sample data set.
what the easiest way is to combine date and time into one column. Concatenate? What format do you give the combined date and time column after you group them? Below is what I need to do:
Date Time Combined_Date_Time 01/01/2008 06:05:42 01/01/2009 06:05:42
I have data in multiple worksheets and I want it all combined in 1 excel sheet. The 1st worksheet is named as A and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. 2nd worksheet is named as B and all the data in column labled "date" as well as column labled "name" should be copied to master sheet. The range is not specific as vary every month. and there are some more worksheets like that. The start point of data will always be same but can end till any row. Can I get a code for collating all together in one sheet,
I'm trying to get the following code to work (as a practice run for a bigger set of spreadsheets) but it doesnt seem to work - no errors either. There are 3 workbooks all with 1 worksheet called "Data 1" with 3 columns of data (under 20 rows). These (theoretically) should copy into 1 called " Totals". The code I have used is: ....
I have 5 worksheets (all formatted the same) with data inputs from row 19 to 119 and columns A to BA. After row 119 I have several sums based on the data that is being entered on rows 19 to 119, I don't want this data being copied over. In most cases all rows from 19 to 119 will not be used, so I would only like to copy the rows that have data. To complicate matters, there are times when there will be blank rows within rows that have data. For example rows 19 to 25 will have data and 26 will be blank but rows 27 to 38 will have data. The blank rows are identified by column A (Job Number) being blank. This occurs because a job is dropped from the schedule and is no longer necessary.
In the end, I would like to have a button using VBA that will first clear the data in the new sheet (the sheet that is being populated with the information) and then re-populate it with updated data from the 5 worksheets.
I am hoping to combine repeated data into single rows. The best way for me to explain is to attach a spreadsheet with an example of how the data is and how I would like it presented. The spreadsheet has comments to highlight this.
so basically, here's an example of what I have. The spaces in between represent different cells:
Name Corporation Type Group Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 2 Vicki SchweitzerAon ConsultingBusiness GroupCouncil 3
What I want to have is instead of having 3 rows in this instance, I want to have a macro that looks at rows and if it finds multiple rows with the exact same name, corporation, and type, it merges the rows to look like this:
Vicki SchweitzerAon ConsultingBusiness GroupCouncil 1, Council 2, Council 3
In some instances, I have 3 rows that might match, some its 2, some its 10, it varies.
I need to combine data from multiple rows to single rows, for certain fields only. The database is large and can described as having an ID column, followed by columns 2 to n that do not need to be combined, followed by columns n+1 to N that do.
I've attached a sample dataset along with the desired outcome. For the purposes of illustration the sample uses n=3 and N=5 but the actual dataset has much larger dimensions.
I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.
VB: Sub Patient_Detail() Dim n As Integer 'index of rows to record to Dim i As Integer 'index of column to record from
I have 5 columns, each with some data. On the 6th column I need to combine the data from all 5 columns into column 6 to be as: Data in A2-Data in B2-Data in C2-Data in D2-Data in E2. With one exception. column D is the date in this format 27-Aug-09. I need the date to be modified on the fly from 27-Aug-09 to be 270809 which will be the info in D2. See example attached.
Sample file attached. How do I combine the data in columns B-E into column A? Obviously in my sample file you'd use a simple cut/paste but my actual file has hundreds of columns and rows so cut/paste isn't an option. I need to move all the data into column A.
