July 21, 2008 8:23 AM Subscribe

What's the easiest way to merge data from multiple Excel worksheets into one big worksheet

I have a workbook with 223 worksheets. Each worksheet contains information about a given geographic area. The information for any given variable is in the same cell each time. I would like to convert this into one big worksheet.

---------------

Example of what I have:

Worksheet 1 [Area 1]:

Blue: 3

Black: 5

Green: 4

Worksheet 2 [Area 2]:

Blue: 5

Black: 2

Green: 5

------------------

Example of what I want

ONE BIG WORKSHEET

Area, Blue, Black, Green

1, 3, 5, 4

2, 5, 2, 5

--------------------------

If, for example, the value for 'Blue' will always be in cell B3, then, essentially I want a macro that works like this:

For i < 224

BIGSHEETBi=SHEETi!B!3
posted by ewiar to Computers & Internet (2 answers total) 3 users marked this as a favorite

I have a workbook with 223 worksheets. Each worksheet contains information about a given geographic area. The information for any given variable is in the same cell each time. I would like to convert this into one big worksheet.

---------------

Example of what I have:

Worksheet 1 [Area 1]:

Blue: 3

Black: 5

Green: 4

Worksheet 2 [Area 2]:

Blue: 5

Black: 2

Green: 5

------------------

Example of what I want

ONE BIG WORKSHEET

Area, Blue, Black, Green

1, 3, 5, 4

2, 5, 2, 5

--------------------------

If, for example, the value for 'Blue' will always be in cell B3, then, essentially I want a macro that works like this:

For i < 224

BIGSHEETBi=SHEETi!B!3

For anyone with the same problem, I also hacked together the following far less elegant solution.

Three columns: A, B, C

In A: 1 through n (A1: 1, A2: "=A1+1", fill down)

In B: " '=Sheet "; fill down

In C: ![Cell coordinates]; fill down

Concatenate the three in a fourth column: "=concatenate(B1, A1, C1)"; fill down

Cut and paste the column with the concatenated formulae (the first will look like "=Sheet1![Cell Coordinates]") into Notepad. Copy and paste from Notepad back into Excel into the desired column.

posted by ewiar at 1:08 PM on July 21, 2008

Three columns: A, B, C

In A: 1 through n (A1: 1, A2: "=A1+1", fill down)

In B: " '=Sheet "; fill down

In C: ![Cell coordinates]; fill down

Concatenate the three in a fourth column: "=concatenate(B1, A1, C1)"; fill down

Cut and paste the column with the concatenated formulae (the first will look like "=Sheet1![Cell Coordinates]") into Notepad. Copy and paste from Notepad back into Excel into the desired column.

posted by ewiar at 1:08 PM on July 21, 2008

This thread is closed to new comments.

posted by inigo2 at 8:40 AM on July 21, 2008