Combining columns from multiple worksheets into a master

[catandhorse1976]

Hi,

I have 10 + worksheets all in the same excel file. Within each sheet there are two columns (A and B) of identical length (approx 100 rows long). Between sheets, the contents in column A are identical (they are variable names) but the contents of the column B (data values) are different.

I would like to create a master sheet which has all the 'B' columns side by side and a single version of the identical 'A' column on the extreme left. For example, if I have 10 worksheets, the master sheet will then have an A column followed by 10 columns labelled 'B-K'.

Thank you.

e.

 

[drsarao]

Hi,
Welcome to Board.

One way of doing it:
1. Add a blank sheet - name it Master or something.
2. In column A copy paste the names from one of the data sheets.
3. In cells B1 to K1 write the Names of the data sheets.
4. In cell B2, enter the formula =INDIRECT(B$1 & "!B" & ROW())
5. Copy this formula across to column K and then whole thing down to row 100 or wherever the names end.
Thus:

Excel Workbook
	A	B	C	D
1	Name	Sheet1	Sheet2	Sheet3
2	ab1	23	77	12
3	ab2	28	83	32
4	ab3	33	89	52
5	ab4	38	95	72
6	ab5	43	101	92
7	ab6	48	107	112
8	ab7	53	113	132
9	ab8	58	119	152
Master

Excel 2003

Cell Formulas
Range	Formula
B2	=INDIRECT(B$1 & "!B" & ROW())
C2	=INDIRECT(C$1 & "!B" & ROW())
D2	=INDIRECT(D$1 & "!B" & ROW())

Excel Workbook
	A	B
1	Name	Data
2	ab1	23
3	ab2	28
4	ab3	33
5	ab4	38
6	ab5	43
7	ab6	48
8	ab7	53
9	ab8	58
Sheet1

Excel 2003

Excel Workbook
	A	B
1	Name	Data
2	ab1	77
3	ab2	83
4	ab3	89
5	ab4	95
6	ab5	101
7	ab6	107
8	ab7	113
9	ab8	119
10	ab9	125
Sheet2

Excel 2003

 

[catandhorse1976]

Thank you drsarao,

Unfortunately this doesn't quite work because the names of my worksheets are long and variable (they include non consecutive dates, for example). It would be quicker just to copy and paste each of my columns in the master than to write out the names of the worksheets in B1 to K1 as you suggest.

 

[drsarao]

You are then looking for a macro solution.
Try this:
(Modify if needed. I have made some presumption which may not be correct)
First insert a blank sheet naming it Master.
Then copy the following code into a code module in the same workbook.
Run when master is active sheet.

Sub Consolidate()
If ActiveSheet.Name <> "Master" Then Exit Sub
With Sheets("Sheet1")  'change this name if needed
    lr = .Cells(Rows.Count, "A").End(xlUp).Row 'last row in sheet 1 to pickup the names.
    .Range("A1:A" & lr).Copy Destination:=ActiveSheet.Range("A1:A" & lr) 'copy names
End With
c = 2 'next available column for data
For Each ws In Worksheets
    If Not (ws Is ActiveSheet) Then
        ActiveSheet.Cells(1, c) = ws.Name 'write sheet name in header
        ws.Range("B1:B" & lr).Copy Destination:=ActiveSheet.Range(Cells(2, c), Cells(lr + 1, c)) 'copy data
        c = c + 1 'go to next column
    End If
Next ws
End Sub

 

[iyyi]

You can add a small UDF that will return the Sheet name from it's number.
then you can retrieve the Sheets names using the column numbers. When you have the Sheet name you can use it within the INDIRECT function.
The formula for the Sheet name would be =SheetName(COLUMN())
the formula for the second Sheet (column B in Master) would be:
INDIRECT("'"&(B1)&"'!B"&ROW())

here is the UDF code

Function SheetName(num As Integer)
  SheetName = Worksheets(num).Name
End Function

 

[catandhorse1976]

Thank you drsarao and iyyi,

I've solved the problem using drsarao's code.

 

[drsarao]

You are welcome!