Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 col listings n append items in A not in B to bottom of B
In Sheet1 (the codename)
In B19 down is a list of unique items: xx yy zz In Sheet2 (the codename) In A3 down is another list of unique items: aa yy bb I need a sub to compare the 1st list with the 2nd, and to append any items in the 1st which are not found in the 2nd list, to the bottom of the 2nd list. Example, for the sample data above, the 2nd list should finally look like this: aa yy bb xx zz (xx, zz are appended) The sub is to reference the 2 codenames Thanks for insights |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 col listings n append items in A not in B to bottom of B
Hi Max,
Try the following. Sub AppendMissingData() Dim rng1 As Range Dim rng2 As Range Dim cel As Range Dim foundCel As Range With Sheet1 Set rng1 = .Range("B19", _ .Cells(.Rows.Count, "B").End(xlUp)) End With With Sheet2 Set rng2 = .Range("A3", _ .Cells(.Rows.Count, "A").End(xlUp)) End With For Each cel In rng1 Set foundCel = rng2 _ .Find(What:=cel.Value, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If foundCel Is Nothing Then Sheet2.Cells(Sheet2.Rows.Count, "A") _ .End(xlUp).Offset(1, 0) = cel.Value End If Next cel End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 col listings n append items in A not in B to bottom
That was marvellous, OssieMac !
Many thanks for your sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 col listings n append items in A not in B to bottom
Thanks for the feed back Max. However, I have realized that if the range in
sheet1 had multiple instances of 'new' values that are the same, it will append them multiple times in sheet2. If there will never be multiple instances of the 'new' data then no problem but if there can be then replace the sheet2 range code with the following and it will always test against the updated list including any appended data and only append one instance of missing data. With Sheet2 Set rng2 = .Range("A3", _ .Cells(.Rows.Count, "A")) End With -- Regards, OssieMac "Max" wrote: That was marvellous, OssieMac ! Many thanks for your sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 col listings n append items in A not in B to bottom
Noted with thanks, OssieMac.
It should not happen (dups in Sheet1) but you never know |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filter data by top 10 AND bottom 10 items simultaneously | Excel Worksheet Functions | |||
How to have a the liist items appear at the top, not bottom | Excel Worksheet Functions | |||
Moving Items to bottom of spreadsheet based on criteria | Excel Programming | |||
Compare ranges n copy at bottom of first range | Excel Programming | |||
Compare and Append | Excel Programming |