Is There A Formula For Conditional Concatenating

Mar 23, 2005

I would like to concatenate cells from a column based on the match in the
preceding column with a defined value.

Example:
Col. A B
Row1: T1 Jim
Row2: T3 Paul
Row3: T1 Joe
Row4: T2 Mike
Row5: T3 Caroline

The formula should return for T1 for instance: Jim;Joe;
(for T2: Mike;
for T3: Paul;Caroline
Cells in column A can repeat any number of times.

ADVERTISEMENT

Conditional Concatenating

Oct 5, 2007

I've got a spreadsheet that is being somewhat auto-generated from another program that extracts four different lines of text and puts them into separate cells (Starting at W30, X30, Y30 AND Z30). I believe there are 15 possible combinations. What I would like to end up with is each cell concatenated with " - " separating each one, but ONLY if there is text there to separate.

The function code I have so far gets me very close to what I want, but there are a couple of small issues remaining. See Red Bolded Text.
Here's what I've got so far.

Code: ....

View 9 Replies View Related

VBA - Concatenating Formula With String Variable?

Jan 16, 2013

I'm getting the generic 1004 error on the Range.FormulaR1C1 line, but I can't seem to see the problem.

Code:
Sub UpdateFormulas()
Dim stockFund As String
For i = 2 To finalRow

[Code]....

The mouse-over on the stockFund variable in that last line shows the correct cell address as the value and I checked the If statement to ensure it actually finds the number. I would guess that it would be a syntax error with that line, but it looks correct to me.

View 8 Replies View Related

Finding Right Syntax For Concatenating A Space In If / Then Formula

Apr 16, 2012

Below is the "real formula"

=IF(SUM($F$4-$E$4)= 0," ",SUM($F$4-$E$4)) 'If the value is 0 then enter a space in the cell.

And below is the VBA formula

"=if(sum(" & LastRowCell & "-" & FirstRowCell & ")= 0," & " " & ",sum(" & LastRowCell & "-" & FirstRowCell & "))"

The real formula works, but I am having having difficulties with the syntax of the VBA formula. The above VBA formula works to, but enters a 0 in the cell instead of a space.

View 4 Replies View Related

Conditional Median Formula Into Conditional Quartile / Percentile?

Apr 15, 2014

Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:

(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.

However I can't figure out how to manipulate it into a percentile keeping the condition of

IF(DATA!$B$15:$DZ$15=1000)

View 2 Replies View Related

Concatenating More Than 30 Columns At One Go

May 11, 2009

I have more than 30 Columns. I want to concatenate all these columns and put the value in one cell. When I do it, Exccel gives me an error, "You have entered too many arguments for this function"

View 4 Replies View Related

Concatenating With Delimiters

Sep 21, 2009

I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.

I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)

View 3 Replies View Related

Concatenating Two (2) Columns

Nov 6, 2008

This is the concatenation macro I have for 2 columns. I want to make this more general in that I would like the program to insert a new column to the right and concatenate the 2 columns based on the current cell the cursor is on and the one adjacent to the left (-1). The loop works fine, the modification I need is for the following three (3) lines.

View 2 Replies View Related

Concatenating The Results

Jul 24, 2009

For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):

View 3 Replies View Related

Concatenating An Array

Feb 12, 2006

I have a list of about 1,500 email addresses. To send an email I need to have a comma between each entry. I know I can use the formula =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the 2 strings, but is there a formula to automatication do it for A1 - A1500???

Or is there an entirely different approach I should consider?

View 9 Replies View Related

Concatenating With A Percentage?

Apr 9, 2014

I have a table where a field is called "Expected conversions at [X%]".

X% is a monthly calculation.

So to populate the table row name I tried ="Expected Conversions At"&" "&C28 where C28 refers to a percentage calculated elsewhere in the sheet.

Cell C28 is formated to 1 decimal place. But when I refer to it in this manner it displays to many decimal places.

The result:

Expected Conversions At X.XXXXXXXXXXX

It should say:

Expected Conversions At X.X%

How do I do this so as the % is dynamic and auto updates each month?

View 2 Replies View Related

Concatenating Data

Jan 17, 2008

I need to combine data from two adjacent columns into one in a condensed version of a spreadsheet. As the spreadsheet itself is quite big (over 20,000 rows) I was wondering if there was a quick and efficient way to doing this.

Here is a sample of what I'm working on (I know this would be better off as an image but unfortunately Photobucket is blocked at work):

Contract: Apr-2007 4000 Calls
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000
20-Apr-07Apr-074000

Contract: Apr-2007 4000 Puts
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000..............

View 9 Replies View Related

Concatenating Fractions

Apr 6, 2008

