Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

TheSpook: Aug 21, 2007; Spooky!

(I guess this is the new Megathread?)

I have multiple Excel files, each with a column of countries and some columns of data pertaining to those countries.

File 1:

code:

Country1   1.6
Country2   2.3
...

File 2:

code:

Country1   ABC   4.65
Country2   XYZ   3.21
...

I would like to merge them all into one Excel file, joining on country name. Is there an easy way to do this? I'm not really familiar with VBA.

# ¿ Sep 1, 2009 21:28

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 07:48

TheSpook: Aug 21, 2007; Spooky!

rekk posted:

is it too complicated for copy & paste?

The problem is this:

Some of the files have the countries listed in different orders, some are missing countries entirely.

I don't care about efficiency (it's only 45 files, so too much for a human but not much for a computer) -- my plan is this:

code:

Start some Master.xls
For each file f do
    Find the country column c
    For each row r in c
        If the country in r is in my Master's country list
             append the data to that row in the Master
        Else
             add the country to the Master
             append the data to that new row in Master

The problem is, I have no idea how to VBA

. Working on it, but any suggestions are great.

Again, runtime = schmuntime for this.

Edit: I have it working for one file! Will post the code when finished. This seems like it'd be a very common problem.

TheSpook fucked around with this message at 04:01 on Sep 2, 2009

# ¿ Sep 2, 2009 02:38

TheSpook: Aug 21, 2007; Spooky!

Thanks! We can certainly discuss.

Each of the files turned out to be significantly different, so I'm not using the outer "for each file f" loop. I'm opening each by hand and tweaking my code a little for each file.

The code itself copies all rows of N columns from the source to the destination, matching on some Key Column (in this case, the country name).

If no match is found, a new row (Country, {columns}) is appended to the destination file.

Edit: Here's a version: http://gannon-house.com/matching_module.txt. This is a mess of my own work with snippets from the Internet.

TheSpook fucked around with this message at 05:13 on Sep 2, 2009

# ¿ Sep 2, 2009 05:04

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread