Is there a way to write a macro to accomplish this? There is too much data
manipulation. I need my client to populate column B, column C then press
something and get column A with the answers. What I need is a column A
containing a list of duplicates based on values of column B and column C. I
may have more rows in column B than C.
Dup List1 List2
50 21 18
63 50 26
68 58 50
71 63 63
77 68 66
85 70 68
86 71 71
90 73 77
98 74 85
77 86
85 89
86 90
88 98
90
93
98
"Luke M" wrote:
Did you read "Extracting Elements Common To Two Lists" on the site?
Referring to your two lists as List1 and List2 respectively, the formula to
use is:
=IF(NOT(ISERROR(MATCH(List1,List2,0))),List1,"")
If you then want to clean this up and get rid of blanks (and perhaps hide
the column with previous formula, you can use the formula Chip suggested
under "No Blanks" (http://www.cpearson.com/excel/noblanks.htm)
=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
As said on Chip's site, these are both array formulas, and need to be
inputted using Ctrl+Shift+Enter, not just enter. Also, note that if you do
not use named ranges and enter the actual cell references, you must use
absolute cell references (e.g., $B$1) rather than relative cell references
(e.g., B1).
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"Vic" wrote:
Hi Eduardo,
Sorry. This did not help at all. I still don't know what to do.
"Eduardo" wrote:
Hi,
Look into CPearson web
http://www.cpearson.com/excel/ListFunctions.aspx
if this helps please click yes, thanks
"Vic" wrote:
I have 2 columns (B and C) of non-sorted invoice numbers starting in row 2. I
need to list in column A invoice numbers that appear in column B and column
C. This means that such invoice passed to criterias and I want to process it.
I have over 1500 invoices. How can I do this with minimum data manipulation
since in the future I need to just insert invoices into column B and into
column C and have an automatic result in column A on a daily basis?