Concatenate different columns with delimiter

[Dani_LobP]

Hi there,

I am trying to concatenate different columns from 2 sheets, which are in different order one to each other.

This would be the columns i need from the Auto sheet, and their counter from Manual sheet:

[TABLE="width: 106"]
<tbody>[TR]
[TD]Auto[/TD]
[TD]Manual[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]Column[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD]AN[/TD]
[/TR]
[TR]
[TD]V[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]W[/TD]
[TD]AQ[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]AT[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]AH[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]AI[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]AU[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD]AV[/TD]
[/TR]
[TR]
[TD]AG[/TD]
[TD]AW[/TD]
[/TR]
[TR]
[TD]AI[/TD]
[TD]BG[/TD]
[/TR]
[TR]
[TD]AJ[/TD]
[TD]AG[/TD]
[/TR]
[TR]
[TD]AK[/TD]
[TD]AE[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]AF[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]BN[/TD]
[/TR]
[TR]
[TD]AS[/TD]
[TD]BO[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]BP[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]BQ[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, its a bit messy but so far i managed to get this, althou is not really working as expected:

ActiveCell = Join(Application.Index(Range("Q2:AU2").Value, 1, 0), "|")  <- this one works, but i don't need some columns between that range

Ideally i'd like this one below:

ActiveCell = Join(Application.Index(Range("Q2,S2:AG2,AI2:AL2,AR2:AU2").Value, 1, 0), "|") <- but for some reason is not working, i guess i do wrong the range..

And for the other sheet, i guess this is what should look like, but as before, is not working the range.

ActiveCell = Join(Application.Index(Range("AB2, T2, U2, AN2, V2, AQ2, AT2, W2, Z2, AA2, AH2, AD2, AI2, AU2, AV2, AW2, BG2, AG2, AE2, AF2, BN2, BO2, BP2, BQ2").Value, 1, 0), "|")

i also tried this, but doesn't really work as expected either:

ActiveCell = _   
 "=CONCATENATE(Q2,""|"", S2,""|"", T2,""|"", U2,""|"", V2,""|"", WQ2,""|"", X2,""|"", Y2,""|"", Z2,""|"", AA2,""|"", AB2,""|"", AC2,""|"", AD2,""|"", AE2,""|"", AF2,""|"", AG2,""|"", AI2,""|"", AJ2,""|"", AK2,""|"", AL2,""|"", AR2,""|"", AS2,""|"", AT2,""|"", AU2)"

Eventually the idea is have both concatenated rows pasted on a new sheet so i can compare both sheets with vlook up to be sure both sheets have same data.

This is connected to this query i did some days ago: https://www.mrexcel.com/forum/excel-questions/1109017-vlookup-loop.html?highlight=vlookup+loop

Hope someone can help, thanks in advance!

 

[jasonb75]

Maybe something like

Dim str As String, c As Range
For Each c In Range("Q2,S2:AG2,AI2:AL2,AR2:AU2")
    str = str & c.Value & "|"
Next
str = Left(str,Len(str)-1)
ActiveCell = str

edit:-

ActiveCell = Join([COLOR="#FF0000"]Application.Index([/COLOR]Range("Q2,S2:AG2,AI2:AL2,AR2:AU2").Value[COLOR="#FF0000"], 1, 0)[/COLOR], "|")

I think that Application.Index is the cause of failure in your attempt, if you remove that it should work, if not, try the loop above.

 

[Dani_LobP]

Thanks for the help and fast reply Jason!

i tried remove index as you suggested but wouldn't work, still gives an error for some reason.

But the other piece of code worked wonders! thanks.

Any chance you can help in how to make now that be dragged down rows till the last one applying same rule?
As for the way i have it now, it just copy/pastes same result since there is no formula. With formula was easier to drag down since it would be changing the cell to the below and so on...

Sheets("Auto").Select
Range("A1").End(xlToRight).Select
ActiveCell.Offset(1, 1).Select
For Each c In Range("Q2,S2:AG2,AI2:AL2,AR2:AU2")
    str = str & c.Value & "|"
Next
str = Left(str, Len(str) - 1)

Selection.AutoFill Destination:=Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))
ActiveSheet.Calculate
Sheets("Auto").Range("AV2:AV" & LastRowAuto).Copy  <- this AV2:AV is actually last column, where the concatenate is happening, couldn't think on another way in how to select this cell...
Sheets("Check").Range("A2").PasteSpecial xlPasteValues

