Combining/merging Columns
Mar 18, 2009
ive got columns of dates that i want to aggregate up into a single column of distinct dates. the dates columns are of varying length, some with gaps in the middle of the column, and some columns are blank. dates in teh columns may duplicate, but i only want distinct dates copied in the single column.
i cant do this by hand because there are dates in around 200 columns so i need a macro or way to do this quickly. ive attached a picture to explain the problem.
View 5 Replies
ADVERTISEMENT
Jun 13, 2014
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
View 5 Replies
View Related
Dec 21, 2007
Hi i need to combine and merge difefrent data into groupings. EXAMPLE
1ax
1bx
1cx
2ax
2bx
2cx
It needs to end up as
1ax
2b
c
With a 2 or 3 empty lines until the next set of data
But if for example i have this:
1ax
1bx
1cx
2ax
2bx
2cz
3 c z......................
View 9 Replies
View Related
Jan 28, 2010
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
View 12 Replies
View Related
Dec 20, 2006
i would like to merge two columns as explained below
for ex
the 1st column is:
1
2
3
4
5
the 2ed column is:
1
2
3
4
5
the merged columns should be:
1
1
2
2
3
3
4
4
5
5
View 9 Replies
View Related
Jul 16, 2009
I have two very long columns of client names. The columns represent reports that were run at different times on the same information.
What I want to do is merge the two clomuns. The issue is that there are many names that are the same but some that are different. Is there a way that you can merge the info. So it ignores the client names that are the same but auto adds the ones that are new. There are financials in other columns that I want to have come along during the merger.
View 7 Replies
View Related
Jul 8, 2014
i have two columns with account numbers, i need one column with both account numbers, separated by a hyphen, see below the first line would be 10-15.
10 15
10 20
10 30
10 40
10 41
10 41
View 3 Replies
View Related
Aug 12, 2010
I'm looking for a way to put data into 1 column from 2 different columns, alternating every other row.
The data looks like this:
A B C
1 2
3 4
5 6
And I want C to look like this:
C
1
2
3
4
5
6
I don't necessarily need to keep the data in columns A and B, as long as C follows this format.
Excellent, just what I was looking for. I couldn't get the UDF working right, I'll try messing around with it, but the Index solution works great!
View 5 Replies
View Related
Jan 24, 2009
Give me the code to attached one column of data to the bottom of another?
ie the next available cell
so column a has 250 lines of data the data from another column will then go diectly into 251
thats not the number i want but the next available
View 13 Replies
View Related
Jul 26, 2006
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of
converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data
without one column overwriting the other. If only there were an automatic
way to merge two columns of data and to be able to place a delimited
character in-between, just like the “Convert Text to Columns Wizard”, except
in reverse. Currently, the only way to merge two columns of data is to
manually go row-by-row and cut and paste them together. However, for 500,000
rows of data… this is impossible. Or to use a function to merge two columns,
however this requires that the original two columns remain. This is also
unacceptable. If Microsoft really wants to make Excel more functional, how
can this vast improvement be overlooked?
View 10 Replies
View Related
Aug 3, 2014
I've been trying unsuccessfully to find a way to merge data into a matrix, but can't quite figure out a way to manage it. I have a list of employees in column A, in the list there are employees, many with multiple instances, and in columns C onwards there are dates in dd/mm/yyyy format.
Each line represents the number of nights in a trip, from 1 to 35 nights and the start to finish dates. Where as some employees may only have one row with one date others may have 20 rows with between 2 to 35 dates.
I'd like to merge this into a matrix with one line for each employee showing which nights in the year they were away (tab matrix) which has a day of the year to illustrate which employees were away on which nights of the year.
I've uploaded an example to highlight what I mean.
I've tried a pivot and an index match formula but couldn't get either to work.
View 7 Replies
View Related
Dec 5, 2013
I have two columns
Req | Expiry Date
yes 11/12/2010
yes 08/06/2012
yes 02/07/2017
how do i merge both columns into one.. not losing any data hence
Req Expiry Date
yes 11/12/2010
yes 08/06/2017
I am using excel 2007 ITS FOR WORK!
View 9 Replies
View Related
Feb 27, 2009
Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!
View 2 Replies
View Related
Aug 28, 2008
I have monthly reports that come to us from a Data Warehouse. They vary in size(row length). The problem is that the information in one column actually belongs in the next column (i.e. C to D). As this information in Column C is just an "offset" from Column D there is no issue with overwriting any data in column D. Manually, I could copy the columm and Paste Special, Skip Blanks, to column D.
I've been trying to do this in code as some reports can be fairly lengthy: As there are some blank rows, I've inserted this FinalRow procedure, to provide the size of each report:
FinalRow = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Rows(1).Row - 1
However, in trying to use the FinalRow value in a "Copy the contents from Column C....Cells(20,3 to FinalRow, 3) and then PasteSpecial to Column D (Operation:=xlNone, SkipBlanks:=True) has not been productive. It keeps producing a number of Errors, in every way that I have tried to make this work.
View 9 Replies
View Related
Dec 16, 2012
Current Data:
File 1:
Each set of data is listed in either two or three rows
Eg.,
ID
Date
Filename
ID
Date
ID
Date
Filename
The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)
File 2:
Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)
Eg.
ID Detail
The goal is to merge properly formatted data from File 1 to File 2
Eg
ID Date Filename Detail
View 14 Replies
View Related
Mar 22, 2012
My data in excel looks like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5
What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:
A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7
View 2 Replies
View Related
Aug 2, 2006
If I have two columns which are lists of names, how do I make a new column which merges the names from each column into one list, and will refresh when new data is added to the two originals.
View 14 Replies
View Related
Apr 6, 2012
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
View 2 Replies
View Related
Dec 12, 2007
see the picture below to get an idea of what i want to do.
http://aycu15.webshots.com/image/362...0389296_rs.jpg
View 9 Replies
View Related
Oct 28, 2009
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False
Row = 1
col = 1
For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Feb 23, 2010
I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.
View 2 Replies
View Related
May 22, 2014
RCA Page for forum.xlsx
I'm trying to get entries from one table to appear in another. The first table is like this:
DateDate
Customer Column1
Description Column1
Customer Column2
Customer Description2
Created By
The table I want to put the data into looks like this:
Date
Customer
Description
Created By
View 2 Replies
View Related
Mar 18, 2013
Date formatting. I have two columns first column is for the date (3/6/2013) and the second column is for the time (12:37:16 AM). I would like to combine the content of both columns to be like (2/11/2013 12:35:00 AM) .
I am using concatenate but I don't have any luck. How to combine the content of my two columns.
View 4 Replies
View Related
Mar 3, 2014
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
View 2 Replies
View Related
Dec 21, 2008
I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.
On the 'Input' sheet I have a button called Format Graphs which controls the following macro:
View 6 Replies
View Related
Jun 9, 2009
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies
View Related
Sep 28, 2009
I am waaaaay in over my head here. I've not done any scripting/macro/vb/whatnot work in Excel, but I'm staring down a potential all-night project manually merging data.
Here's what I have:
View 6 Replies
View Related
Jun 27, 2012
Is there a function in Excel that allows you to combine non-numeric columns as a list?
For example, 91401 is a zip shared by three cities: Van Nuys, Sherman Oaks and Valley Glen.
The data is in a workbook as three rows (each with 91401 as the first column, and cities listed individually:
91401 Van Nuys
91401 Sherman Oaks
91401 Valley Glen
I am eventually wanting to do a v-lookup with the zip, but the duplication is causing problems. I want the associated cities listed, but only want one row. The cities could be listed, separated by commas (preferably), like below:
91401 Van Nuys, Sherman Oaks, Valley Glen
The list of zips I have is several thousand long, so it's not practical for me to do it manually. Is there a "pivot table" of text that I could use?
View 5 Replies
View Related
Mar 7, 2014
I am merging the following two IF formulas together.
=IF(ISBLANK(M4),"NOT RECEIVED",0)
and the second formula I want to merge is
=IF(A2+5>=M2,0,"LATE")
View 4 Replies
View Related
Jul 8, 2008
I have an excel sheet with first and last name and I have another column called username.
The username will be "firstnamelastname" all lower case and no spaces.
I know I can use C1=lower(&A1""&B1)
View 13 Replies
View Related
