Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need a formula that returns a group of different lists in one column with each list showing under each other in one column
Solved by K. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/03/2018 - 12:25
Hello :)
Excelchat Expert 19/03/2018 - 12:25
Can you please describe what you're looking for in a little more detail?
Excelchat Expert 19/03/2018 - 12:25
Showing an example would be ideal.
User 19/03/2018 - 12:25
ok hang on
User 19/03/2018 - 12:28
ok so hi, and i hope this one is easy for you
Excelchat Expert 19/03/2018 - 12:28
Me too :)
User 19/03/2018 - 12:29
want all the data in sheets 2 and onwards to list in Sheet1!A:A
User 19/03/2018 - 12:29
first of all i should ask can this be done
Excelchat Expert 19/03/2018 - 12:30
It can certainly be done. There are a few constraints that we have though.
User 19/03/2018 - 12:30
now i should also probably say one more thing
Excelchat Expert 19/03/2018 - 12:30
So, this ideally, could be accomplished via VBA.
Excelchat Expert 19/03/2018 - 12:30
However, Got It PRO no longer supports VBA solutions.
Excelchat Expert 19/03/2018 - 12:30
There is a workaround though.
User 19/03/2018 - 12:31
the data in the other sheets is listed using a formula so for example in sheet 2 A:A there is a formula that returns the data that is there
User 19/03/2018 - 12:31
that data list may change size
Excelchat Expert 19/03/2018 - 12:31
It will require creating A column in Sheet1 for each Sheet you have in your file.
User 19/03/2018 - 12:31
will this be a problem
User 19/03/2018 - 12:32
no see i want all the different lists in the other sheets to show in column A:A after one another
Excelchat Expert 19/03/2018 - 12:32
It will, there will just be additional columns to bring the data in.
Excelchat Expert 19/03/2018 - 12:33
It is the only way to work around VBA.
User 19/03/2018 - 12:33
i don't want the lists from the other sheets to list next to each other i want them to list after each other in column A:A
Excelchat Expert 19/03/2018 - 12:33
It's called using helper column.
Excelchat Expert 19/03/2018 - 12:33
You can hide the helper columns so you do not see them.
User 19/03/2018 - 12:33
ok so why do we need helper column will there be a formula in this helper column?
Excelchat Expert 19/03/2018 - 12:33
I'll show you what I mean, and you can tell me if the solution will work for you.
Excelchat Expert 19/03/2018 - 12:34
Yes.
Excelchat Expert 19/03/2018 - 12:34
The, I will add a formula in A1 to bring in all the data in order.
Excelchat Expert 19/03/2018 - 12:34
using an if statement.
User 19/03/2018 - 12:34
can we not use the columns in the other sheets as our helper columns?
User 19/03/2018 - 12:34
can we not add the helper formula to the formula that exists in the other sheets column or is it too difficult
Excelchat Expert 19/03/2018 - 12:34
No, I don't believe so.
Excelchat Expert 19/03/2018 - 12:35
Let me review a possible solution.
User 19/03/2018 - 12:35
ok
User 19/03/2018 - 12:35
show me
Excelchat Expert 19/03/2018 - 12:35
Will the number of sheets(tabs) change?
User 19/03/2018 - 12:36
hey can we put the helper columns in a seperate sheet instead of hiding them in the sheet 1?
Excelchat Expert 19/03/2018 - 12:36
In other words, will you add sheets.
Excelchat Expert 19/03/2018 - 12:36
Yes.
Excelchat Expert 19/03/2018 - 12:36
Also, will more data be added into each sheet/
Excelchat Expert 19/03/2018 - 12:36
?
User 19/03/2018 - 12:37
no the tabs will remain the same number but the data in the sheet will change and the quantity of data down the column will get more or less
User 19/03/2018 - 12:37
no extra tabs
Excelchat Expert 19/03/2018 - 12:37
Okay, got it. Thank you.
User 19/03/2018 - 12:37
the data in those sheets is returned from elsewhere by a formula in the cells
User 19/03/2018 - 12:39
ok great so its all possible
Excelchat Expert 19/03/2018 - 12:39
I'm reviewing now how this will work.
User 19/03/2018 - 12:39
great thanks
Excelchat Expert 19/03/2018 - 12:42
What is the largest number of rows any sheet will have?
User 19/03/2018 - 12:43
ok so 2000
User 19/03/2018 - 12:43
but there willl not be data in there its just a precaution as the list is populated by a formula from another file
Excelchat Expert 19/03/2018 - 12:44
Okay, no worries, that will not cause any issues.
Excelchat Expert 19/03/2018 - 12:44
As long as the formula doesn't return a value.
User 19/03/2018 - 12:45
just lists of names but can the name have some letters and some digits?
Excelchat Expert 19/03/2018 - 12:45
Yup, that'll be fine. This formula will bring in anything from that list.
Excelchat Expert 19/03/2018 - 12:48
I wish we still had VBA, this would take half the time.
Excelchat Expert 19/03/2018 - 12:55
Okay, so just so you know how this works, I'm bringing everything to the Helper sheet.
Excelchat Expert 19/03/2018 - 12:55
Then, stagering the data, so that I can bring it all together using offset.
User 19/03/2018 - 12:55
ok
Excelchat Expert 19/03/2018 - 12:56
If you look at J:M, that is where the data will be stagered using a complex formula to find multiple lookups.
User 19/03/2018 - 12:56
sheet 1?
Excelchat Expert 19/03/2018 - 12:56
Helper.
Excelchat Expert 19/03/2018 - 12:59
Okay, so the helper piece is complete.
Excelchat Expert 19/03/2018 - 12:59
=IFERROR(INDEX(B$2:B$1000,SMALL(IF($A$2:$A$1000=J$1,ROW($A$2:$A$1000)),ROW(1:1))),"")
Excelchat Expert 19/03/2018 - 12:59
That is the formula to pull in the data from each tab.
Excelchat Expert 19/03/2018 - 12:59
It has to adjust a little bit based on the column which it is pulling.
Excelchat Expert 19/03/2018 - 01:00
A:H is just bringing in the raw data in separate columns.
Excelchat Expert 19/03/2018 - 01:01
Then, J:M is organizing the data by staggering it so that it can be transfered successfully to Sheet1.
User 19/03/2018 - 01:01
ok
User 19/03/2018 - 01:06
looks good it looks like your done
Excelchat Expert 19/03/2018 - 01:07
I'm making sure it is pulling in correctly first.
User 19/03/2018 - 01:07
ok
User 19/03/2018 - 01:07
can you then upload to me as a excel spreadsheet?
User 19/03/2018 - 01:07
so i don't have to copy and paste from the example sheet
Excelchat Expert 19/03/2018 - 01:08
You, can do that by going to file-Download as which will allow you to save this file to your computer.
User 19/03/2018 - 01:08
ok so click on file then click on download as?
Excelchat Expert 19/03/2018 - 01:08
Yup.
User 19/03/2018 - 01:08
wow cool
User 19/03/2018 - 01:09
the formula in sheet 1 A:A is long
Excelchat Expert 19/03/2018 - 01:09
Yes :)
User 19/03/2018 - 01:09
how did you figure it out so quick
Excelchat Expert 19/03/2018 - 01:09
I have practiced Excel for many years and have some formulas memorized.
User 19/03/2018 - 01:09
yikes
User 19/03/2018 - 01:12
so r u still checking
Excelchat Expert 19/03/2018 - 01:13
Yes, I need to figure out a way to stagger the helper column because all the data did not pull in.
User 19/03/2018 - 01:13
ok
Excelchat Expert 19/03/2018 - 01:22
It should be good now.
Excelchat Expert 19/03/2018 - 01:23
https://docs.google.com/spreadsheets/d/1U5rDzJqeyGS5qgv1tX1CmWS2mSlgkl34YBrP4rCVhtM/edit#gid=0
Excelchat Expert 19/03/2018 - 01:23
Sorry, go to that link and check.
User 19/03/2018 - 01:23
so it works?
Excelchat Expert 19/03/2018 - 01:23
Yup :)
User 19/03/2018 - 01:23
in here it looks like there are errors is it ok?
User 19/03/2018 - 01:24
can i download from there?
Excelchat Expert 19/03/2018 - 01:24
Yes, I would use the link.
Excelchat Expert 19/03/2018 - 01:24
Yes, using the same process.
User 19/03/2018 - 01:24
ok great thanks i will check
User 19/03/2018 - 01:24
and you so much
User 19/03/2018 - 01:24
thank you so much
Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.