Second part of the code was working with a previous formula, by checking lastrow based on column to the left, dragging down and then calculating sheet.

 

[jasonb75]

See if this works, this should replace everything in your post above.

Dim rw As Long, c As Range
With Sheets("Auto").
    Do Until .Range("AV2").Offest(rw, -1) = ""
        For Each c In .Range("Q2,S2:AG2,AI2:AL2,AR2:AU2").Offset(rw)
            str = str & c.Value & "|"
        Next
        .Range("AV2").Offest(rw) = str
        str = ""
    Loop
    .Range("AV2").Resize(rw-1).Copy Sheets("Check").Range("A2")
End With

I'm going to be offline for the rest of the day, hopefully somebody else will help you with any follow ups from here. I'll check when I get back on, but that might not be until thursday morning (uk time).

 

[Dani_LobP]

thanks for the help, tried adding that but i keep getting an error.

Dim str As String
Dim rw As Long, c As Range


With Sheets("Auto").     <- had to remove dot to let macro run for some reason                                                 
    Do Until .Range("AV2").Offset(rw, -1) = ""                         
        For Each c In .Range("Q2,S2:AG2,AI2:AL2,AR2:AU2").Offset(rw)
            str = str & c.Value & "|"
        Next
        .Range("AV2").Offset(rw) = str
        str = ""
    Loop      <- Loop works, but is not adding new lines with the next row concatenated 
    Range("BR2:BR" & LastRowAuto).Copy    
    Sheets("Check").Select
    Range("A2").PasteSpecial xlPasteValues
End With

Had to change the end of the code you suggested cuz it kept giving error: Runtime Error 1004 “Application-defined or Object-defined error”

And when i try do it with the second sheet, the loop will simply go forever but won't be fillin even the first row.

Dim str2 As String
Dim rw2 As Long, c2 As Range


With Sheets("Manual")    
    Do Until .Range("BR2").Offset(rw2, -1) = ""
        For Each c2 In .Range("AB2, T2, U2, AN2, V2, AQ2, AT2, W2, Z2, AA2, AH2, AD2, AI2, AU2, AV2, AW2, BG2, AG2, AE2, AF2, BN2, BO2, BP2, BQ2").Offset(rw2)
            str2 = str2 & c2.Value & "|"
        Next
        .Range("AV2").Offset(rw2) = str2
        str2 = ""
    Loop
    Range("BR2:BR" & LastRowAuto).Copy
    Sheets("Check").Select
    Range("B2").PasteSpecial xlPasteValues
End With

 

[jasonb75]

Sorry, I was in a bit of a rush and posted the code without testing, there was a line missing, I've added it now and removed the dot that shouldn't have been there.

Dim rw As Long, c As Range
With Sheets("Auto")
    Do Until .Range("AV2").Offest(rw, -1) = ""
        For Each c In .Range("Q2,S2:AG2,AI2:AL2,AR2:AU2").Offset(rw)
            str = str & c.Value & "|"
        Next
        .Range("AV2").Offest(rw) = str
        str = ""
    rw = rw + 1
    Loop
    .Range("AV2").Resize(rw-1).Copy Sheets("Check").Range("A2")
End With

I think that should be right now, if not, think baby steps, lets get this one right before trying to make it work with the second sheet.

 

[Dani_LobP]

thanks again for the help! and don't worry, there was/is no rush for this

i was sure there was something missing, and was wondering where the +1 to be added would be missing, but im very limited in VBA knowledge and for sure loops and whiles and kind of things are my very weak point.. usually make simple stuff, way shorter hehe

