[rochey]

Hi guys, hope someone can help with this, though I'm not sure if it's really possible.

I've got hundreds of Excel sheets which all use the same variable (column header) names, except that they all have different numbers of variables. For example one sheet might have:

S_1 | S_11 | S_111 | S_112 | S_12 | S_121 | S_122 | S_2... etc

Whereas another sheet might just have:

S_1 | S_11 | S_12 | S_2

So if you can imagine, hundreds of sheets which all use the same coding for the variables, but each of which reporting different numbers of variables.

I want to get all of the sheets into a single sheet, the eventual goal is to convert it to just one Stata file, but the fact that the sheets are not of a consistent format is a real problem.

What I really need is for Excel to recognise the header names and move data according to those, rather than according to which cells data occupy. Is there some kind of macro which can do this? Or am I just going to have to do it manually? (which will take all year!)

[slurpee55]

can you post a sample - fake some data if you need to.

[rochey]

okay, i've attached a file - it's publicly available data so no worries on that front.
So i want to automate a process whereby i convert the three worksheets into a single sheet. But if I did this through a concatenate type of thing, it would put Cyprus S_2122 underneath Maldives S_211, because it's column L. I suppose I'd need to create a template which has all possible series names, and somehow automate it from there?

[slurpee55]

I don't see an attachment - if it is there, alas, I probably won't see it - I don't know why, but the system has some problems.

[rochey]

oops... let's see if the attachment works this time round...

[slurpee55]

Do you have a full listing of the various possible headings?

[rochey]

I don't currently, but its possible for me to make one, although it'll take a while

[slurpee55]

It would probably be possible to collect the data based on the column headings on each sheet and then, once that is done, combine all the data based on that.

[rochey]

that's what i was thinking... any ideas how i would go about doing that?

[slurpee55]

Probably a formula using INDIRECT - if you can post the headers, we can do a search and match
see this post for something similar in concept
http://forums.techguy.org/business-a...ls-macros.html

[rochey]

okay, here is a template which (hopefully) has every possible header, at least in the context of the example sheet. So, it's possible to write a macro that will move the data in the three sheets to this new template sheet, and place the data according to the header names?

[OBP]

I would add Names to your Column Headers that basically match the Header title, that should make writing the macro that much easier to implement.

[slurpee55]

Hmmm, gave you the wrong link....
Oh well, here is a start - thanks to AJ_Old in another thread, this formula looks up the value in the top of the column and finds it in the column with the data below it.
Do this for each page - keeping all the headings in always the same place (start at column AR) and then you will have the data with the same formatting for each page. It should be easy to combine them later.
Oh, and it says there is a circular reference in a formula - I can't find it....

[Aj_old]

Rochey, try this code:

Sub DoWhatIWanna()
Application.ScreenUpdating = False

'Adds a new sheet and names it to "Master"
Sheets.Add before:=Sheets(1)
Set shm = ActiveSheet
shm.Name = "Master"

' Freezes names (first row, and first 9 columns, to the right of column I)
Range("J2").Select
ActiveWindow.FreezePanes = True

' Copy the header form the first line, of the second sheet, to the master sheet
Sheets(2).Rows("1:1").Copy
shm.Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

'Copy data from all the sheets to the master one and put it in right place!
For Each sh In ActiveWorkbook.Sheets
    If sh.Name <> "Master" And sh.Name = sh.Cells(2, 1).Value Then
        rws = sh.Range("A65536").End(xlUp).Row
        cls = sh.Range("IV1").End(xlToLeft).Column
        rw = shm.Cells(65536, 1).End(xlUp).Row + 1
        With WorksheetFunction
        cl = 1
            For i = 1 To cls
                countt = .CountIf(shm.Rows("1:1"), sh.Cells(1, i).Value)
                If countt = 1 Then
                    cl = .Match(sh.Cells(1, i).Value, shm.Rows("1:1"), 0)
                    shm.Cells(rw, cl).Resize(rws - 1, 1).Value = sh.Cells(2, i).Resize(rws - 1, 1).Value
                ElseIf countt = 0 Then
                    cl = .Match(sh.Cells(1, i).Value, shm.Rows("1:1"), 1) + 1 'cl + 1
                    shm.Cells(rw, cl).EntireColumn.Insert shift:=xlToRight
                    shm.Cells(rw, cl).Resize(rws - 1, 1).Value = sh.Cells(2, i).Resize(rws - 1, 1).Value
                    shm.Cells(1, cl).Value = sh.Cells(1, i)
                Else
                
                End If
            Next i
        End With
    End If
Next sh
shm.Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

Please tell us how it works!

[slurpee55]

AJ, would your code here

Sheets(2).Rows("1:1").Copy
shm.Cells(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False

require that the sheet have all the possible values for headers in it?