How To Concatenate Columns

May 16, 2012

I have the below set of data for example,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

I need the output as,

aaabbbcccdddeeeaaabbbaaabbbcccaaa

As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.

View 3 Replies


ADVERTISEMENT

Concatenate 2 Columns & Delete Orginal Columns

Sep 25, 2007

I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.

Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................

View 4 Replies View Related

Concatenate 3 Columns Into One

Jul 30, 2012

I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column

D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name

This is what i'm trying to get it to look like in column d:

("First Name Last Name" OR "FirstName.LastName") AND @companyname.com

View 6 Replies View Related

Concatenate Two Columns

Nov 21, 2006

writing a macro with following requirement

i need to display the third column with the concatinate values of column 1 and column 2

if A1 = Hi
B1 = All

then C1 should be Hi All

View 3 Replies View Related

Concatenate Columns

May 7, 2007

Item No amountinvoice no
2050377 14,870 105
5000085 120 1102
5000085 15,000 90197361
5000139 16,000 12346
5000237 31,000 56789
2050377 20,000 10111213
2050377 ($20,000)105

I have a spreadsheet with 3 columns. when I'm reimbursed money (see 2050377 for ($20,000) i'm trying to use VLOOKUP to search the spreadsheet and find out the invoice that relates to that reimbursement. but as you can see VLOOKUP finds the first incidence of 2050377 and gives me inv no 105 when it should be 10111213. my formula in C8 is =VLOOKUP (A8,$A$1:$C$8,3) but I need for the VLOOKUP to look at the item number as well as the amount. i've been told that I need to utilise concatenate, but I don't know how to do this.

View 4 Replies View Related

Concatenate Two Columns Of Text

Dec 25, 2008

In Excel 2007 I want to concatenate two columns of text. In Column A all the cells contain a single statement that I want to prefix the statements in the cells of column B (the statements in column B differ from cell to cell) I have used the formula =A1&" "&B1 and this is fine for that row, when I use the fill handle and pull it down the page the formula changes accordingly i.e.=A2&" "&B2, =A3&" "&B3 etc. But when I make the text appear using control+ I only get the concatenation of the first row repeated all the way down, irrespective of the contents of other cells in Column B.

View 14 Replies View Related

Continuously Concatenate 2 Columns

Jan 15, 2008

I have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....

Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?

View 6 Replies View Related

Concatenate Words From 2 Columns

Feb 15, 2008

I need a simple macro to take a list of words in 1 column, and concatenate them with each word from another column

So if Column A had "bird"

and Column B had
food
feeder
house

the output would be

bird food
bird feeder
bird house

I will have many many more words than just "bird" though.

View 3 Replies View Related

Concatenate Multiple Columns With Spaces

Jun 21, 2008

I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.

I've found solutions that say use

=concatenate(A1, " ", B1)

or the VBA equivalent

I have at least 15-20 columns which I wish to concatenate.

Is there an easier way than to type for all 15 columns?

=concatenate(A1," ", B1," ", C1,....)

Also, the number of columns of each row vary

View 9 Replies View Related

Concatenate 2 Columns To Make Every Possible Combination

Jul 19, 2009

Is there an easy way to concatenate two columns and provide every possible combination?

E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2

View 3 Replies View Related

Concatenate Function To Merge Certain Columns

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

Concatenate With Loop Through 2 Columns Of Information

May 28, 2014

I have a list of names with blank cells in Column A cell 2.

I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).

In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.

I need numbers in column B cleared as they are concatenated into the single cell.

Spreadsheet.jpg

View 12 Replies View Related

Auto-Concatenate Columns-row Within Worksheets

Mar 21, 2007

How do I create a code that will concatenate the number in Column B, C and D into Column E for each row within each worksheet of the file, excluding the "Update" worksheet. If there is no number is each column of the row, then do not produce a record for that row in column E. Start executing function on row 2.

eg E2=b2&c2&d2=047, E3=b3&c3&d3=964, E4 cell is empty, because there is no record in b4, c4 and d4

View 9 Replies View Related

Concatenate Columns Into File Path

Nov 15, 2006

