Excel sheet to sheet column copy.

I have to create a report from three different csv files. lets say these files are: input1, input2 and input 3.

These three files have a set amount of columns but an unknown amount of rows. (sometimes 50, sometimes 150k+)
 
each file gets copied into its own tab (input1, input2 and input 3) respectfully.

I need to create a report that pulls information from each input file...  

I have tried to do a special copy of each column into the report but the file size gets too large because it copies all the way to the bottom of excel.

I have tried (='input1'!A1) or vlookup commands but it too has to be dragged to the end of the row of data.

Is there another way to auto populate a column?

I hope I have explained this correctly.
LVL 1
leebaskinownerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leebaskinownerAuthor Commented:
logically....

if there is a way to count the rows and only copy that number of row data over to the column in the reports sheet. without going all the way to the bottom and causing the excel to lockup.

hope this helps, Thanks.
0
Glenn RayExcel VBA DeveloperCommented:
You can count the number of items in a column like so:
=COUNTA(Input1!A:A)
If there's a header row, you'd subtract one from this to get the number of used rows.

However, I'd still like to know how you're wanting to consolidate this data.  If the data layout is the same for all three input files, couldn't you just insert them into the same sheet, one following the other?

An example file would certainly help.  

-Glenn
0
leebaskinownerAuthor Commented:
Yes, I know how to count a row.

I need to: count column > take that number and only copy that much data into a column in another sheet.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

leebaskinownerAuthor Commented:
Sorry, I didn't mean to sound snappy above.

The data is not the same in all three documents. the first document is used to create the other two, then all three are used to create a report based on the three.

it sounds more complicated then it is. I am trying to have the report auto populate with information from the input files.
0
Laila JacksonCommented:
From the dynamacy of your inputs, I think what you are wanting to do is way too complicated for a macro.

You could achieve what you need plus allow for further expansion with some visual basic code but it would require sample data and an exact definition of the steps from x to y to achieve the desired result.

If you wish to go down this path then by all means upload a sample file. If privacy is an issue then pm me and we can go from there.
0
Glenn RayExcel VBA DeveloperCommented:
leebaskin:

I'm going to restate what I think you want; please confirm and then I can likely help.

You have three sheets of data (input1, input2, input3) with identical construction column-wise, but different amounts of rows.

You want to consolidate the three sets of data into one sheet.

Aside from cutting and pasting the data manually, are you looking for an automated solution (i.e., VBA)?  

I can think of two methods:
1) Consolidate the three sheets to a new sheet in the same workbook.
2) Open each csv file and append to a new, blank worksheet.

Let me know if this is correct.  Either of these options is easy to achieve.  Here is an example of a solution I posted here last year:

Merge 3 worksheets into 1

Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.