Combine multiple columns into one

A

Alex501

Board Regular
Joined
Dec 11, 2015
Messages
54
Office Version
  1. 365
  2. 2019
Hi

I have a set of data in columns and want to combine them all into one column, the data is in a table (named TestTable) laid out like the following;

Example data.PNG


Using the following formula I've got part way there;

=INDEX(TestTable,1+INT((ROW(A1)-1)/COLUMNS(TestTable)),MOD(ROW(A1)-1+COLUMNS(TestTable),COLUMNS(TestTable))+1)

The result looks like this;

Example output.PNG


I need the ouput to be in column order as follows;

Example output correct.PNG


How I amend the formula for the desired output.

Thanks in advance
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Do you have Office 365? If so, a very easy way to do this is just:
Blank3.xlsb
ABCDEF
1Column1Column2Column3Column4Ad Group
2Ad GroupAd GroupAd GroupAd GroupPreston
3PrestonBlackpoolBoltonCarlislePreston
4PrestonBlackpoolBoltonCarlislePreston
5PrestonBlackpoolBoltonCarlislePreston
6PrestonBlackpoolBoltonCarlislePreston
7PrestonBlackpoolBoltonCarlislePreston
8PrestonBlackpoolBoltonCarlislePreston
9PrestonBlackpoolBoltonCarlislePreston
10PrestonBlackpoolBoltonCarlislePreston
11PrestonBlackpoolBoltonCarlislePreston
12PrestonBlackpoolBoltonCarlisleAd Group
13Blackpool
14Blackpool
15Blackpool
16Blackpool
17Blackpool
18Blackpool
19Blackpool
20Blackpool
21Blackpool
22Blackpool
23Ad Group
24Bolton
25Bolton
26Bolton
27Bolton
28Bolton
29Bolton
30Bolton
31Bolton
32Bolton
33Bolton
34Ad Group
35Carlisle
36Carlisle
37Carlisle
38Carlisle
39Carlisle
40Carlisle
41Carlisle
42Carlisle
43Carlisle
44Carlisle
Sheet12
Cell Formulas
RangeFormula
F1:F44F1=VSTACK(TestTable[Column1],TestTable[Column2],TestTable[Column3],TestTable[Column4])
Dynamic array formulas.
 
Upvote 0
Hi Fluff, thanks. Account details updated. I'm using 365 but also have access to 2019.
 
Upvote 0
Thanks for that. (y)
How about
Fluff.xlsm
ABCDEF
1Column1Column2Column3Column4
2Ad GroupAd GroupAd GroupAd GroupAd Group
3PrestonBlackpoolBoltonCarlislePreston
4PrestonBlackpoolBoltonCarlislePreston
5PrestonBlackpoolBoltonCarlislePreston
6PrestonBlackpoolBoltonCarlislePreston
7PrestonBlackpoolBoltonCarlislePreston
8PrestonBlackpoolBoltonCarlislePreston
9PrestonBlackpoolBoltonCarlislePreston
10PrestonBlackpoolBoltonCarlislePreston
11PrestonBlackpoolBoltonCarlislePreston
12PrestonBlackpoolBoltonCarlislePreston
13Ad Group
14Blackpool
15Blackpool
16Blackpool
17Blackpool
18Blackpool
19Blackpool
20Blackpool
21Blackpool
22Blackpool
23Blackpool
24Ad Group
25Bolton
26Bolton
27Bolton
28Bolton
29Bolton
30Bolton
31Bolton
32Bolton
33Bolton
34Bolton
35Ad Group
36Carlisle
37Carlisle
38Carlisle
39Carlisle
40Carlisle
41Carlisle
42Carlisle
43Carlisle
44Carlisle
45Carlisle
Report
Cell Formulas
RangeFormula
F2:F45F2=TOCOL(Table1,,1)
Dynamic array formulas.
 
Upvote 0
If you don't have the TOCOL yet (not everyone has) then another option is
Excel Formula: 
=LET(d,Table1,r,ROWS(d),s,SEQUENCE(r*COLUMNS(d),,0),INDEX(d,MOD(s,r)+1,INT(s/r)+1))
 
