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!

 

[Dani_LobP]

nice, thanks for the info i guess i can apply that with sheets trick more often and save time.

 

[Dani_LobP]

So i checked this,
both formulas, work, when columns in order it is crazy fast, i guess ill have to work that one out since is what i like most.
but for this to work i will need to add some ascending filter that is applied to both columns. the way i had to do it now for test is manually and is what i try to avoid.

and also,
when using this formula:

[COLOR=#333333] .Formula = "=--(LOOKUP(B2,A$2:A$" & lastrow & ")=B2)"[/COLOR]

this next line won't work, will show VALUE? error for some reason.

.[COLOR=#333333]Range("C1").Value = Evaluate("SUM(C2:C" & lastrow & ")")[/COLOR]

And when autofilter, is Criteria1:=1 meaning to remove blanks?

In the end what i need is those rows that are not matching, thats why i tagged matches as 0 and no matches as 1, so the sum would tell me if there is any, and with filter just display rows that didnt matched.

 

[jasonb75]

The first problem should be an easy fix, changing the formula line to

.Formula = "=--(IFERROR(LOOKUP(B2,A$2:A$" & lastrow & ")=B2,0))"

should resolve the #VALUE! error in the sum line.

Also, the filter criteria should be 0, not 1. There are no blanks, just 1 if the 2 columns match or 0 if they don't.

The sort doesn't need to remain in place, the code writes the actual 1 / 0 values in the cells in place of the formulas, so if you need to go back to the original order after running the code it will not mess things up.

 

[Dani_LobP]

Yes, that solves the issue, thanks!

i just noticed that when checking both concatenated cells, it will give error (so placing a 0) on some even if they are match.

Is it because is too long the information inside the cell?

 

[jasonb75]

Is it because is too long the information inside the cell?

Quite possibly. The other thing to consider is the decimal precision of any numbers, in such cases you would be comparing the actual value, not the rounded value that is shown in the cell. Times and / or dates can also be problematic.