VBA Merge Multiple Columns
Feb 15, 2014
I'm currently working with the "Original"sheet shown on the attached file. From this, you will see that there is generic information contained in columns A:D then columns containing a month date and the associated FTE figure.
What I'm trying to do is as follows:
-Insert two columns after the "Investment Role" column, with column headers of "Period" and "FTE"
-Then, for each month column for each record, create a new row one under the other, copy the values in columns A:D and paste them into the newly created row,
-Then move the Date value for each month and the associated FTE figure and put these in the "Period" and "FTE"columns.
I've attached a file showing the 'Original' dataset and 'Desired Outcome' to look at.
Although the values in all of the fields may change month on month, and the number of rows may change, the number of columns will remain the same.
Merge Columns Test.xls‎
View 5 Replies
ADVERTISEMENT
Mar 31, 2014
I have two columns, which have different length and in which there can be three types of data:
The data also in the first and the second column, so it is an old data.Second is, that the data is in the first column, but not in the second, so it is old, but it was solved.The third type is, when the data is in the second column, but not iin the first, so it is new data.
My task is to regularly update the list (so I have to merge the new and old columns), to see in which conditions the data is, but not to delete any of them (only the recurring, so if it is old, then it must not be listed two times).
View 1 Replies
View Related
Feb 7, 2009
I have a need to get the data from columns A and B and transfer to C but one line from A and second line from B and this successively until the end of A and B. They will have the same number of lines.
View 3 Replies
View Related
Jan 26, 2013
How I can merge two columns together and keep the data in both columns?
I have an address issue when the data was converted to excel it put the numbers part of the address in column "C" and the street name in column "D".
View 2 Replies
View Related
Sep 12, 2008
i am trying to create a macro that merges two columns in excel.
View 9 Replies
View Related
Jul 9, 2009
I'd like to perform a merge of value between columns D and E.
So if D9's value = 2 and E9's value = 15
then the result will be
E9=215
I've tried the following but I haven't gotten it to work.
View 7 Replies
View Related
Jul 23, 2014
I have two columns that are the same field, both Middle Name. The first column does not have every single row filled and the second column has every row the first column does not have and vice versa. I want to merge them to one filled column.
View 2 Replies
View Related
Aug 14, 2009
I have a worksheet with 10000 plus entries in ten columns From K to T. I wish to have a macro with Concatenate function to merge certain Columns from these ten columns, in one column with help of input boxes which may ask inputs, about range (from and to ), and number of digits to concatenate in the required column. I use Excel 2003 XP in work but an example is 2007 attached.
View 5 Replies
View Related
Dec 19, 2012
So I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.
I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.
I want to be able to turn this:
HTML Code:Â
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>
[Code].....
And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.
View 3 Replies
View Related
Nov 12, 2007
I have been scanning web forums for days and have not found a solution to my question. I have found similar, but unfortunately am not clever enough with Excel VBA to adapt.
I have two columns of data which I want merged into rows (a) by survey number and (b) by time slots. The data I have is similar to:
Survey No.....Results
12345...........9am-12pm
12345...........3pm-6pm
12345...........No answer
56789...........6am-9am
56789...........12pm-3pm
56789...........6pm-8pm
56789...........10pm-12am
.....................
View 12 Replies
View Related
Nov 14, 2008
I've got two columns:
A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn
And I need to bring these together in one column so it looks like this
C
abcdef
qwerty
asdfgh
zxcvbn
View 2 Replies
View Related
Nov 18, 2011
My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
View 1 Replies
View Related
Jun 7, 2012
I use the following code to merge all data a "master sheet", but I want to take the data and put it next to each other on the "master sheet", not under. For example, my range of data is "A1:D15" and I want to copy from all sheets. So my first paste would be A1:D15, then the next should be E1:H15.
I would like the code to find the used range, like it does currently.
Code:
Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
[Code]....
View 1 Replies
View Related
Sep 27, 2013
I've found macro, which merge rows with the same data
Example:
BEFORE:
january
1st
january
2nd
[Code] ........
Macro:
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("a1", Cells(Rows.Count, "a").End(xlUp))
[Code] .....
But i need use the same procedure for columns, it means:
BEFORE
A
B
C
D
E
F
january
january
january
february
february
february
AFTER
A
B
C
D
E
F
january
february
I've tried modified macro marked above, but without success...
View 3 Replies
View Related
Oct 26, 2013
I have 13 different worksheets with the same header columns, i would like to merge into one creating new work sheet. All the data in column A should keep on adding on the column A from all the worksheet and from column N going across. I have more than million records on each sheet
I have dropped the file on [URL]... for example
View 8 Replies
View Related
Dec 5, 2009
I use CAD software that generates Bills Of Material. I cut & paste these to an Excel template that has column headers in row 3, for example:
U3 = Item name
V3 = Manufacturer
W3 = Reference_item_name
X3 = Reference_item_ID
Starting from row 4, I would like to add the content of columns V, W and X to column U, separated by comma's. No superfluous comma's should be added when columns are empty. It would be nice to have a macro that uses the row 3 column names, so it still works if someone changes the column order.
View 9 Replies
View Related
Aug 7, 2008
i have a table looking something like this
----A--------------B---------------C----------------D-------------
1---Destination-----Country_Code---Area_Code------ Price_Per_Minute
2---Armenia--------99--------------58, 59, 566------0.098
3---Jordan---------96-------------79,78,77,2--------1.023
4---UK-------------44-------------------------------0.50
5---UK-------------44-------------79,78-------------0.48
what i actually need is to turn this table into 3 columns table, the first column is the destination, the second one is the destination code which consists of the concatenation of the country_code and the area_code columns, and the third column will be the price_per_minute, the main problem is that the Area Code column contains many values and each value should be attached to the country_code and then i need to create more rows for each country, so the my table should look like this:
----A--------------B---------------D-------------
1---Destination----Code-------Price_Per_Minute
2---Armenia-------9958-------0.098
3---Armenia-------9959-------0.098
4---Armenia-------99556------0.098
5---Jordan--------9679-------1.023
6---Jordan--------9678-------1.023
7---Jordan--------9677-------1.023
8---Jordan--------962--------1.023
9---UK------------44----------0.50
10--UK------------4479-------0.48
11--UK------------4478-------0.48
View 5 Replies
View Related
Jan 10, 2012
I have dates values in 3 columns.
A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
View 1 Replies
View Related
Mar 12, 2014
I have 2 columns full of information feeding from 2 different sources on our internet database
I would like to collect this information into 1 column to create a drop down list
As these are expanding tables feeding from the internet I don't think copy and paste into one column will work (I'm not sure about this but I would prefer a formula to avoid any issues).
View 4 Replies
View Related
May 19, 2014
I have a large volume of data and i need to manage and organize somehow.
Example:
A B C D E
CHRIS AN204 2005 Apple Green
CHRIS AN204 2005 Apple Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
This should look like this
A B C D E
CHRIS AN204 2005 Apple Green, Red
CHRIS AN205 2005 Apple Yellow
TOM AN204 2006 Apple Green
I manage somehow to find a VBA code that will merge my rows, but it's not working as it should. It's losing data.
Also in a cell it can happen to have more than 255 characters.
View 1 Replies
View Related
Apr 13, 2008
I have got a wordlist in worksheet "original" which looks like:
Before: [Code] .....
I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".
After: [Code] ......
The macro must be able to deal with very large lists. biger than 200 000 words in column A
Here is the excel file containing the example : excel file
View 14 Replies
View Related
Apr 13, 2008
i have got a wordlist in worksheet "original" which looks like:
Before:
View 14 Replies
View Related
Oct 30, 2013
My data is in column A , Column B and Column C, where are column C has a uniuqe value for certain rows.
Based on these uniques value, column A and Column B should be merged to singe Row without loosing data.
MY Data:
Source address
Destination address
Destination Port
Application
IP Protocol
192.168.1.2
192.168.250.10
53
dns
udp
[Code] .......
Result expecting is :
Source address
Destination address
Destination Port
Application
IP Protocol
[Code] .......
View 7 Replies
View Related
Jul 28, 2008
to prepare an excel spreadsheet for a mailmerge but as all of the info for 1 recipient needs to be in columns instead of rows. I need to convert 2 columns' data into columns but only when there are duplicate invoices, see below;
View 9 Replies
View Related
Feb 23, 2010
I have a report that the name and address was put into one cell. I need to put this into numerous columns so I can do a mail merge.
View 9 Replies
View Related
May 11, 2014
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
long story short, we ended up with an excel sheet like this: Screen_Shot_2014-05-11_at_4.png
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)
Screen_Shot_2014-05-11_at_46FDQO.png
I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly. Method to do it automatically?
EXCEL 2013
View 3 Replies
View Related
May 11, 2014
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
Excel sheet like this:
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.) Any method to do it automatically?
EXCEL 2013
View 3 Replies
View Related
Sep 1, 2008
After sorting and filtering rows with in a set range I will have several rows that are almost duplicates. This is normal and expected due to how the workbook is used. Among these rows also will be several single rows that are not duplicates. It is important that I combine any two duplicates into one row. Example:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS101300PS9
2 NameA0XX15930777PS91200PS10
3 NameX1159XXP555FBX1545PS9
4 NameB0A1234P123PS101263PS9
5 NameB1A1234P123PS90512PS10
What I need is this end result:
CREATE TABLES LIKE BELOW?
ABC D E F G H I J K
1 NameA 0XX15930777PS91200PS10PS101300PS9
2 NameX1159XXP555FBX1545PS9
3 NameB1A1234P123PS90512PS10PS101263PS9
It’s important that the data in each column stay with in that same column. Also of course it needs to be on the same row with the same person (NameA and NameB). The Columns that would determine if it’s a duplicate are D and E –. I would need this to be preformed via macro or some easy way so that others will not have a hard time. It will be on a protected Shared Workbook with Excel 2003. I've enclosed a Sample. How can I sort these or accomplish this and maintain the data where it needs to be?
View 5 Replies
View Related
Dec 13, 2012
I need to copy data from multiple cells into a comment box. Right now, I'm highlighting each text in each cell and pasting into the comment box. So A1 data, I am going to the formula bar, highlightin the text and pasting individulally into the comment box.
Also, is there a way to merge multiple cells into one cell . I have text in cells A1:A14 and want all that data into one cell. Is this possible?
View 1 Replies
View Related
Jun 3, 2013
I have been looking everywhere and I can't seem to find what I am looking for. I want to create a Macro that will go into a folder, search every Workbook (each with multiple Worksheets), and copy and paste the data onto a single Worksheet. The data that I want will begin in Row 3 of each Worksheet (the first two rows are titles and headers). The formatting will be the same for each sheet.
The only other part is that Column G has the following formula in each cell that I would like to keep if possible.
Code:
=IF(AND(ISNUMBER(F4),ISNUMBER(D4)),"Complete",IF(ISNUMBER(D4),TODAY()-E4,0))
View 1 Replies
View Related
