Concatenate Multiple Cells Into Single Cell With Line Feeds
Dec 21, 2007
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.
would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
View 7 Replies
ADVERTISEMENT
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 Replies
View Related
Jul 9, 2012
I am trying to create a single input sheet that automatically feeds data into several other worksheets within the same workbook. Some of the sheets use the same data, some are completely independent of each other. The point of having this is so that users can go in, fill in the input sheet, and the other sheets will automatically be filled with data from the input sheet.
View 1 Replies
View Related
Mar 30, 2014
I have a multiple line texts I want to convert it in single line like for Example:
The following contents is to be converted in a single line
contents:
[aaaaaaaa] [bbbbbbb]......so on
[cccccccc] [ddddddd]......so on
[eeeeeeee] [fffffffffff]......so on
Result:
[aaaaaaaa] [bbbbbbb] [cccccccc] [ddddddd] [eeeeeeee] [fffffffffff]....so on
View 4 Replies
View Related
Jan 24, 2008
I have 2 sheets in a work book. Sheet 1 I paste information from an email into A4.
Sheet 2 gathers the information and places it in the cell formating the text so I can import it properly to another program.
I want to take notes that a person fills in (they fill out an online form with their personal information and sometimes leave comments, but not always) and paste this into Sheet 1. When I paste the comments into Sheet 1 it is pasted into mulitple cells. I want sheet 2 to format the text from these mulitples cells into one cell with spaces placed correctly ....
View 9 Replies
View Related
May 19, 2008
Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?
I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:
Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"
View 9 Replies
View Related
Feb 10, 2010
Single coding line into multiple using underscore
How can i break this up
View 3 Replies
View Related
Oct 16, 2007
I import data into an excel spreadsheet where a unique job number may have multiple costs against it. These import as separate lines, repeating the job number.
The data imports on a sheet called 'DataImport'. From that sheet I transfer some of the imported fields onto another sheet.
What I need to be able to do is transfer the job number and a sum of the costs onto the other sheet.
The data is run regularly and I delete all the fields on the DataImport sheet in the macro each time the import is run.
View 9 Replies
View Related
Jan 18, 2011
Here is the set up:
I have a calendar sheet where the dates go down the rows, and there is one column per member of staff. The staff is split in to three streams (different apps to cover, etc).
In the calendar, we mark a "C" for when someone will be on-call, R for when they will be doing a release, and so on.
In a separate sheet in the workbook, rather than having people look at the whole calendar of 30+ people to see who is on what shift for a given stream, I have an On-Call Rota where the rows are again the dates, and the columns are the streams. In each cell of this sheet, there should be the names of all the people of that stream who will be on-call for that date, so a concatenation of the actual staff members from the calendar, separated by CHAR(10).
I have been unable to find a combination of INDEX, MATCH, LOOKUP, VLOOKUP, etc, etc that gets me past the main problem: they only ever match the first C (or the first R for the On-Release Rota). I can't get a formula for a single cell in the rota that will return all the names where there is a C in the calendar in such a way that I can concatenate them.
Here is the kind of thing I have had to do .....
View 7 Replies
View Related
Jun 10, 2006
I have some code (authored by Dangler) that i want to pull not just one range but also the contents of one specific cell and then copy the range and cell to a line on another spreadsheet.
Question:
f .Cells(i, 8).Value > 0 Then
.Range(Cells(i, 1), Cells(i, 200)).Copy
Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)
End If
I want to add a single cells contents to the LINE that is being copied .... something like this...
f .Cells(i, 8).Value > 0 Then
.Range(Cells(i, 1), Cells(i, 200)) AND A1.Copy
Sheets("all").Range("a65536").End(xlUp).Offset(1).PasteSpecial (xlValues)
End If
the output would be like this: CELL CONTENTS, RANGE CONTENTS
or
K21, 40012122 400 450 450 50 001 BAGS
View 9 Replies
View Related
Apr 20, 2009
I have been using the CONCATENATE function to merge 2 strings together into a single cell. However the problem is, the third concatenated cell depends on the other 2 existing. The idea is I want to take 2 columns of data, and use a formula to merge them, then delete the original 2 data columns so I have a single column with the full data merged.
COL 1 (A3) = First Name
COL 2 (B3) = Last Name
COL 3 (C3) = Full Name (wanted)
So I do =CONCATENATE(A3,B3) and that puts the fullname in C3, but I want to sort of "flatten" C3 so that I have the fullname by itself and I can delete the original cells (A, and B)
View 5 Replies
View Related
Sep 25, 2012
I have values on master sheet and want to link those values to source documents in the same workbook. But the values on master sheets are made up of more than one value on another sheet.
For example on master sheet I have $137,000 that is made up of values in P7 + Q7 + P57 + Q57 on source sheet. If user wants to see where this number is coming from; the user will click the number to go to source document.
On a single valve I linked to the source sheet to a single cell. One thing I was thinking, using conditional format, so if one of the cells is selected in source document the other cells that combine the total of the master sheet cells should be highlighted, just my 2 cents.
View 6 Replies
View Related
Nov 4, 2009
I'm trying to take the sum of multiple cells and return it to one single cell using the Vlookup
For Example I want to match the ID numbers from one spreadsheet and use the list of codes to return the value of the sum of all matched numbers. So in this example I would want the number 65 in one cell...
View 9 Replies
View Related
Jun 19, 2008
i need to copy the values from more than one cell and need to paste all the values in the single cell (if possible values seperated by commas).
View 2 Replies
View Related
Dec 31, 2008
I have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase.
Example:
="Jackpot: &G2 (&G1) / &G3 = &G4 each"
I already tried this:
="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each"
Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.
View 9 Replies
View Related
Dec 5, 2012
I have 5 columns of data where each column of data has two number in it separate by a space where the headers for each column is c1, c2, c3, c4 and c5. for example
c1 c2 c3 c4 c5 c6 c7 etc
1 1 1 2 2 2 2 1 1 1
3 3 3 4 4 4 4 3 3 3
etc
where each of these number pairs is under a separate column. The preview option for this forum editor is showing quite a difference between intended presentation and actual..
What I am looking to do is for each line item is to put the content of each row into a single cell with vertical alignment of the pairs of numbers. for example
c6
1 1
1 2
2 2
2 1
1 1
3 3
3 4
4 4
4 3
3 3
where each group of five pairs is in a single cell.
I am looking to do this in as automated an approach as possible. I dont want to have to ctrl-enter for example 4 times for each cell in c6 for 1000 different line items..
View 5 Replies
View Related
Apr 16, 2012
Why is the VBA for putting a simple border around a cell so convoluted. For example cell A2:
Code:
Sub CellBorder()
Range("A2").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
[Code] ........
So many lines to put a simple - single line border (square) around a cell.
View 3 Replies
View Related
Nov 29, 2013
New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:
(NOTE: does not contain actual names or info) Book1.xlsx
This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?
While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.
View 6 Replies
View Related
Aug 22, 2008
My basic problem is I compile a newsletter for my company. Many people write me E-mails and i paste their "daily accomplishments" into a template. My question is if there is an easy way to insert bullets at the beginning of each line inside the cell. My company wants it prepared with a certain type. I am attaching a sample.
View 2 Replies
View Related
Apr 15, 2014
I am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
View 5 Replies
View Related
Sep 23, 2013
Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.
View 5 Replies
View Related
Nov 25, 2009
I have a macro to concatenate multiple cells but unfortunately it is not giving the result,
It has to concatenate below mentioned cells and put the result in column N
Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
Range("N1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
View 9 Replies
View Related
Jan 21, 2009
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
View 9 Replies
View Related
Jun 26, 2008
I have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.
View 9 Replies
View Related
Mar 21, 2008
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies
View Related
May 23, 2014
I have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:
john.doe@dm.com, john.doe1@dm.com, john.doe2@dm.com
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
=CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)
View 2 Replies
View Related
Mar 4, 2012
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
View 1 Replies
View Related
Feb 22, 2014
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
View 4 Replies
View Related
Apr 24, 2012
I wish to create a Look up that looks at a range of cells on the same line Eg C6:G6
Then looks at a different tab and completes the look up.
E.g.
TCR!A:B,2,FALSE
No within the cells C6:G6 i want the formula to only use the highest match
E.g.
C D E F G
070031070031#N/A#N/A070
#N/A#N/A#N/A#N/A070
So in the first line i want the result to use the number 070031 and in the second example i want it to use 070 for the look up.
View 1 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
View 9 Replies
View Related
