Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns
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 -- Regards, Tom Ogilvy "Khurram" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare two columns
Thank you Tom,
Does exactly what I asked for and more by the looks of things. Cheers Tom Khurram |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two columns | Excel Worksheet Functions | |||
Compare two columns | Excel Discussion (Misc queries) | |||
COMPARE 2 COLUMNS | Excel Discussion (Misc queries) | |||
Compare all following columns when value is same | Excel Programming | |||
Compare columns of value | Excel Programming |