I'm trying to create a model for woodworking where I can enter fractional wood sizes and have Excel create panel sizes and board lengths for cabinetry. I can easily get the inputs formatted in fractions, but then I'd like to have a concatenation formula that joins two fractions and displays a panel size as a fraction. However, when I try to do this, the concatenation formula changes the fractions to decimals.

Example: cell A1 is 3/4" formatted as fraction. Cell A2 is 1 1/2" formatted as fraction. Let's say I want a to concatenate so that A3 reads

3/4 x 1 1/2

But when I use concatenate (A1," x ",A2) the answer in A3 reads

.75 x 1.5

View 9 Replies View Related

Looping And Concatenating

Nov 17, 2009

for i = 2 to i = 23

c.Offset(0, i).Value = combo_& i + 1 & .Value

next i

View 9 Replies View Related

Concatenating 2 Cells Into 1

Apr 27, 2007

I have 1 work book with 2 work sheets. I need the data from 1 cell on the first sheet to be merged with a cell on the second sheet, ( I could also do with this having a line being inserted) can this be done in the same cell or does the merge command only work into a new cell?

View 3 Replies View Related

Concatenating Many Columns To One

Aug 5, 2008

my problem is that i have a huge number of rows and columns, what i want is to concatenate the data in all the columns to the data in the first one, but separately, this is really complicated, so let us assume that i have the following table

-----A-----B-----C-----D-----E

1 ---98---13----17----19----44
2 ---94---18----20----23
3 ---19---22----245---45----62

so after concatenating the value in A to all the values in the other columns and put the result in a new column we get the following output

-----A
1---- 9813
2---- 9817
3---- 9819
4---- 9844
5---- 9418
6---- 9420
7---- 9423
8---- 1922
9---- 19245
10--- 1945
11--- 1962

also we need to take in consideration the empty cells as they must be skipped during the process, also find attached my Excel sheet that needs the modification

View 5 Replies View Related

Concatenating Row Values With Comma?

Dec 9, 2013

I have approx 1000 rows in my spreadsheet. Each row contains one column which is a number. What I would like to do is list all these numbers side by side with only a comma seperating them. Just say the first 3 rows have the following numbers 10003, 10056, 100039. I would like to get the numbers to appear in one cell like this: 10003,10056,100039.

View 3 Replies View Related

Concatenating Large Row Of Values?

Aug 20, 2014

Say I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?

View 5 Replies View Related

Concatenating Months And Years

Oct 14, 2009

Please refer to the attached screenshot of my working spreadsheet.

I'm attempting to concatenate lines 86 and 87, month and year together.

The formula I use is: "=C86 & C87" - but I get a large number instead of "October 2009"

View 4 Replies View Related

Concatenating And Transposing A Row Of Numbers

Jul 13, 2006

I have a column of numbers going down the sheet in column A.

I need to concatenate all the numbers in each cell in the column and
separate each by a comma. This would then be displayed as a long list
all in cell B1.

View 14 Replies View Related

Concatenating Text In Two Cells

Dec 3, 2011

I would like code for concatenating the text in two cells (F2) and (F4) saparated by a gap

View 2 Replies View Related

Concatenating Two Columns Of Text Into Third

Feb 1, 2012

This should be easy, I have a column of First Names, a column of second names and I wish to create a new column with First Name , space, Last name.

I can create a cell specific code such as
= CONCATENATE(B2," ",C2)

However I want to make this into a macro to run on any file that I open for every entry in the columns (I export a file weekly and need to run it each time).

My macro and VBA skills are very basic!

View 7 Replies View Related

Concatenating Values And Not Text?

Aug 3, 2012

I've got a workbook with several sheets, Sheet_1, Sheet_2, Sheet_3 and so on.

Each worksheet has various estimates in them and cell B61 contains a sum

I also have a summary worksheet that contains the sums from each worksheet like so:

Sheet_Name
Sum

Sheet_1
Sheet_1!B61

Sheet_2
Sheet_2!B61

Sheet_3
Sheet_3!B61

In the sum column, isntead of having to manually type in Sheet_1, Sheet_2 and Sheet_3, I'd like to use the value listed in the Sheet_Name column

I've tried concatenating the value in the sheet_name column with !B61 but instead it uses the text value Sheet_1 and doesn't go to the actual sheet.

Is there a clever formula I can try in the sum column?

View 2 Replies View Related

Concatenating Moving Columns

Jun 17, 2009

I receive a report which, amongst the other columns of data, has 7 specific columns with four digit codes on each row. I would like to find a way to concatenate these 7 columns into Column A, inserting a - between each code and adding -uu at the end of the last (7th) code.

