Join data in 3 Columns to 1 column

  • Hi All


    Any ideas how to join data from 3 columns into 1 list

    Data is in columns AM, AN, AO and AP and All data starts on Row 9 of each column


    Data should be shown in column AV starting at row 9


    Sometimes though, not all columns are populated.




    Thanks

  • You say data from 3 columns but you state 4 columns that have data. Which columns need to be used to join the data?


    How do you want the data to be displayed, as a continuous string, or data from each column to separated by something (such as , or - or _)?


    What to do if there is no data in a column.?


    Can you either give some examples of the data or, better still, attach your workbook.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try this in AQ9 copied down


    =IF(LEN(AM9&AN9&AO9&AP9)>0,AM9&AN9&AO9&AP9,"No Data To Display")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Ahhhh, I see what you mean by "joined list" now!


    This cannot be done with a formula, a macro is needed.


    Is there any data in columns AL and ARfrom row 8 down also any data in AM8 to AP8?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • try the attached, no need to answer questions in my previous post


    Code assigned to the button

  • you can do using paramArray so dinamis multi range and multi typename range,variant,etc
    i has using KjBox file in ar9 type formula
    =Myjoin(ROW(A1);$AM$9:$AM$21;$AN$9:$AN$10;$AO$9:$AO$15;$AP$9:$AP$13) copy down

    Code
    [/B][/I][/URL][/B] Function Myjoin(rw As Long, ParamArray a() As Variant) As StringDim v, v1, t$For Each v In a   For Each v1 In v    If Len(v1) Then If t = "" Then t = v1 Else t = t & "," & v1   Next v1Next vIf rw > UBound(Split(t, ",")) + 1 Then Myjoin = "" Else Myjoin = Split(t, ",")(rw - 1)End Function[B][URL="https://www.ozgrid.com/forum/member/33159-kjbox"][I][B]

    [/I][/URL][/B]

  • Quote


    try the attached, no need to answer questions in my previous post


    Code assigned to the button


    Perfect solution although I attached to the on change event rather than a button
    I especially like the fact that any changes to a column are inserted in the correct place in the "Joined List"


    Just for interest: Should I want to add more columns, can you please show how to modify the VBA provided


    i.e. adding columns AQ, AR, AS and AU


    Thank you

  • Quote


    you can do using paramArray so dinamis multi range and multi typename range,variant,etc
    i has using KjBox file in ar9 type formula
    =Myjoin(ROW(A1);$AM$9:$AM$21;$AN$9:$AN$10;$AO$9:$AO$15;$AP$9:$AP$13) copy down


    Not to ignore your efforts,
    I did try this but it throws an error with the Row(A1)


    Thank you

  • This would be the code for data in AM to AU and list starting in AV9

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi


    Thanks for the update.
    There was a typo in your original post which caused the error No "+" before "Myjoin", but no worries, and I see how this works, but it is not dynamic in selecting new entries.


    Adding more lines to the original lists do not get picked up into the joined


    Thanks anyway and a good solution.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!