stacking multiple columns into one

I need to stack multiple columns into one big columns...

So if the first column was "first names" and the second column was "last names" I would need to stack it as such:

first names
bob
john
mike
mark
last names
johnson
sears
churchill
evans

I would like to do this in a dynamic way with a formula, so any names added to the list would be reflected in the "single stacked" column.

What is the best way to do this... and actually I would like to also exclude the title of each column so the final results would look like this:

bob
john
mike
mark
johnson
sears
churchill
evans

Thank you for the help!

********update w/ additional details************
example excel file attached, with details on how I'll be using it:

I will be adding additional items in columns F through J

When I do that, I would like for columns A and B to be automatically updated.

...so for example if i add "pear" in cell F8, I want for pear to dynamically populate in cell B7, and "fruit" to populate in cell A8, while the rest of the contents in the column would shift down.

Because the length of the list will chage, the formula would need to have a way of eliminating blank spaces before the list is populated in column A and B.

*****************************************

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Please check the attached file in which included the vba code.

This can be done with PowerQuery. Just load the source table into the PQ editor and then choose Unpivot Columns from the Transform ribbon. Sort on the first column, ascending, then close and load to cell A1. Once set up, the query can be refreshed whenever data is added to the source table. Query refresh is on the Data ribbon.

ARRAY formula ( Pl see note below) in A1 then drag Down

Please Login or Register  to view this content.

In B1 then drag down.

Please Login or Register  to view this content.

Try this
Enter array formula in cell F2 and drag formula across to I2 and down

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula:

Please Login or Register  to view this content.

Unknown

	A	B	C	D	E	F	G	H	I	J
1	fruits	grapefruit				fruits	vegetables	grains	dairy	meat
2	fruits	papaya				grapefruit	celery	millet	milk	chicken
3	fruits	pineapple				papaya	broccoli	quinoa	yogurt	beef
4	fruits	avocado				pineapple	lettuce	wheat	butter	pork
5	fruits	lemon				avocado	cauliflower	buckwheat	cheese	fish
6	fruits	apple				lemon	cale	barley
7	vegetables	celery				apple		oat
8	vegetables	broccoli
9	vegetables	lettuce
10	vegetables	cauliflower
11	vegetables	cale
12	grains	millet
13	grains	quinoa
14	grains	wheat
15	grains	buckwheat
16	grains	barley
17	grains	oat
18	dairy	milk
19	dairy	yogurt
20	dairy	butter
21	dairy	cheese
22	meat	chicken
23	meat	beef
24	meat	pork
25	meat	fish

Sheet: Sheet1

Here is a dynamic way
Enter this formula to populate headers. As your columns A & B grow, formula will automatically expend.
Enter in F1 and drag formula across

Formula:

Please Login or Register  to view this content.

the use this formula to populate values from col B.

Formula:

Please Login or Register  to view this content.

Both formulas are regular formulas and don't require use of CTRL+SHIFT+ENTER