Upvote 0
Solution
cmowla said:
Do you have Office 365? If so, a very easy way to do this is just:
Blank3.xlsb
ABCDEF
1Column1Column2Column3Column4Ad Group
2Ad GroupAd GroupAd GroupAd GroupPreston
3PrestonBlackpoolBoltonCarlislePreston
4PrestonBlackpoolBoltonCarlislePreston
5PrestonBlackpoolBoltonCarlislePreston
6PrestonBlackpoolBoltonCarlislePreston
7PrestonBlackpoolBoltonCarlislePreston
8PrestonBlackpoolBoltonCarlislePreston
9PrestonBlackpoolBoltonCarlislePreston
10PrestonBlackpoolBoltonCarlislePreston
11PrestonBlackpoolBoltonCarlislePreston
12PrestonBlackpoolBoltonCarlisleAd Group
13Blackpool
14Blackpool
15Blackpool
16Blackpool
17Blackpool
18Blackpool
19Blackpool
20Blackpool
21Blackpool
22Blackpool
23Ad Group
24Bolton
25Bolton
26Bolton
27Bolton
28Bolton
29Bolton
30Bolton
31Bolton
32Bolton
33Bolton
34Ad Group
35Carlisle
36Carlisle
37Carlisle
38Carlisle
39Carlisle
40Carlisle
41Carlisle
42Carlisle
43Carlisle
44Carlisle
Sheet12
Cell Formulas
RangeFormula
F1:F44F1=VSTACK(TestTable[Column1],TestTable[Column2],TestTable[Column3],TestTable[Column4])
Dynamic array formulas.
Click to expand...
Hi cmowla, thanks for the reply. Seems however we haven't yet been updated to have that function, it's been rolling out since last month. I get a #NAME# error
 
Upvote 0
cmowla said:
Do you have Office 365? If so, a very easy way to do this is just:
Blank3.xlsb
ABCDEF
1Column1Column2Column3Column4Ad Group
2Ad GroupAd GroupAd GroupAd GroupPreston
3PrestonBlackpoolBoltonCarlislePreston
4PrestonBlackpoolBoltonCarlislePreston
5PrestonBlackpoolBoltonCarlislePreston
6PrestonBlackpoolBoltonCarlislePreston
7PrestonBlackpoolBoltonCarlislePreston
8PrestonBlackpoolBoltonCarlislePreston
9PrestonBlackpoolBoltonCarlislePreston
10PrestonBlackpoolBoltonCarlislePreston
11PrestonBlackpoolBoltonCarlislePreston
12PrestonBlackpoolBoltonCarlisleAd Group
13Blackpool
14Blackpool
15Blackpool
16Blackpool
17Blackpool
18Blackpool
19Blackpool
20Blackpool
21Blackpool
22Blackpool
23Ad Group
24Bolton
25Bolton
26Bolton
27Bolton
28Bolton
29Bolton
30Bolton
31Bolton
32Bolton
33Bolton
34Ad Group
35Carlisle
36Carlisle
37Carlisle
38Carlisle
39Carlisle
40Carlisle
41Carlisle
42Carlisle
43Carlisle
44Carlisle
Sheet12
Cell Formulas
RangeFormula
F1:F44F1=VSTACK(TestTable[Column1],TestTable[Column2],TestTable[Column3],TestTable[Column4])
Dynamic array formulas.
Click to expand...
Had it not been Friday afternoon, i would have realised to try this with 365 online - worked a treat.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA Changes Only in Specific Table
Replies
5
Views
398
cmowla
C
M
  • Question
Stack/Combine multiple columns into one column
Replies
4
Views
111
myall_blues
myall_blues
R
  • Solved
Replace cell reference
Replies
1
Views
85
dreid1011
dreid1011
V
  • Question
text Join function
Replies
6
Views
81
Peter_SSs
Peter_SSs
C
  • Question
Combinations Permutations_Variable Analysis
Replies
1
Views
282
cotech_10
C

Forum statistics

Threads
1,215,025
Messages
6,122,731
Members
449,093
Latest member
Mnur

Share this page

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