I wouldn't be requesting assistance at all with this if the codes were always in the same column. Sometimes, they move 1 or 2 columns left or right depending on the additional (unwanted) data in the columns in-between. The column names, however, never change: Code 1, Code 2, Code 3, Code 4, Code 5, Code 6, Code 7

I was wondering if there was a way to still concatenate these columns into Column A but also take into account that the columns shift...

View 9 Replies View Related

Concatenating Special Characters

Sep 10, 2009

my requirement is to create db2 query with the existing excel sheet data.

Cells in the excel sheet contains special characters like { ) , ( , ’, ; , , , _ , ... }

These cells has to be merged, is there any function in excel to do that?
Concatenate function is not working..! getting data error

View 9 Replies View Related

Concatenating All Combinations From Two Lists

Jul 31, 2003

Column A has a list of items beginning in A2:
1
4
11
29
109
234

Column B has another list of items beginning in B2:
CAR
BOAT
TRUCK

Would like to put a list in column D, starting at D1, that concatenates both lists like this:
1CAR
1BOAT
1TRUCK
4CAR
4BOAT
4TRUCK
11CAR
11BOAT
11TRUCK
etc until all combinations have been covered.

Notes:
-Both lists can vary in number of items
-Both lists will never have a blank cell until the end of the list

I am thinking of a looping macro to do this, perhaps a loop within a loop? But I am stumped how to go about this.

I know this should be done in Access. but I need to do it in Excel.

View 9 Replies View Related

Concatenating Variable Range Of Data?

Jan 9, 2014

concatenating a variable range of data. Attached are examples of what I have and what the desired outcome is.

View 2 Replies View Related

Automatic Updates; Concatenating Name Of Day To Date

Jan 28, 2009

I have two problems with my PivotTable: 1. My PivotTable consists of a constant number columns and varying number rows, eg.......

The dates (with data) should continue to update as I add new date records to the source datasheet, but they don't. I try the refresh option for the table, but it only updates for the dates that it was designed for. I also have automatic updates set, but so far nothing is working. I have to rebuild the PivotTable each day after I add new data...

2. Second problem are the date labels on the rows. I need to have them include the literal day of the week such as
Thursday 01/01/09
Friday 01/02/09

Another less urgent problem that I would like to solve is that I have included additional percent calculations for the total column that I am having to rebuild each time. Can that be included in the PivotTable as well?

View 3 Replies View Related

Code For Trimming Columns And Then Concatenating Them?

Mar 28, 2012

I have many workbooks where Cols B and C and G and H all need to be trimmed of extra spaces, then concatenate B and C into Col D with a space between, same for G and H into col I. (If you hadn't guessed, these are first name and last name columns which need to be combined into one name.)

I have been doing this manually, inserting extra columns, copy paste special values, then concatenate, copy paste special values?

View 6 Replies View Related

Adding Character Then Concatenating Macro?

Mar 8, 2013

I'm looking for a quicker way to take a list of numbers and adding commas, then putting them in the same cell so I can input them into a web database much faster. I can do it long hand by using concatenate first on the list to add commas to each cell, then making another concatenate to put them all in one cell, but doing the latter takes quite a while because of having to input each cell into the formula. Is there a way I can combine the 2 into a macro to accomodate for any length of cells?

Example, I have this list

12
13
14
15
16
17

As my list of numbers, and I need it to look like this in one cell:

12,13,14,15,16,17

View 5 Replies View Related

Concatenating Of Dynamic Cell Range?

Jun 4, 2014

I have a example. I have 4 columns. first 3 columns ID, C1, C2 have the input data. Under a ID we have multiple number of rows. In this example we have 4 rows for ID1. But in real time it can be any number of rows under a single ID. When I click a button the values under ID 1 should get concatenated and stored under the Concatenate column.

I tried some macros but i cant get the logic behind finding the dynamically changing row counts under single ID.

ID
C1
C2
Concatenate

[Code].......

View 5 Replies View Related

Concatenating Times Based On Date

Jul 18, 2007

I am looking to total a sheet as follows into a header row - Is it possible?

A - B - C
1)Date - Estimated Time - Actual Time
2)
3)7/27/07 - 2 - 4
4)7/27/07 - 3 - 4
5)7/27/07 - 2 - 5
6)7/28/07 - 2 - 4
7)7/28/07 - 8 - 6
8)7/28/07 - .25 - .4
9)7/28/07 - 1 - .5

I would like B2 to read "7/27/07 - 7hrs, 7/28/07 - 11.25hrs"
and B3 to read "7/27/07 - 13hrs, 7/28/07 - 10.9hrs"

View 9 Replies View Related

Concatenating Cells In 41000 Rows

Aug 4, 2007

