1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CHAR Function

Discussion in 'Excel Dashboards' started by Krishna2385, Jul 11, 2017.

  1. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    Hi,

    I have a daily activity which requires me to update the company names which are in separate cells into one cell based on the count. for example : if the count is two I need to combine company 1 and company 2 in one cell , like wise for count 3 and so on.
    the data is huge the challenge for me is to add up the formula for the count more than 5. the count varies from data to data and it is not mandate that count 2 is followed by 3 nd 4 , if the data is not there it will skip 3&4 and updates 5 .
    the formula which am using to update count 2,3 & 4 are as follows:
    =C2&CHAR(10)&C3
    =C3&CHAR(10)&C4&CHAR(10)&C5
    =C4&CHAR(10)&C5&CHAR(10)&C6&CHAR(10)&C7
    as I mentioned if its a sequential order I would have updated a simple macro but the count is not stable and may vary

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    What version of Excel do you have?

    Edit: Oh, and what you have currently is the desired result?
  3. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    2010 is the version....

    yes the sheet which I have uploaded is the result I am looking for
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Hmm, can't think of smart way of doing this without later version of Excel (CONCAT function introduced in Excel 2016 to replace old CONCATENATE).

    Also, let me see if I understood your operation correctly. For each row, using Count in Column C, you concatenate x number of cells from each row? I ask, since, I can't think of reason for doing this.

    At any rate, don't think I can help you with formula. Are you ok with VBA solution for this?

    By the way, why are you concatenating CHAR(10)? If you want to do line break, it's best practice to use both CHAR(13) & CHAR(10) together. As some editor will not interpret CHAR(13) or CHAR(10) alone as line break. But both together, (vbCrLf/vbNewLine), will result in new line feed.
  5. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    Well VBA works for me.... and the reason to use CHAR(10) is in one our application there is a function which works only if I use CHAR(10). and it is done back end. and I cannot ask to use both CHAR(13) & CHAR(10).
  6. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Try this code.
    Code (vb):
    Sub Demo()
    Dim cel As Range
    Dim y, x As String

    For Each cel In Range("C7:C" & Cells(Rows.Count, 3).End(xlUp).Row).Cells
        ReDim y(1 To cel.Value)
        For i = 1 To cel.Value
            y(i) = cel.Offset(i - 1, -1)
        Next
        x = Join(y, Chr(10))
        cel.Offset(, 1).Value = x
    Next
    End Sub
    Thomas Kuriakose and Krishna2385 like this.
  7. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    Awesome......... its works perfectly

    Thanks you so much
  8. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    can anyone get a excel formula for the above query.... just to enhance my logical thinking in excel..
  9. Krishna2385

    Krishna2385 New Member

    Messages:
    11



    I am able to get the results when I use this code however there is a Run time error '9' and it says Subscript out of range

    when I searched it said if we don't specify the number of elements we get this error however in the above code ReDim is used and as per my research we shouldn't receive when we use Dim or ReDim.
  10. Krishna2385

    Krishna2385 New Member

    Messages:
    11
    and to add further I have two sets of records in the same sheet for which I need to use the same code.

    so here in my sheet I have the Count from M5 and count data from S5.

    I have updated changed the range from "C7:C" to "M5:M" . as I mentioned earlier the code is working perfectly fine, I have re checked it there is no issue with data but still I receive the error.

    and one more help ... how can I specify range for two different cells in same code. I have count from M5 which has to be updated and on S5. will update a sample file to clear this confusion.
  11. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    I can't replicate your issue on my end. Please upload sample file where the error is produced.

Share This Page