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