So i tried adding the new line, and works wonders! awesome, thanks!

Just so i know,

if the code starts with the "with sheet..." does this mean, that anything happening in that section, will be happening in the sheet, and no need to have it selected? i could have any other sheet selected, or even workbook if i specify it? "with workbook.sheets..." ?

Another question, what does the "resize(rw-1)" do? Ain't that line already selecting just the column to be copy/pasted in different sheet?

thanks again for the help, will modify it for second sheet and try test it now with larger amount of data and see how fast it goes.
im worried that 300-400k rows might make it slow.

Once i have both sheets done, ill have 2 columns, and then will compare both with vlookup, there is where my worries are, afraid that it could take forever.

 

[Dani_LobP]

yeah, works great! thanks Jason

but as i expected, huge data just delays macro a lot. Any ideas in how to make it faster?

once i get both sheets concatenated columns, i check them both to find which ones are missing or have errors.

So in column A i have Auto concatenated, and in column B i have Manual concatenated:

Sheets("Check").Select
LastRow = Range("A2").End(xlDown).Row
Range("C2:C" & LastRow) = "=IFERROR(IF(VLOOKUP(A2,$B$2:B" & LastRow & ",1,FALSE)=A2,0,1),1)"
Range("C1") = "=SUM(C2:C" & LastRow & ")"      <---- this is just to know how many rows will have a 1, meaning how many rows have an error or are no match so i need to check/analyze deper
Range("D2:D" & LastRow).FormulaR1C1 = "=If(RC[-1]=1,RC[-3],"""")"
ActiveSheet.Calculate
Worksheets("Check").Range("D1").AutoFilter Field:=4, Criteria1:="<>"

Hope it makes sense to understand whats going on..

I have the feeling that as i wrote it, its applying formulas in whole rows at same time, hence making excel extra busy and slow.
would it be faster/better apply here some loop and make the formulas apply row by row (and paste as value) and that way maybe will be faster?

thanks in advance!

 

[jasonb75]

if the code starts with the "with sheet..." does this mean, that anything happening in that section, will be happening in the sheet, and no need to have it selected? i could have any other sheet selected, or even workbook if i specify it? "with workbook.sheets..." ?

That is correct, selection is the fastest way to slow your code down, using With instead of Select will make it run a lot quicker. You just need to remember the dots in between otherwise it could go wrong. In this simple example, if the dot in front of Range was missing then it would write "Hello" in the selected sheet, not in sheet1

With Sheets("Sheet1")
    .Range("A1").Value = "Hello"
End With

The Resize line is used because there is no selection taking place, the loop only had 1 row active at a time. rw counts the iterations of the loop, and tells us how many rows down from AV2 to copy.
-1 at the end because the final value of rw relates to the empty row that caused the loop to end, so we need to go back 1 row.

Using a range find in a loop might be quicker than vlookup. Alternatively, if you can sort the data in ascending order by the concatenated column then approximate match might be a good choice.

edit:- I didn't refresh before posting this, going back to look at your last reply.

 

[jasonb75]

Give this a go, there are 2 formula lines which are both commented out (apostrophe at the start of the line).

With your data sorted by column A ascending values, remove the apostrophe from the second formula only. (this will run much faster).
With your data in descending or random order, remove the apostrophe from the first formula only.

Option Explicit
Sub check()
Dim lastrow As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With
With Sheets("Check")
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
    With .Range("C2:C" & lastrow)
        '.Formula = "=--ISNUMBER(MATCH(B2,A$2:A$" & lastrow & ",0))"
        '.Formula = "=--(LOOKUP(B2,A$2:A$" & lastrow & ")=B2)"
        Application.Calculate
        .Value = .Value
    End With
    .Range("C1").Value = Evaluate("SUM(C2:C" & lastrow & ")")
    .Range("A1:C" & lastrow).AutoFilter Field:=3, Criteria1:=1
End With
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With
End Sub