Compare two columns

[Khurram]

Hi all,
I have column A that contains a list of various items. Next to it are
monthly totals of each item. After that is an empty column. In the
next column (lets assume I) there is another list of items and the
column after contains the total for each item (lets assume J). The
list in column I contains items that should be present in column A
(though not always).

Each item in column I needs to comapred to the list in the column A.
If a match is found then take the correspoding value from column J and
paste it in the next available cell corresponding to that item.

If a match is not found then go to the end of the list in column A,
find the next available cell in column A, paste item there and in the
next column, paste the corresponding total from column J.

Any ideas

Thank you in advance
Khurram

 

[Tom Ogilvy]

As I understood your description, this worked for me.

Test it on a copy of your data

Sub CheckItems()
Dim rng As Range, rng1 As Range
Dim rw As Long, i As Long
Dim cell As Range, res As Variant
Dim icol as Long
icol = 9 ' column i
Set rng = Range(Cells(2, icol), Cells(2, icol).End(xlDown))
For Each cell In rng
If Application.CountIf(Range("A:A"), cell) = 0 Then
rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(rw, 1).Value = cell.Value
Cells(rw, 2).Value = cell.Offset(0, 1).Value
Else
res = Application.Match(cell, Columns(1), 0)
For i = icol - 1 To 1 Step -1
If Not IsEmpty(Cells(res, i)) Then
Cells(res, i + 1).Value = cell.Offset(0, 1).Value
Exit For
End If
Next
End If
Next
End Sub

 

[Khurram]

Thank you Tom,
Does exactly what I asked for and more by the looks of things.

Cheers Tom
Khurram