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?

Can someone please help? This is very urgent.

"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?

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?

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?

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?

Hi Vic,

Call the list in columb B " List 1" and the one in column C "List 2" then in
A apply the formula as follow

=IF(NOT(ISERROR(MATCH(List1,List2,0))),List1,"")



"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?

Hi Vic, you could try this:

=IF(COUNTIF($B$2:$B$10,C2),B2,"")

since this is an array formula hit ctrl+shift+enter.

--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"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?

Sorry, ignore the previous message, instead use this formula:

=IF(NOT(ISERROR(MATCH($B$2:$B$7,$C$2:$C$7,0))),B2: B7,"")

CTRL+SHIFT+ENTER when finish typing it.

--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"alexrs2k" wrote:

Hi Vic, you could try this:

=IF(COUNTIF($B$2:$B$10,C2),B2,"")

since this is an array formula hit ctrl+shift+enter.

--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"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?

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?

Here's a macro that will go through your B list, and if its found in C list,
prints it to A list. From the workbook, Press Alt+F11, then goto Insert -
Module. Paste this in.

'=============
Sub CreateDups()
Dim lngRow As Integer, lngRow2 As Integer
Dim NoGood As Boolean

lngRow = Cells(Rows.Count, "B").End(xlUp).Row
lngRow2 = Cells(Rows.Count, "C").End(xlUp).Row
r = 2

For Each cell In Range("B2:B" & lngRow)
NoGood = True
On Error GoTo ErrorHandler
xValue = cell.Value
MyTest = WorksheetFunction.Match(xValue, _
Range("C2:C" & lngRow2), 0)

If NoGood Then
Cells(r, "A") = cell.Value
r = r + 1
End If
Next
Exit Sub
ErrorHandler:
xValue = Range("C2").Value
NoGood = False
Resume

End Sub
'==============

Back in the workbook, you can create a command button (forms
toolbar)/picture or something, and assign the macro to it so your client can
press it after they upload the 2 lists.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Vic" wrote:

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?