Populating various columns from different workbooks into centralized table.

JayGatsby

New Member
Joined
Apr 27, 2020
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hello! I have few csv files (all data in separate columns),e.g file1,file2,file3, file4 and I’d like to combine those in one table in excel placed within separate sheet with static headings. The files have a different structure (i.e. the columns containing relevant info differ/are in different order) and need to be pasted without headings into a table of consistent format (e.g. where column “name” would be fitted in e.g column B2). The location of column “name” would differ per file but that can be adjusted in the code itself.

If possible, I would like to do this without having to open the CSVs, so basically the table within the main sheet would get populated with the data from each (closed) csv based on given path/location. Also, once file 1 is done populating, the file 2 should be populated below without headers (different source columns per file into same destination column below one another). The size of files will vary so it would be good to have the range set as dynamic but the column A of each file defines the number of rows that should be copy/pasted.



Example of the table:



File1 (csv):

SportNationalityNameSurnameCityClassCountryPosition
FootballTurkeyKateBlackNewcastle54Turkeymidfielder
RugbyAustraliaBillWhiteRome11Australiastriker


File2 (csv):

First nameSecond nameActivityOriginGroupLocation
JoeNewtonHockeyGeorgia22Georgia
JackSmithTennisJapan34Japan


Desired result in main sheet:

ActionNationalityRatingNameSurname
FootballTurkey54KateBlack
RugbyAustralia11BillWhite
HockeyGeorgia22JoeNewton
TennisJapan34JackSmith
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,114,139
Messages
5,546,179
Members
410,731
Latest member
keobongmacao
Top