worksheet has about 50000 rows
in col1 there is a single letter, either A or P
in col2 there is an ID number with duplicates
so looks like
A 233
P 250
A 250
P 250
A 300
P 300
etc
I would like a method to concatenate the strings in col1 and place the result in col3 at the top of the list of duplicates so result would be
A 233 A
P 250 PAP
A 250
P 250
A 300 AP
P 300

View 9 Replies View Related

Concatenating Unique Values In A Cell

Jun 5, 2009

I have some data in a sheet and i want the last cell in the row to contain the unique values in that row concatenated. How can it be done?

This is what i want

RowResult that I wantabbccaada
b
c
d

View 9 Replies View Related

Concatenating Text And Cell References

Jul 29, 2006

I need to concatenate some standard text with some cell references but I can't use quotation marks as I would in the normal excel function as it produces an "expected end of statement" error message.

Is there a way around this?

What I want to say is:

Range(B23).FormulaR1C1 = "CONCATENATE(“With ”, Info!C18), “ entries, your expected rate is: ”, H21)"

View 4 Replies View Related

How To Create Destination Cell By Concatenating Cells

Feb 22, 2012

I am trying to create a destination cell by concatenating several cells and text together. A1 = Folder Name, B1 = File Name, C1 = Tab and Cell. In D1, I am creating a formula that concatenates all the cells to create a destination. This obviously doesn't work so I run a macro to copy, paste special values which displays this in E1, ='FolderName[File Name]Tab'!Cell. The pasted formula works but only after you click on the cell and hit the enter key. Is there a better way to do this?

View 2 Replies View Related

Concatenating Cells Based On Common Value In Next Column?

Apr 23, 2013

I'm using excel 2008 for mac. I've got a spreadsheet with around 4000 rows. The data is organized like this:

|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain

What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:

|___________URL_________|______EMAIL______|
|Example Domain
|Example Domain
|Example Domain
|Example Domain
|Example Domain

Where these are 2 adjacent cells.

EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.

View 3 Replies View Related

Concatenating Data From Multiple Duplicate Records

Sep 15, 2006

I have an excel spreadsheet that should have one record for each artifact in a museum collection. The problem is that the museum has consolidated this information from several different sources into one spreadsheet and now there are many duplicate records. They want all the duplicate records removed so that there is just one record for each artifact, BUT there may be different pieces of information in each of the duplicate records. So I want to do the following:

- sort records based on Accession Number (column A)
- find duplicate Accession Number records
- determine which fields (columns) within a duplicate record are unique and concatenate those entries into one master record for each Accession Number
- delete the duplicate Accession Number records

In the attached sample sheet, for Accession Number 66-1-100, we have 6 duplicate records. In the columns, we have information which in some of the records is duplicated, in some it is unique and in some it is missing completely. The museum wants just one master record for each Accession Number and they want all the data from the duplicate records concatenated into one and all the duplicates and blanks discarded.

What I've done so far:............

View 6 Replies View Related

Excel 2007 :: Create Array - Round Value When Concatenating

Feb 17, 2012

Excel 2007, Windows XP

I am concatenating some cells into an array. The amount fields should always have just 2 rounded digits following the decimal. What should change in the following VBA code to achieve that result?

Currently Cells(r, 6) & Cells(r, 7) could have these values:
1.5
24.78945678
45.2341

What I want is rounded values to 2 decimals:
1.50
24.79
45.23

The array is used as an input parameter in a remotely called function module, after logging into the remote system, SAP.

' delim is a | character

' Populate Myarray with data from all rows
' - Only from rows which are not hidden ' 05/23/2008

For r = 1 To row_count 'r is row number
if worksheets("JEMASTER").rows(r).hidden = false then
i = i + 1 'increment myarray index by 1
myarray.AppendRow

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

View 5 Replies View Related

Concatenating Multiple Cells With Different Font Format Query

Jun 14, 2006

if it is possible to concatenate multiple cells that have different font formats? For example; cell A1 is font formatted SYMBOL and contains the letter "D", cell B1 is font formatted ARIEL and contains the word "delta". What I am trying to do is concatenate the two cells while maintaining their font formats.

View 3 Replies View Related

Preserve Cells Word Formatting Concatenating Text

Jun 7, 2008

I have a cell with some text in it that has formatting applied (bold in this case). I want to append some text on the end using VBA, but preserve the existing font formatting information. So say I have "one bold one" in my cell, I'm executing the following: ActiveCell.Value = ActiveCell.Value & "two"

But this wipes out the bold information. Is there any way of preserving the bold that's already in the cell. Either perhaps by copying the Characters object and reapplying it, or by some other method?

View 4 Replies View Related

ADVERTISEMENT