Question description:
This user has given permission to use the problem statement for this
blog.
a formula for excel to find the last cell of data in a variable column and to then add data beneath this
Solved by S. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/03/2018 - 11:31
Hello, I understand that you need help with finding the last cell in a column with data.
Excelchat Expert
23/03/2018 - 11:31
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
23/03/2018 - 11:32
yes, so basically i want to extract data fro multiple different spreadshet sand to copy this into a central workbook
User
23/03/2018 - 11:32
howeevr, as the data in the other sheets is variable, I don't know how to be able to pull this all in into one sheet
Excelchat Expert
23/03/2018 - 11:32
Can you give me a sample data to analyze so that I can see if it can be done via formulas alone?
User
23/03/2018 - 11:34
so i would like all the number (1, 2) in sheet 2 and 3 to appear in number column A in sheet 1
User
23/03/2018 - 11:34
and england and wales to be pulled into column B
User
23/03/2018 - 11:34
however, sheet 2 and 3 will be constantly changing
Excelchat Expert
23/03/2018 - 11:34
Can you complete Sheet1 please or press Enter? It is still being edited so I can't see it.
User
23/03/2018 - 11:35
ok?
Excelchat Expert
23/03/2018 - 11:35
Okay, what should the output look like in Sheet1?
User
23/03/2018 - 11:35
column A in sheet 2 and 3 should be pulled into column A sheet 1
User
23/03/2018 - 11:36
column B sheet 2 and 3 should be pulled into coolumn B sheet 1
User
23/03/2018 - 11:36
however, as I said, the data in sheet 2 and 3 is goign to be changing on a regular basis
Excelchat Expert
23/03/2018 - 11:37
The data in Sheet2 and 3 ends when there is a blank row, right? I meant there will never be a blank row in between them so we can be sure that once a blank row is found, it is the end of the record?
User
23/03/2018 - 11:38
exactly
Excelchat Expert
23/03/2018 - 11:40
Okay, I got to be honest with you. This is much easier with a VBA solution but it may be possible using Formulas. I'm just not sure if that's what you are looking for since solving this using formulas can result to a very slow consolidated view dependin on the number of records and columns.
Excelchat Expert
23/03/2018 - 11:40
Will it be different files as well or the same file but different sheets?
User
23/03/2018 - 11:40
different files
User
23/03/2018 - 11:40
hmm i thought VBA code might be the best answer...
Excelchat Expert
23/03/2018 - 11:41
If they are of different files then I'm afraid VBA is our only viable solution.
Excelchat Expert
23/03/2018 - 11:44
The only other way I can think of to solve this without VBA is to use the Consolidate feature of Excel but it won't be automated.
User
23/03/2018 - 11:44
okay
Excelchat Expert
23/03/2018 - 11:45
I'm not even sure if that's exactly what you need but honestly, since the data will be located in different files, your best bet is really to use VBA.
Excelchat Expert
23/03/2018 - 11:45
Solving this with VBA would be easy but I'm afraid that we currently do not support VBA solutions. I think it's due to security reasons so even if I want to, I can't.
User
23/03/2018 - 11:46
ah dammmm. that's a shame
User
23/03/2018 - 11:46
thanks for your help anywy
Excelchat Expert
23/03/2018 - 11:47
There are a couple of resources you can go to as there are a lot of general VBA code to do what you need so try searching for "consolidate data from different workbooks VBA code"
Excelchat Expert
23/03/2018 - 11:47
It is against policy to send a URL link so I'll have to ask you search for that instead. I sincerely hope you understand.
Excelchat Expert
23/03/2018 - 11:48
If you want, you can contact us again in a few days and we may be supporting VBA/Macro solutions then. Any of our VBA experts can easily help you with this.
Excelchat Expert
23/03/2018 - 11:49
I'll be rating you 5 stars so our group of experts know that you are a good client and to take good care of you should you contact us again.
User
23/03/2018 - 11:49
that's fantastc
User
23/03/2018 - 11:49
sounds good
Excelchat Expert
23/03/2018 - 11:49
That's the best I can do for you for now, I really hope you understand. Thank you.
Excelchat Expert
23/03/2018 - 11:50
Our session is about to expire. Thank you for contacting Got It Pro. Have an awesome day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
