Is There A Formula For Conditional Concatenating
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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Concatenating
I've got a spreadsheet that is being somewhat autogenerated 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 Replies!
View Related
Concatenating Data
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: Apr2007 4000 Calls TimeStampDeliveryDateStrike 18Apr07Apr074000 19Apr07Apr074000 20Apr07Apr074000 Contract: Apr2007 4000 Puts TimeStampDeliveryDateStrike 18Apr07Apr074000 19Apr07Apr074000..............
View Replies!
View Related
Concatenating An Array
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 Replies!
View Related
Concatenating Fractions
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 Replies!
View Related
Concatenating Two (2) Columns
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 Replies!
View Related
Concatenating With Delimiters
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 nonneeded 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 Replies!
View Related
Concatenating 2 Cells Into 1
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 Replies!
View Related
Concatenating Many Columns To One
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 ABCDE 1 9813171944 2 94182023 3 19222454562 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 Replies!
View Related
Concatenating All Combinations From Two Lists
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 Replies!
View Related
Concatenating Special Characters
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 Replies!
View Related
Concatenating Moving Columns
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 inbetween. 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 Replies!
View Related
Concatenating Months And Years
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 Replies!
View Related
Concatenating Cells In 41000 Rows
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 Replies!
View Related
Concatenating Times Based On Date
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 Replies!
View Related
Automatic Updates; Concatenating Name Of Day To Date
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 Replies!
View Related
Concatenating Text And Cell References
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 Replies!
View Related
Concatenating Data From Multiple Duplicate Records
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 661100, 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 Replies!
View Related
Preserve Cells Word Formatting Concatenating Text
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 Replies!
View Related
Percentage With Formula Or Conditional Formula
I am trying to figure out 3 differnent percentage categories but I cannot figure out what formula or conditional formatting that I should be using. The scenario is as follows: For example, Jamaal is authorized for 80 hours per month but only uses 15 hours. What I need to figure out is if he is below 25% of hours funded, then he will be exited from the program. If he is between 25% and 50%, then he will be warned. If he is between 50% and 75%, then he has excellent attendance. Therefore, I came to the conclusion that: 0.25% x 80 = 20 hours 0.50% x 80 = 40 hours 0.75% x 80 = 60 hours Which means that Jamaal is in the below 25% category, meaning he will need to be exited, unless he improves his attendance. But what I want to show on my spreadsheet, is a color RED for below 25%, and other colors for the other categories. But I don't seem to be able to conclude it all into 1 cell. I tried using the Conditional Formatting and the below 25% works but once I add a new rule for between 2550, then my rule for below 25% doesn't work anymore. I don't know if an actual formula exists for this. Does will be a monthly spreadsheet that I will be working off of so it would be nice if all I do is input the total hours used and BOOM it tells me if the client is below 25% hours used or above.
View Replies!
View Related
Formula Using Conditional Formatting
1. I have a range of random numbers within a column, i.e. A1:A9 2. A specific number will appear in a cell at the end of that column, i.e. A11 3. I need to see via conditional formatting which of the above cells add up to that number. I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.
View Replies!
View Related
Conditional Range Formula
Using this basic version of my data: NameGradeWeekJim31Sally31Fred32Claire53Susan43Chris52Ted41Jenn53Nicole43 I am trying to figure out How many 3rd grade, 4th grade, 5th graders there will be for each week. I will need a formula to fill in for each blank cell below. # of 3rd# of 4th# of 5thWeek 1Week 2Week 3 I think I need a formula that would "read" something like: if "Week" is equal to "1, 2, or 3" then within the rows that contain that week, count all "Grade" that are equal to "3, 4, or 5" I tried sumif, counta, if, and combinations of them, but I am not sure I am using them correctly.
View Replies!
View Related
OR Formula In Conditional Formatting
Have 2 conditional formattings going on in a database query and need to apply two more conditions but only have 1 conditional formatting left. I am trying to highlight everything in a row that contains either "HCAT" or "HCAL" in column D. So far I have this, =$D2=OR(HCAT,HCAL) but its not highlighting anything, any clues?
View Replies!
View Related
Conditional Format If No Formula
I was wondering if anyone knows if you can set conditional formatting if the cell contains no formula but is not blank. We use many formulas to calculate cells and we need to the cell to be colored if there is no formula entered. But the cell is not necessarily blank or zero.
View Replies!
View Related
Set Up Formula In Conditional Formating
I want to set up formula in conditional formating so that when: $AF6 = "CA" the color is red or if $AF6 = ("GA","ME","PR") then the color is blue what I came up with is Condition 1 = ($AF6 ="ca") [which will turn the cell red] Condition 2 ="OR($AF6={""GA"",""ME"",""PR""}, "")" [which doesn't do anything]
View Replies!
View Related
Conditional Formula Results
Hello, I cant figure out how to do this! I have a cell B2 that I enter a number in and in cell C2 I want it to add the number from B2 and add 15 to it. I can get the formula "=B2+15" and it works but I wont always have a number in B2 and I want it to say "0" and "0" would be in C2. Any ideas?
View Replies!
View Related
Triple Conditional IF/AND Formula
I am trying to create a conditional/IF formula contingent on 3 different possibilities for f2, with the formula I wish to use based on these 3 possibilities, as shown below: If f2 <=10, f2*100 If f2 >10 but f2<20, 1000+((f210)*50) If f2 >=20, 1500+((f220)*20) I'm not sure how to create this formula using three different conditions, especially as it is unclear to me how Excel needs to have the parenthesis placed.
View Replies!
View Related
Conditional IF AND Formula, Change It To VBA
I am trying to understand VBA codes. Actually I wrote a formula with IF and I want to change it to VBA because it is so long and creates some problems. It is too important for me =IF(AND(0<C6;C6<9);"0";((IF(AND(8<C6;C6<14);(C68)*(8,4);((IF(AND(13<C6;C6<10000);(5*8,4+(C613)*(26,45));" "))))))) The logic is like that, this program will calculate an amount. btwn 0 and 8 days, it is free btwn 9 and 13 days, 8.4 euros per day after 13 to infinity, 26.45 euros per day Therefore for example for 10 days, you have to pay 2*8.4 = 16.8 euros for 14 days, 5*8.4 + 2*26.45 = 92.9 euros
View Replies!
View Related
Conditional COVAR Formula
I am preparing data for an econometric application using excel. The format of the data is as follows: (figues are made up) Portfolio Return(Pn) Return(MarketIndex) Covariance 1  2.1  1.1  =COVAR(B?:B?:C?:C? ... 1  2.2  1.3  =COVAR(B?:B?:C?:C? ... 1  2.3  1.5  =COVAR(B?:B?:C?:C? ... 1  2.4  1.7  =COVAR(B?:B?:C?:C? ... 2  2.4  1.1  =COVAR(B?:B?:C?:C? ... 2  2.3  1.3  =COVAR(B?:B?:C?:C? ... 2  2.2  1.5  =COVAR(B?:B?:C?:C? ... ... ... ... From this I need to calculate the Covariance of a portfolio's returns with the market index. The problem is that I cant seem to specify a condition within the =COVAR(...) formula so that the COVAR Folrmula automatically selects only the applicable pairwise observations... That is, I need to write a COVAR function that will automatically search the first Column for a specific portfolio number and then include the corresponding returns data in the COVAR formula. For example, the first four formula need to return (B2:B5,C2:C5) and the next three formula need to return (B6:B8,C6:C8). I hope this has been descriptive enough of my problem. I am doing my head in trying to use IndexMatch functions but know there must be some simple solution.
View Replies!
View Related
Conditional IF Function/Formula
Is it possible to write a formula that will compare the text in two different cells? And if I can do this can I also have that formuls place a numeric value in another cell? An example of what I am trying to do is: If the text in C2 is equal to the text in C3 place a 1 in C4 or if the text in C2 is not equal to the text in C3 place a 0 in C4.
View Replies!
View Related
Conditional Formatting Formula
I am referencing a cell in order to change the background color of a row of cells if the number in column B is = to the number in a specific cell. The formula used in the conditional formatting is =$B73=$B$10. The reference cell $B$10 contains the following formula =INT(MAX('Assumptions & Inputs'! F8:F9)) The conditional formatting worked fine when I used that formula. I had to change the formula in cell $B$10 so that it would meet a new criteria. When I did that the conditional formatting would not change the color of the row. The new formula that I put in the reference cell $B$10 is ='Assumptions & Inputs'!$D$8+MAX('Assumptions & Inputs'!$F$8'Assumptions & Inputs'!$D$8,'Assumptions & Inputs'!$F$9'Assumptions & Inputs'!$D$9)
View Replies!
View Related
Conditional Formula  Calculate Only If Net Unit Is Zero
Here's the table for one game DateBuy(Unit)Sell(Unit)Net UnitPrice($)Net Profit/Loss($) xx/xx2##2125 ## xx/xx##11150 ## xx/xx##10165 +65 xx/xx##4(4)170 ## xx/xx2##(2)165 ## xx/xx##1(3)180 ## xx/xx3##0180 (10) Number in bracket means negative. ## means empty cell. How can I type a formula, so that every time there's no stock left (ie zero net unit), it will calculate the net profit once.
View Replies!
View Related
Conditional Color Changing And A Formula
My nominal number is 2.000 and in column A, it can not be larger or smaller than .005 which is in column C. If it is larger than 2.005 or smaller than 1.995, I would like to change the cell column B color to red. I have tried =abs(a1b1)c1. but does not do what I want it to.
View Replies!
View Related
Conditional Format For Formula Or Number
I have a spreadsheet where I have certain cells that contain a link to copy a number from another tab in the spreadsheet. At times, I have to plug in a number to try certain values. In doing this, I overwrite the link or formula. I then do an 'undo' to bring it back to the original link or formula. I would like to put a conditional format based on the cell's content. If it is the formula, leave it as is. If it is overwritten by a number, I want it to change colors to remind me to undo my change when I am done. The conditional formatting options I have found so far allow me to format according the the value in the cell but not the nature of it's content: number of formula.
View Replies!
View Related
Complex Logical/conditional Formula
I am working between two worksheets. In one worksheet I have a list of account numbers. In the second worksheet I have the same account numbers in a different order. What I am trying to do is, If the account number from worksheet one is found in the column with the account numbers in worksheet two, I want to display the matching title for the account number that is in the same row but different column in worksheet two. So essentially (if acct # from column x in wks 1= an acct in number in column m from wks 2, display the corresponding title in the same row of the matching acct # found in column m). Is this possible?
View Replies!
View Related
Formula To Count Conditional Formats
I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far. The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this: if cell is numeric if column in (E, M, X, Y) if (value <c1 or > c2) highlight in blue end else ' columns not in (E, M, X, Y) if (value > c1 or < c2) highlight in red end end end My attempt to put together a formula for each cell appears to work, it looks like this for cell B3: '=AND(ISNUMBER($B3), OR( AND(.....................
View Replies!
View Related
Conditional Formula, Then Delete Rows
In column A, is a timeline  based on every 15 minutes. In column H, I use conditional formula to change the cell color of any cell equal to or greater than the value of 00:00:07. I would like to delete all other rows in column H that is under 7 seconds. I would like the rest of the worksheet to stay in tact  so I can see the timeline. (or at least column A) I have tried several macros and vba codes, and it either deletes all the rows or simply does not work with my worksheet. I am unable to attach a worksheet, but I copy/pasted a simple view. This continues down the page, until 24 hours is complete. Where 00:00:00 is, I am looking for anything over/equal to 00:00:07  then delete the rest. The cells are formatted as 'general'.
View Replies!
View Related
Conditional Formula And Calculate Automatically
I am creating a sales per day/week worksheet for my reps to keep track of their sales. I have created the sheet that they only have to enter in what they have sold and it automatically calculates what they have sold per hour in the day, and what that makes their weekly total. The problem is sometimes they will work 5 days, sometimes 4, sometimes 6 or 7. im trying to fix the weekly average formula to divide by 7.5 for 1 day worked, 15 for 2, 22.5 for 3, etc all through 7... this is the formula I have now, obviously it doesnt work =IF(COUNT(B7:H7)=1,SUM(E42,E75,E110,E143,E175,E207,E238)/7.5,IF(COUNT(B7:H7)=2,SUM(E42,E75,E110,E143,E175,E207,E238)/15,IF(COUNT(B7:H7)=3,SUM(E42,E75,E110,E143,E175,E207,E238)/22.5,IF(COUNT(B7:H7)=4,SUM(E42,E75,E110,E143,E175,E207,E238)/30,IF(COUNT(B7:H7)=5,SUM(E42,E75,E110,E143,E175,E207,E238)/37.5,IF(COUNT(B7:H7)=6,SUM(E42,E75,E110,E143,E175,E207,E238)/45,IF(COUNT(B7:H7)=7,SUM(E42,E75,E110,E143,E175,E207,E238)/52.5)))))))
View Replies!
View Related
Formula For Conditional Formatting Wizard
I am trying to create a formula in the Conditional Formatting wizard. I would like the formula to format B2:F2 in a particular way if a count of the number of values in this range equals 5 AND if all values are less than or equal to 25. I have tried with following formula without success: =IF(AND(COUNT(B2:F2)=5),COUNTIF(B2:F2,<=25)=5),))
View Replies!
View Related
Conditional Formatting Based On Now Formula
My attached spreadsheet is for tracking when certain tasks were completed on each case and figuring the number of days that it takes to complete these tasks. The task I am needing help in formatting is the Corrections Received field (column E). We need to track the cases that do not have corrections received within 7 days of the changes being requested (Out for Corrections, column D). The issue is that there would not usually be a date entered in the Corrections Received column when we need to look at those cases...because they would not be received yet. In order to not get an extreme default value (like the 40181 or 40922), I entered the =Now() function in column E (except for first case as an example of what this would look like otherwise). What I was hoping to be able to do is use conditional formatting to look for that Now() formula. If the formula is present, the cell would be highlighted. If a date were entered, even if it was today's date, the fill on that cell would be removed. That way we could tell when the displayed date is a true date and when it is a placeholder until we receive the information. Currently, it either highlights no matter what the cell includes or it doesn't highlight anything...
View Replies!
View Related
Combining Conditions Within Conditional Formula
i am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need. Basically i need to say the following: =if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
View Replies!
View Related
