joining two or more columns into one

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
510
Office Version
  1. 365
Platform
  1. Windows
hi!

for example

test
ABCD
1wanted result
21101
32112
43123
54134
65145
76156
87167
98178
109189
1110
1211
1312
1413
1514
1615
1716
1817
1918
test


and just for future goggling, how should i needed to search for it? cause when i google it all i found was merging the values/text in two cells into one, like a1=bob and b1=evans so c1=bob evans
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Couple of options for you.

Book1
ABCDEFG
1M365BetaOldSkool
211111
321222
431333
541444
651555
761666
871777
981888
1091999
1110201010
121111
131212
141313
151414
161515
171616
181717
191818
201919
212020
22
Sheet2
Cell Formulas
RangeFormula
E2:E21E2=TOCOL(B2:C11,,1)
F2:F21F2=INDEX($B$2:$C$11,MOD(ROW(A1)-1,ROWS($B$2:$C$11))+1,INT((ROW(A1)-1)/ROWS($B$2:$C$11))+1)
Dynamic array formulas.
 
Upvote 0
thanks dave,
it seems i need a lot for filtering blanks,
some columns are longer then others, so i have too many blanks,
tried this
Excel Formula:
=TOCOL(FILTER(AB1:AP55000,((AB1:AP55000=0)*((AB1:AP55000<>"")))))
but didn't work,
how ignore blanks?
 
Upvote 0
tried this
Excel Formula:
=TOCOL(FILTER(AB1:AP55000,((AB1:AP55000=0)*((AB1:AP55000<>"")))))
but didn't work,
how ignore blanks?
That is close to one way but filter at the end, not the beginning. :)

22 09 25.xlsm
BCDEF
1
21111
322
433
50660
65200155
7606
8066
918200
101911
112015
120
130
1418
1519
1620
17
TOCOL
Cell Formulas
RangeFormula
F2:F16F2=LET(TC,TOCOL(B2:D11,,1),FILTER(TC,TC<>""))
Dynamic array formulas.
 
Upvote 0
thank you peter
and you too dave!

if this is allowed... (if not tell me and i'll delete this bit)
with your permission, i wonder if you can take a look in my other post here
my last msg in the thread, can you help with the blanks there too?
 
Upvote 0
If it's just blanks you want to ignore then there is an optional parameter for that

Book1
HIJKLMN
1
21111
32122
4134
54145
656
76167
87178
989
10910
111011
1212
1313
1414
1516
1617
17
Sheet2
Cell Formulas
RangeFormula
M2:M16M2=TOCOL(I2:J11,1,1)
Dynamic array formulas.
 
Upvote 0
Solution
dave, you helped me in the above mention post too, without realizing, thanks a bundle!!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top