Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two lists
I have two lists, a long list like:
1 itemA 2 itemB 4 itemC 5 itemD and a short list like: 2 null 4 null I'm looking for a method that would result in a chopped long list like this: 2 itemB 4 itemC So, I'd like to automatically compare the lists and as the result remove all rows from the long list that contain a number in the leftmost cell that is not present in the leftmost cells of the short list. In other words, I'd like to remove the row with itemA and the row with itemD based on the fact that those rows are not present on the second list. (Note that the number is a value in a cell, not a row number.) How could I manage this? I'm new with Excel so newbie explanation is appreciated. It doesn't have to be done in Excel but I thought Excel might be good at this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two lists
use countif
C1 of the long list =if(countif(Sheet2!$A:$A,A1)=1,na(),"") then drag fill down the column select C do Edit=goto Special and select formulas and Errors, then do Edit=Delete and choose entire row. Delete column C in code Dim rng as Range, rng1 as Range With Worksheets(Sheet1) set rng = .Range(.Cells(1,1),.Cell(rows.count,1).end(xlup)) End with rng.offset(0,2).Formula = "=if(countif(Sheet2!$A:$A,A1)=1,na(),"""")" On Error Resume Next set rng1 = rng.offset(0,2).specialCells(xlFormulas,xlErrors) On Error goto 0 if not rng1 is nothing then rng1.EntireRow.Delete end if -- Regards, Tom ogivy then drag fill down the column "Pauli Soininen" wrote in message ... I have two lists, a long list like: 1 itemA 2 itemB 4 itemC 5 itemD and a short list like: 2 null 4 null I'm looking for a method that would result in a chopped long list like this: 2 itemB 4 itemC So, I'd like to automatically compare the lists and as the result remove all rows from the long list that contain a number in the leftmost cell that is not present in the leftmost cells of the short list. In other words, I'd like to remove the row with itemA and the row with itemD based on the fact that those rows are not present on the second list. (Note that the number is a value in a cell, not a row number.) How could I manage this? I'm new with Excel so newbie explanation is appreciated. It doesn't have to be done in Excel but I thought Excel might be good at this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing two lists
what you want to use is the vlookup. In the function wizard or help, look up
the function VLOOKUP or HLOOKUP, depending on whether the lists are horizontal or vertical. I would suggest something like this: =IF(ISERROR(VLOOKUP([reletive cell reference],[absolute range],1,FALSE)),"Delete Row","Do Nothing") If you use this function in a column adjacent to the long list, the value Delete Row will appear next to those rows in the long list which do not appear in the short list. You could use VBA to automate the deletion of these rows, but since you are a beginer it'll be easier for you to do manually. "Pauli Soininen" wrote: I have two lists, a long list like: 1 itemA 2 itemB 4 itemC 5 itemD and a short list like: 2 null 4 null I'm looking for a method that would result in a chopped long list like this: 2 itemB 4 itemC So, I'd like to automatically compare the lists and as the result remove all rows from the long list that contain a number in the leftmost cell that is not present in the leftmost cells of the short list. In other words, I'd like to remove the row with itemA and the row with itemD based on the fact that those rows are not present on the second list. (Note that the number is a value in a cell, not a row number.) How could I manage this? I'm new with Excel so newbie explanation is appreciated. It doesn't have to be done in Excel but I thought Excel might be good at this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing lists - i think | Excel Discussion (Misc queries) | |||
Comparing Lists | Excel Worksheet Functions | |||
Comparing Lists to Partial Lists | Excel Programming | |||
COMPARING LISTS | Excel Programming | |||
Comparing 2 Lists | Excel Programming |