[RESOLVED] Useful two table line up excel

[gphillips]

Hi experts - just want to match two tables side by side lined up by certain fields.

Sheet names list

Start with - contains the start with data (note could be any length - not lined up but some could be line up already or may not be lined up at all)

Two table line up - contains a sample example of what the startwith data should look like after the code is
run in the output two table line up sheet.

Supporting info - if not clear is explained under the supporting info explanataion.

Any ideas of how this could be done as I'm struggling. All info should be in the spreadsheet attached by the viusal pattern between start with amd two table line up. Also supporting info gives a more descriptive explanation.

Much appreciated.

[westconn1]

this seems to work with the limited data in the test sheet, but it maybe neccessary to run another similar evaluation to check the values of item parts when the ref is the same

i = 3
Do Until i = ActiveSheet.UsedRange.Rows.Count
        If Cells(i, 1) > Cells(i, 9) Then
            Range(Cells(i, 1), Cells(i, 4)).Insert xlDown
            
        ElseIf Cells(i, 1) < Cells(i, 9) Then
            Range(Cells(i, 9), Cells(i, 13)).Insert xlDown
            
        End If
        i = i + 1
Loop

[gphillips]

Hi,

Thanks for this. Very clever!!!!! Few points first of all, the original code doesn't work for the attached scenario.

It just loops on forever to the bottom of the spreadsheet. Ref/part could be string or numeric.
One table could also have more records than another.

Also I am trying to get the complete solution I have posted my attempt. Please note both ref and part are the lining up mechanisms combined but this doesn't work either - it just loops on forever.

i = 3
Do Until i = ActiveSheet.usedrange.Rows.Count
        If Cells(i, 1) > Cells(i, 9) Or _
            Cells(i, 2) > Cells(i, 10) Then
            Range(Cells(i, 1), Cells(i, 4)).Insert xlDown
 
        ElseIf Cells(i, 1) < Cells(i, 9) Or _
        Cells(i, 2) < Cells(i, 10) Then
            Range(Cells(i, 9), Cells(i, 13)).Insert xlDown
 
        End If
        i = i + 1
Loop

Cheers once again.

[westconn1]

try this

Sub lineup()
i = 3
Do Until i = ActiveSheet.UsedRange.Rows.Count
    If Not IsEmpty(Cells(i, 1)) And Not IsEmpty(Cells(i, 9)) Then
        If Cells(i, 1) > Cells(i, 9) Then
            Range(Cells(i, 1), Cells(i, 4)).Insert xlDown
            
        ElseIf Cells(i, 1) < Cells(i, 9) Then
            Range(Cells(i, 9), Cells(i, 13)).Insert xlDown
        ElseIf Cells(i, 1) = Cells(i, 9) Then
            If Cells(i, 2) > Cells(i, 10) Then
                Range(Cells(i, 1), Cells(i, 4)).Insert xlDown
            ElseIf Cells(i, 2) < Cells(i, 10) Then
                Range(Cells(i, 9), Cells(i, 13)).Insert xlDown
            End If
            
        End If
    End If
        i = i + 1
        
Loop
End Sub

[gphillips]

Hi,

Getting there, for some reason , it does not work if the very bottom ones are different on both tables are different.
It seem to get lost at the very bottom few lines. try changing a few
Any ideas

[westconn1]

dunno, try this

Do Until i > ActiveSheet.UsedRange.Rows.Count

or add a couple of extras to the rows count

[gphillips]

Top man, 5 star rating for this stanadard useful tool.
It works if you add plus 1 on end of code above!

Thanks very much