• 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...

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

CHAR Function

Krishna2385

New Member
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
 

Attachments

Chihiro

Excel Ninja
What version of Excel do you have?

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

Chihiro

Excel Ninja
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.
 

Krishna2385

New Member
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).
 

Chihiro

Excel Ninja
Try this code.
Code:
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
 

Krishna2385

New Member
Try this code.
Code:
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



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.
 

Krishna2385

New Member
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.
 
Top