I am creating an excel file for a mail merge following this process. I need to take the three columns below and enter the path to the corresponding chart. I have all the charts created in a directory. The naming convention for all of the files is XX-XX-XX.gif. The X's represent the numbers below without the decimal places. Therefore the example below would be 44-43-27.gif. Is there anyway to create a formula to drag down instead of entering the filename's manually? In the end, I want to have the full file path as shown in the XLS.

q_3_abc q_3_teamq_3_yscore
4.4 4.3 2.7

I have 700+ rows to do this for, therefore I want a formula or VBA solution instead of the manual donkey work.

View 3 Replies View Related

Concatenate (Join) Cells Across Columns

Feb 9, 2008

I have a marco that works perfects to Concatenate (join) all of the cells in Column A. However, I want to seperately join all of the cells in each column. So I wanted to the Concatenate of Column B to go in B1, Column C to go in C1, etc (or until there is no data in a column). I think there is a way to change this into a loop, but I don't know how. Below is the macro I have now.

Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("A2:A300")
For Each cel In rng
x = x & cel.Value
Next
.Range("A1").Value = x
End With

End Sub

View 2 Replies View Related

Vlookup And Concatenate Multiple Columns Of Date?

May 8, 2014

Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"

View 3 Replies View Related

Nested Loop To Concatenate 2 Columns Of Data

Oct 27, 2009

I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.

View 3 Replies View Related

Excel 2007 :: Consolidate Several Columns Into One (not Concatenate)

May 6, 2014

I have the following situation and I'm trying to do this using a PC with Excel 2007.

I would like this:
T
N
O
O
C
O
E
I
M
I
T

[Code] ........

To look like this:

T

H

A

N

K

[Code] .........

This procedure will become part of a macro. The number of rows will vary but the number of columns will be the same (11 columns). I don't care too much about the order since I can later sort. As long as the data from all columns in now in one column.

View 7 Replies View Related

Concatenate Multiple Columns But Ignoring Blank Values

Aug 13, 2012

I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?

View 4 Replies View Related

Concatenate Cells In Columns & Change Part Of Font

Apr 12, 2008

I need for it do perform the same action on multiple rows/ cells of data. What adjustment to the code needs to be performed to do so?

Sub fconcat()
Range("C1").Value = Range("A1").Value & Range("B1").Value
For i = 1 To Range("A1").Characters.Count
Range("C1").Characters(i, 1).Font.Name = Range("A1").Characters(i, 1).Font.Name
Next
For i = 1 To Range("B1").Characters.Count
Range("C1").Characters(Range("A1").Characters.Count + i, 1).Font.Name = Range("B1").Characters(i, 1).Font.Name
Next
End Sub

View 3 Replies View Related

Macro To Concatenate Variable Amount Of Columns Based On Cell Value

Jul 23, 2014

I have a table that looks like:

7300
3

7301
2

7302
5

And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.

View 7 Replies View Related

SUMIF - Trim And Concatenate 2 Sets Of 2 Columns Giving Answer In Another Column

Jun 22, 2008

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

A = 2000 text
B = 1234 text

F = 2000 trailing spaces, text
G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

View 9 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.

ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C

I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C

View 5 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two
one

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Opposite Of Concatenate

Sep 29, 2006

I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.

View 3 Replies View Related

Concatenate (two Different Cells Into One)

Dec 11, 2008

I'm having a rather difficult time getting this one.

I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.

A picture is worth a thousand..

View 4 Replies View Related

Concatenate The Dropdown

Jun 19, 2009

I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.

View 7 Replies View Related

Increment Using Concatenate

Sep 2, 2009

I want to write formula in cells(many cells!) using concatenate. The desired output:

In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F

Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.

View 2 Replies View Related

Variable With Concatenate

Jan 2, 2013

I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:

1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8

I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008

Easy enough! Now adding the following is the problem:

1. Cell A2= 12345 Cell B2= 67 Cell C2= 89

I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089

Now D2 has one too many characters from C2

I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.

Is there a way to use a variable with the concatenate or am I using the wrong formula period ??

View 3 Replies View Related

Automatic Concatenate

Jul 9, 2009

A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved