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?
 
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
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.