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.

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.

View 9 Replies


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.

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


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


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

Contract: Apr-2007 4000 Puts

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


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

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

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:





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:

Column B has another list of items beginning in B2:

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

-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


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


View 5 Replies View Related

Copyrights 2005-15, All rights reserved