How to automatically merge two Excel Worksheets with the Same First Column?

Before anyone states that this problem has already been answered before, I am looking for a automatic (macro, program, etc) system that can achieve the following:

Merge multiple workbooks (separate files, not sheets) by a common column.

The first column is the same for all the files. However, there are 500 individual csv files, each with about 7000 rows

I am aware of the VLOOKUP() function, the Consolidate function, and programs like Kutools and Ablebits. However, none of these seem to suit my purpose of creating a single workbook with all of these files combined by the common column.

Does anyone know of a VBA macro or other program that can assist me with this?

EDIT: Below are links to two of the 500 files:

1.https://docs.google.com/spreadsheets/d/1rRdNS9K6QYjfH2_P9wmR5YS0-Ectl1SnVn2SfFz9UUY/edit?usp=sharing

2.https://docs.google.com/spreadsheets/d/13Dmc6hKHCG4RBF2an3NZ7nux5ffI5F5cigbz5fFyaPI/edit?usp=sharing

koreamaniac101

Posted 2018-06-13T18:27:05.633

Reputation: 11

How many columns does your csv files have? Can you provide a sample of your data? Is a python solution acceptable (a dozen lines of python + pandas should do the trick...)? – agtoever – 2018-06-13T18:43:40.250

Python is perfectly fine! Each .csv file only has two columns. – koreamaniac101 – 2018-06-13T18:58:26.713

So the output should be a csv file with ~7000 rows and ~500+1 columns? – agtoever – 2018-06-13T18:59:52.923

Precisely. Also, I have edited the original post to include two of the 500 files. As you can see, the first columns of each file are identical. – koreamaniac101 – 2018-06-13T19:04:29.070

Answers

-1

On a Linux shell, combine paste and awk.

paste -d , *.csv | awk -F , '{for (i=3;i<=NF;i+=2) $i=""} {print}'

The paste command puts all columns of all files next to each other. The awk script deletes all odd columns, starting with the third one.

agtoever

Posted 2018-06-13T18:27:05.633

Reputation: 5 490

I followed the instructions and downloaded Ubuntu on my computer. I ran the shell and moved the directory (cd command) to the file of interest. However, when I ran the command you posted, i get the following error:

paste: '*.csv': No such file or directory

I did an ls command to make sure, and all my csv files are there. – koreamaniac101 – 2018-06-14T13:50:21.293

Never mind, I fixed the issue. All my file extensions were shown as .CSV, not .csv. The command is case sensitive to the file extension. My final question: Where is the new file now? The command ran perfectly, but I don't see a new file. – koreamaniac101 – 2018-06-14T13:51:35.557

The output is shown in the console. Redirect it to a file by adding > output.txt to write it to the file output.txt. – agtoever – 2018-06-14T21:07:17.983

Thanks, but I was hoping to have it in a CSV file. The ultimate goal is to have a .csv (or xlsx) file that will have the approximately 7000 columns and 500 rows (one for each of the files' second columns). I redirecting the command to a csv file, but it didn't work properly, and only showed a large single column. – koreamaniac101 – 2018-06-15T13:31:44.290