Macro to Compare/Consolidate Columns

Hi,

I have a Master sheet which contains a set of Columns. I need a Macro that:

1 - Looks at the columns in Master.
2 - Compares these against the Second sheet (Subset)
3 - Aligns all the columns in the Subset so they are exactly the same as the Master.

- If the Subset contains anymore which do not exist on the Master the should be added to the last column so all the previous ones match.

I have attached a sample of the data and expected output. Can someone please help?

The following code does the sort on the column headings which are on row 5 however it only works for around 10 columns and I have around 250 columns:

Public Sub Test()

Dim FirstRow As Integer
Dim LastRow As Long
Dim i As Integer

FirstRow = 5
LastRow = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row

For i = FirstRow To LastRow
    For j = 1 To Sheets("Master").Cells(i, Columns.Count).End(xlToLeft).Column
        If Sheets("Master").Cells(i, j).Value <> Sheets("Subset").Cells(i, j).Value Then
            NextCol = Sheets("Subset").Cells(i, Columns.Count).End(xlToLeft).Column + 1
            Sheets("Subset").Cells(i, j).Cut Destination:=Sheets("Subset").Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1)
            Sheets("Subset").Cells(i, j).Delete shift:=xlToLeft
        End If
    Next j
Next i
           

End Sub

When I run the macro it runs for a few minutes then I get 'Type Mismatch' which jumps to the code. Please be aware there is data below the column headings too. Can someone please help?

Can anyone help?

When it 'jumps to the code' which line is highlighted?

Also does it stop at the same point every time (i.e. the same number of columns have been moved?

Lastly, I had a quick look at the code, and it seems that you are cycling through every cell in the data range (from first row - last row, from columns 1 to the last column), and comparing the values between the two sheets, so not only are you comparing the headings, but every individual data point!  

Actually, LASTLY, what is the point of the line "NextCol = ..."? You don't use NextCol anywhere in the code?

My guess is that the error occurs on the comparison line ( "<>" ) and it is a result of you comparing DATA points that are not of the same type, or that contain an error value in the cell.

My suggestion would be to lose the outer loop and cut and paste ranges, rather than individual cells...  Something like this (untested):

*** EDIT:  Ok, I've determined that your macro doesn't successfully sort the columns anyway (I tested it) In order for it to work, the inner IF should be a WHILE - so that it keeps shuffling the current column to the end until it finds the one it's looking for.  Of course if your MASTER sheet contains a column that isn't in the Subset sheet, it will loop forever (it won't find the column. ***

Now the tested version:

Public Sub Test2()

    'Modified by Rick Williams 19/8/2010

    Dim FirstRow As Long 'Long, just to be consistent

    Dim LastRow As Long

    FirstRow = 5

    LastRow = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row

    'Dropped the outer i loop, just compare the headings, that are on 'FirstRow'

    For j = 1 To Sheets("Master").Cells(FirstRow, Columns.Count).End(xlToLeft).Column

        'While the column heading isn't the one I'm looking for...

        While Sheets("Master").Cells(FirstRow, j).Value <> Sheets("Subset").Cells(FirstRow, j).Value

            'Move the COLUMN to the right of the last column

            Sheets("Subset").Range(Sheets("Subset").Cells(FirstRow, j), Sheets("Subset").Cells(LastRow, j)).Cut

            Sheets("Subset").Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0, 1).Insert Shift:=xlToRight

            '(cut-insert takes just one action, rather than two with cut-paste-delete)

        Wend

    Next j

End Sub

Rather than look at every single value it just shifts the columns as a whole (improving the speed by a factor of (lastrow-firstRow-1)  which is presumably a lot for your data.  Also, the data will now sort properly, by sticking on each column until the correct one is found.    Note that this is still an in efficient algorithm, as the time taken varies as the square of the number of columns.  I think on average, for your 250 columns, you will cut/paste about (250+1)/2*(250/2) ~16000 times, and the worst case will be double that - 32k cut/pastes.  A better solution would be to search for the right column to cut/paste in each location, thereby only moving each column once.  But there are entire computer science subjects on sorting algorithms...

Ah hell, I wrote it for you: :-P

Public Sub InsertionSort()

Is there a type with Sub Test2(). When I ran it with a small number of columns i.e. 10
it seemed to keep looping and never end. I let it run for at least 5min before pressing Esc to end the Macro which then
pointed to 'Wend'.

I ran InsertionSort on a few columns and it did sort the columns so they were aligned with the Master however what it did not do was
include columns that were in the Master and not in the Subset sheet. So it basically worked one way. It also never moved any of the data below the column
headings.

Once the sort is done on the Subset sheet the end game is to copy all the data under the headings and paste it into the Master. I will let you know
about the timings once the Macro is working both ways i.e Subset checks Master and aligns itself. Then Subset checks if there any columns in the Master
which need to be included in the Subset.

Thanks for your helps far!

Hi Rick,

Did you get a chance to review my response?