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.
Hi
Sorry for the typo, there are 4 columns as per line 2 of my OP
Sample sheet attached
If there is no data in 1 or more columns, skip to the next column and continue the joined list
If there is no data in any columns, return "No data to Display"
Thanks
Try this in AQ9 copied down
=IF(LEN(AM9&AN9&AO9&AP9)>0,AM9&AN9&AO9&AP9,"No Data To Display")
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?
try the attached, no need to answer questions in my previous post
Code assigned to the button
Sub MakeList()
Dim x, y, i As Integer, ii As Long, iii As Long, lRow As Long
With ActiveSheet
lRow = .Cells(.Rows.Count, "am").End(3).Row
If .Cells(.Rows.Count, "an").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "an").End(3).Row
If .Cells(.Rows.Count, "ao").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "ao").End(3).Row
If .Cells(.Rows.Count, "ap").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "ap").End(3).Row
x = Range("am9", Cells(lRow, "ap"))
ReDim y(1 To (lRow - 8) * 4, 1 To 1)
For i = 1 To 4
For ii = 1 To UBound(x, 1)
If x(ii, i) <> "" Then iii = iii + 1: y(iii, 1) = x(ii, i)
Next
Next
With .[aq9]
.Resize(10000).Clear
.Resize(iii) = y
End With
End With
End Subyou 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]
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
Sub MakeList()
Dim x, y, i As Integer, ii As Long, iii As Long, lRow As Long
With ActiveSheet
lRow = .Cells(.Rows.Count, "am").End(3).Row
If .Cells(.Rows.Count, "an").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "an").End(3).Row
If .Cells(.Rows.Count, "ao").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "ao").End(3).Row
If .Cells(.Rows.Count, "ap").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "ap").End(3).Row
If .Cells(.Rows.Count, "aq").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "aq").End(3).Row
If .Cells(.Rows.Count, "ar").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "ar").End(3).Row
If .Cells(.Rows.Count, "as").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "as").End(3).Row
If .Cells(.Rows.Count, "au").End(3).Row > lRow Then lRow = .Cells(.Rows.Count, "au").End(3).Row
x = Range("am9", Cells(lRow, "au"))
ReDim y(1 To (lRow - 8) * 4, 1 To 1)
For i = 1 To 4
For ii = 1 To UBound(x, 1)
If x(ii, i) <> "" Then iii = iii + 1: y(iii, 1) = x(ii, i)
Next
Next
With .[av9]
.Resize(10000).Clear
.Resize(iii) = y
End With
End With
End SubNot to ignore your efforts,
I did try this but it throws an error with the Row(A1)
Thank you
I am has test and work for me
Quote
This would be the code for data in AM to AU and list starting in AV9
Nice one, much appreciated. Another learning curve accomplished.
Quote
I am has test and work for me
Can you please update the sample sheet which I provided and upload so I can see your solution?
Thanks
Can you please update the sample sheet which I provided and upload so I can see your solution?
Thanks
see in columns(av)
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.
Don’t have an account yet? Register yourself now and be a part of our community!