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:
<tbody>
</tbody>
As you can see, its a bit messy but so far i managed to get this, althou is not really working as expected:
Ideally i'd like this one below:
And for the other sheet, i guess this is what should look like, but as before, is not working the range.
i also tried this, but doesn't really work as expected either:
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!
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:
Auto | Manual |
Column | Column |
Q | AB |
S | T |
T | U |
U | AN |
V | V |
W | AQ |
X | AT |
Y | W |
Z | Z |
AA | AA |
AB | AH |
AC | AD |
AD | AI |
AE | AU |
AF | AV |
AG | AW |
AI | BG |
AJ | AG |
AK | AE |
AL | AF |
AR | BN |
AS | BO |
AT | BP |
AU | BQ |
<tbody>
</tbody>
As you can see, its a bit messy but so far i managed to get this, althou is not really working as expected:
Code:
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:
Code:
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.
Code:
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:
Code:
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!