http://chandoo.org/forums/tags/concatenate Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Mon, 20 May 2013 02:40:33 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102956 Fri, 17 May 2013 01:21:37 +0000 chip_excel 102956@http://chandoo.org/forums/ <p>Hi Hui and SirJB7,<br /> Worked like a charm! Thank you guys so much.<br /> Chip </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102951 Fri, 17 May 2013 01:06:23 +0000 SirJB7 102951@http://chandoo.org/forums/ <p>Hi, chip_excel!<br /> In that case I agree with Hui's solution of a user defined function.<br /> Regards! </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102950 Fri, 17 May 2013 00:57:12 +0000 Hui 102950@http://chandoo.org/forums/ <p>If there are more than 1 cell like this as I suspect there is</p> <p>A User Defined Function may be better</p> <p>Copy the code below into a Code Module in VBA<br /> Save the file as a Macro Enabled File *.xlsm or *.xlsb file type</p> <p>Then in your worksheet simply use<br /> =RemoveDuplicates(B2)</p> <p>The code assume Char(10) is the delimiter<br /> You can use another character like a , by using<br /> =RemoveDuplicates(B2,",")</p> <p>The cell where the formula is must have Word Wrap enabled so it will display as you have shown above</p> <pre><code>Function RemoveDuplicates(sDelimString As String, Optional sDelimiter As String = vbLf, Optional eCompare As VbCompareMethod = vbTextCompare) As String &#39; &#39; Code modified from : <a href="http://www.vbusers.com/code/codeget.asp?ThreadID=591&amp;PostID=1" rel="nofollow">http://www.vbusers.com/code/codeget.asp?ThreadID=591&amp;PostID=1</a> &#39; Dim asValues() As String Dim lThisValue As Long Dim sExistingValues As String Dim bEndDelim As Boolean If Len(sDelimString) Then &#39;Check if there is an end delimiter bEndDelim = Right$(sDelimString, Len(sDelimiter)) = sDelimiter &#39;Split the string into an array asValues = Split(sDelimString, sDelimiter) &#39;Loop over the items in the string For lThisValue = 0 To UBound(asValues) &#39;Check if the item is already in the string If InStr(1, RemoveDuplicates, asValues(lThisValue) &amp; sDelimiter, eCompare) = 0 Then &#39;The value is unique, add it to the results RemoveDuplicates = RemoveDuplicates &amp; asValues(lThisValue) &amp; sDelimiter End If Next If bEndDelim = False Then &#39;Remove final delimiter RemoveDuplicates = Left(RemoveDuplicates, Len(RemoveDuplicates) - Len(sDelimiter)) End If End If End Function</code></pre> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102949 Fri, 17 May 2013 00:51:50 +0000 chip_excel 102949@http://chandoo.org/forums/ <p>Hi SirJB7,<br /> Great solution, but the data will constantly be changing and it would be nice to have a formula to keep the cells dynamically updating. I am also sharing with other who will not have the expertise to walk through the steps above :-/<br /> Chip </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102948 Fri, 17 May 2013 00:47:56 +0000 chip_excel 102948@http://chandoo.org/forums/ <p>Hi Hui,<br /> They are always in repeating order.<br /> Chip </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102946 Fri, 17 May 2013 00:38:02 +0000 SirJB7 102946@http://chandoo.org/forums/ <p>Hi, chip_excel!</p> <p>Try doing this:<br /> a) Select the cell<br /> b) Data tab, Data Tools group, Text in Columns icon<br /> c) Choose Delimited by, Next<br /> d) Check Other, and enter in the little text box Alt-010 (line feed) or Alt-013 (carriage return), Next, Finish<br /> e) Select the horizontal range in row 1, Copy<br /> f) Select cell A2, right button, Paste Special, Transpose<br /> g) Delete row 1<br /> h) Select the vertical range in column A<br /> i) Data tab, Data Tools group, Remove Duplicates icon, Accept</p> <p>Not just ten steps, only nine.</p> <p>Regards! </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102945 Fri, 17 May 2013 00:35:28 +0000 Hui 102945@http://chandoo.org/forums/ <p>Are the repetitions allways next to each other ?</p> <p>could it be like?</p> <p>Type 1<br /> Type 2<br /> Type 3<br /> Type 4<br /> Type 3<br /> Type 2<br /> Type 5 </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102944 Fri, 17 May 2013 00:25:38 +0000 chip_excel 102944@http://chandoo.org/forums/ <p>Hi Hui,<br /> It is in one cell.<br /> Chip </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102943 Fri, 17 May 2013 00:23:54 +0000 Hui 102943@http://chandoo.org/forums/ <p>Chip</p> <p>Is all the above data in 1 cell or in Multiple cells? </p> http://chandoo.org/forums/topic/eliminating-doubles-in-cells#post-102941 Fri, 17 May 2013 00:00:26 +0000 chip_excel 102941@http://chandoo.org/forums/ <p>Hi Everyone,<br /> I have a list of hard key, carriage spaced data in a cell that contains repeated lines of information.<br /> The data in the cell looks like this:</p> <p>Type 1<br /> Type 2<br /> Type 3<br /> Type 3<br /> Type 4<br /> Type 5<br /> Type 5</p> <p>Any tips on a formula I can use in an adjacent cell to eliminate the doubles?<br /> Best,<br /> Chip </p> http://chandoo.org/forums/topic/change-text-colour-when-using-concatenate-and-text-function#post-101568 Thu, 09 May 2013 22:37:35 +0000 SirJB7 101568@http://chandoo.org/forums/ <p>Hi, mr_hiboy!</p> <p>Following oldchippy's idea you could try this:<br /> - cell is green by default<br /> - CF1: =SIGN(IFERROR(FIND("-",A1),0))+SIGN(IFERROR(FIND("+",A1),0))=2 and format for yellow<br /> - CF2: =SIGN(IFERROR(FIND("-",A1),0))=1 and format for red</p> <p>And you don't have to split the cells.</p> <p>Regards! </p> http://chandoo.org/forums/topic/change-text-colour-when-using-concatenate-and-text-function#post-101557 Thu, 09 May 2013 21:20:54 +0000 mr_hiboy 101557@http://chandoo.org/forums/ <p>How do, actually that would cover a few of the cells, I have one that can have be +/-, so I've split the cells!</p> <p>Cheers<br /> Paul </p> http://chandoo.org/forums/topic/change-text-colour-when-using-concatenate-and-text-function#post-101551 Thu, 09 May 2013 20:42:53 +0000 oldchippy 101551@http://chandoo.org/forums/ <p>Hi,</p> <p>If you are saying something like this 10% / -2%, then I don't think that possible, BUT maybe if you just coloured the cells green by default to assume all the figure are positive, then if there is a negative % then use CF with this</p> <p>=FIND("-",A1) then format the fill to RED </p> http://chandoo.org/forums/topic/change-text-colour-when-using-concatenate-and-text-function#post-101549 Thu, 09 May 2013 20:11:41 +0000 mr_hiboy 101549@http://chandoo.org/forums/ <p>Hi,</p> <p>I've got two values in a cell, one value is a %age of budget the other variance in £s. I'd like to be able to have green for positive and red for negative, formula is as follow:</p> <p>=TEXT(VLOOKUP($B$2,Data!$B$9:$DA$32,2,FALSE),"0%")&amp;" / "&amp;TEXT(VLOOKUP($B$2,Data!$B$9:$DA$32,19,FALSE),"0%")</p> <p>I know i could split it out into 2 or 3 cells but that would mean a lot of reworking!</p> <p>thanks in advance</p> <p>Paul </p> http://chandoo.org/forums/topic/validate-cell-data-that-is-populated-by-formula#post-96128 Sat, 20 Apr 2013 00:12:00 +0000 TJ 96128@http://chandoo.org/forums/ <p>Thank you - it doesn't look like I'm going to be able to get back to the spreadsheet in question before Monday, so I'll be sure to come back if I have more questions after applying the changes. </p> http://chandoo.org/forums/topic/validate-cell-data-that-is-populated-by-formula#post-95549 Fri, 19 Apr 2013 03:58:13 +0000 SirJB7 95549@http://chandoo.org/forums/ <p>HI, TJ!<br /> Don't be afraid nor shy and ask any doubt, we're all here to learn, remember that.<br /> You're right, ksRng3 is the reference to your CostCenter range, and if it's in another worksheet you should adjust the code a little. I'm going to modify it on my previous post so copy it again and replace in your workbook. Good luck.<br /> Regards! </p> http://chandoo.org/forums/topic/validate-cell-data-that-is-populated-by-formula#post-95476 Thu, 18 Apr 2013 23:10:05 +0000 TJ 95476@http://chandoo.org/forums/ <p>Thank you so much for your reply and your assistance - I spent all day searching online and here on the forum before posting, so I'm glad to know it's possible. I'm very much a novice when it comes to VBA, so I hope you don't mind me asking questions as needed.</p> <p>The third constant being declared at the beginning of the code - this is my named range of valid cost centers, correct? Can I replace "L10:L14" with the name of the existing range, or does it need to be a sheet/cell reference? The named range is on a different sheet (in the same workbook) than the sheet we're working with. </p> http://chandoo.org/forums/topic/validate-cell-data-that-is-populated-by-formula#post-94891 Thu, 18 Apr 2013 01:55:35 +0000 SirJB7 94891@http://chandoo.org/forums/ <p>Hi, TJ!</p> <p>First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.</p> <p>As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).</p> <p>Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.</p> <p>Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.</p> <p>And about your question...</p> <p>If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.</p> <p>In this case you should use the worksheet change event of the worksheet, placing this code in the VBA editor:<br /> -----<br /> <pre><code>Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) &#39; constants Const ksRng1 = &quot;H12&quot; Const ksRng2 = &quot;I9&quot; Const ksWS3 = &quot;Sheet with the cost center list&quot; Const ksRng3 = &quot;L10:L14, range of the cost center list (just address)&quot; &#39; declarations Dim rng1 As Range, rng2 As Range, rng3 As Range Dim I As Long, A As String, A1 As String, A2 As String, bOk As Boolean &#39; start &#39; ranges Set rng1 = Range(ksRng1) Set rng2 = Range(ksRng2) Set rng3 = Worksheets(ksWS3).Range(ksRng3) &#39; input changed If Application.Intersect(Target, rng1) Is Nothing And _ Application.Intersect(Target, rng2) Is Nothing Then Exit Sub A1 = Left(rng1.Cells(1, 1).Value, 2) A2 = Left(rng2.Cells(1, 1).Value, 2) If Len(A1) &lt;&gt; 2 Or Len(A2) &lt;&gt; 2 Then Exit Sub If Target.Cells.Count &gt; 1 Then Exit Sub &#39; process A = A1 &amp; A2 With rng3 I = 1 bOk = False Do Until bOk Or .Cells(I, 1) = &quot;&quot; If .Cells(I, 1).Value = A Then bOk = True Else I = I + 1 End If Loop If Not bOk Then MsgBox &quot;Combination selected invalid!&quot;, _ vbApplicationModal + vbCritical, &quot;Warning&quot; End If End With &#39; end Set rng3 = Nothing Set rng2 = Nothing Set rng1 = Nothing End Sub</code></pre> <p>-----</p> <p>Regards! </p> http://chandoo.org/forums/topic/validate-cell-data-that-is-populated-by-formula#post-94883 Thu, 18 Apr 2013 01:19:48 +0000 TJ 94883@http://chandoo.org/forums/ <p>Hi - I'm not sure if what I'm trying to do is possible, but I'm hoping it is. I haven't had any luck searching out an answer so far.</p> <p>In Brief:<br /> In Excel 2010 I have a sheet where the user selects values in two different validated lists and the first two characters of each selection are concatenated into a third cell. I want to validate the result of this third cell and pop up a Stop error if the concatenation creates an invalid entry.</p> <p>In Detail:<br /> I have validated lists of Departments and Regions; when the user makes a selection on both lists, their selections are trimmed and concatenated in a third cell to create a Cost Center. For example:</p> <p>Formula in J12: =IF(H12="(SELECT)","",IF(I9="(SELECT)","",CONCATENATE(LEFT(H12,2),LEFT(I9,2))))</p> <p>Dept List (cell H12): 10 Accounting, 20 Sales, 30 HR<br /> Region List (cell I9): 01 NW, 02 SW, 03 NE, 04 SE</p> <p>The user selects from these lists to determine their numerical cost center. For example, selecting '10 Accounting' and '03 NE' will populate the result '1003' in the target cell (J12).</p> <p>However, I need to be able to validate these populated results, because not all combinations are valid. For example, if a user selected '10 Accounting' and '01 NW' instead of '03 NE' I need a Stop error to occur because there is no Accounting department operating in the NW region so '1001' is not a valid cost center (the user knows this, but selected the wrong region without realizing the mistake).</p> <p>Currently J12 contains the formula above and has validation applied pointing to a list of valid cost center combinations (I un-checked 'In-cell dropdown' in the validation window because this cell will be locked and the user should not be able to change or select anything in it). I was hoping that when the cell result populated based on user selection in its parent cells, the error would pop up if the result did not match an entry on the validation list. But this is not happening.</p> <p>I'm not sure this is possible, but if it is, any help will be greatly appreciated. </p> http://chandoo.org/forums/topic/concatenate-to-link-cells-and-create-a-link-across-workbooks-on-a-network-drive#post-87993 Sat, 30 Mar 2013 12:16:24 +0000 Shoebird 87993@http://chandoo.org/forums/ <p>I am able to get this string to work on my C:/ but not on the corporate X:/ IT changed some settings but it still seems to be an issue.</p> <p>One additional question for you Hui since I have seen your posts all over and you obviously have a lot more exceo experience than myself. Indirect seems to need to have the workbook open. Can I use Index instead of Indirect? And what does the + symbol infront of Index do that is different than without?</p> <p>Thanks again! </p> http://chandoo.org/forums/topic/concatenate-to-link-cells-and-create-a-link-across-workbooks-on-a-network-drive#post-87988 Sat, 30 Mar 2013 11:53:15 +0000 Shoebird 87988@http://chandoo.org/forums/ <p>Thank you very much for the invite Hui.<br /> You are absolutely right. This was just a mistake made when I was entering it into this forum. But for some reason, I am still having issues with the formula. Any other thoughts? </p> http://chandoo.org/forums/topic/concatenate-to-link-cells-and-create-a-link-across-workbooks-on-a-network-drive#post-87838 Sat, 30 Mar 2013 03:03:36 +0000 Hui 87838@http://chandoo.org/forums/ <p>Shoebird</p> <p>Firstly, Welcome to the Chandoo.org forums</p> <p>Your formula is missing an &amp; near the end and a ' near the start<br /> If you change it to<br /> =INDIRECT("'"&amp;A3&amp;"PDU Data"&amp;A1&amp;" "&amp;A2&amp;"\[PDU Trend "&amp;A1&amp;" "&amp;A2&amp;".xls]PDU Trend "&amp;A1&amp;" "&amp;A2&amp;"'!"&amp;"B3")</p> <p>It evaluates to<br /> =Indirect("'x:\data\PDU DataFeb 2013\[PDU Trend Feb 2013.xls]PDU Trend Feb 2013'!B3") </p> http://chandoo.org/forums/topic/concatenate-to-link-cells-and-create-a-link-across-workbooks-on-a-network-drive#post-87810 Sat, 30 Mar 2013 01:34:03 +0000 Shoebird 87810@http://chandoo.org/forums/ <p>Having a bugger of time with this one.</p> <p>Creating a short cut for users so all they have to do is enter a date.<br /> The date is broken down into year and month in seperate cells.<br /> I am then trying to link cells and text to create a pathway to draw data out of specific workbooks.</p> <p>Example. A1=Feb and A2=2013 and A3=x:\Data File\<br /> =indirect(A3&amp;"PDU Data"&amp;A1&amp;" "&amp;A2&amp;"\[PDU Trend "&amp;A1&amp;" "&amp;A2&amp;".xls]PDU Trend "&amp;A1&amp;" "&amp;A2&amp;"'!"B3</p> <p>I know it is lengthy but thanks for help in advance. </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-81006 Wed, 27 Feb 2013 18:57:32 +0000 Hayley 81006@http://chandoo.org/forums/ <p>Oh...that was it...the data was in TEXT format.</p> <p>Thank you both for your help. </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-81003 Wed, 27 Feb 2013 18:49:17 +0000 shrivallabha 81003@http://chandoo.org/forums/ <p>Hi Hayley,</p> <p>Try this if Dhamo's suggestion doesn't work out.</p> <p>1. See how the cell has been formatted by pressing CTRL + 1<br /> 2. If it is set to 'TEXT' then change it to 'GENERAL'.<br /> 3. Then press F2 followed by ENTER. </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-80998 Wed, 27 Feb 2013 18:41:21 +0000 Dhamo 80998@http://chandoo.org/forums/ <p>You mean, C1 is displaying this (=a1&amp;" "&amp;b1) instead of 123 Stone Street Apt. #4.? if yes, please disable viewing the formulas(Excel menu - Formulas - Show formulas).</p> <p>If I am wrong, please post a sample file to analysis further.</p> <p>=a1&amp;b1 will do the same as what concatenate(A1,B1) will do. </p> <p>To add a space between two address I used this (&amp;" ") </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-80996 Wed, 27 Feb 2013 18:34:04 +0000 Hayley 80996@http://chandoo.org/forums/ <p>Thanks Dhamo. But no, the result was just to show exactly in the cell =a1&amp;" "&amp;b1 </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-80995 Wed, 27 Feb 2013 18:32:09 +0000 Dhamo 80995@http://chandoo.org/forums/ <p>Assuming your values are at first row. Please change the row number according to your data. </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-80994 Wed, 27 Feb 2013 18:30:30 +0000 Dhamo 80994@http://chandoo.org/forums/ <p>Hi Hayley,</p> <p>Try this in column c.</p> <p>=a1&amp;" "&amp;b1 </p> http://chandoo.org/forums/topic/concatenate-acting-funky#post-80993 Wed, 27 Feb 2013 18:22:53 +0000 Hayley 80993@http://chandoo.org/forums/ <p>I was recently upgraded to Excel 2010 so maybe that's why I can't figure this out, but I have 2 columns that contain address information. Column A is 123 Stone Street. Column B is Apt. #4. I want Column C to comibine them into one cell: 123 Stone Street Apt. #4.</p> <p>I tried =concatenate(A1,B1) and the results literally said: =concatenate(A1,B1)</p> <p>Help? How do I get it to actually perform the function? </p>