Matching unordered list
 

I have two sheets containing unordered serial numbers. In fact it is a bank
reconciliation, one list is generated by the bank containing cheque numbers
in full and not by cheque order but by value date, another sheet contains all
cheques issued, but cheque number have had to be truncated because of
limitation in size of field; the last four numbers are commonly used.

I have to bring the date from the bank statement (sheet2) to a blank colum
in (Sheet1) the list of outstanding cheques.

Can anyone give me a lead as how to go about.

Thks for your usual support

Matching unordered list
 

if in sheet 1 (bank)
check number is in column a
Amount is in Column B
Date is is column C
and sheet 2
shorthand check number in column A
amount in Column B
you want date in Column C

if you have too few checks to have a duplicate in the shorter checknumbers
try
in sheet 2 C1
=sumproduct(--(Sheet1!A$1:A$1000-int(Sheet1A$1:A$1000/10000*10000=A1),Sheet1!C$1:C$1000)
copy down to end of data

if there is a chance of a duplicate
=sumproduct(
--(Sheet1!A$1:A$1000-int(Sheet1!A$1:A$1000/10000)*10000=A1),--(Sheet1!B$1:B$1000=B1),Sheet1!C$1:C$1000)



"Wins07" wrote:

I have two sheets containing unordered serial numbers. In fact it is a bank
reconciliation, one list is generated by the bank containing cheque numbers
in full and not by cheque order but by value date, another sheet contains all
cheques issued, but cheque number have had to be truncated because of
limitation in size of field; the last four numbers are commonly used.

I have to bring the date from the bank statement (sheet2) to a blank colum
in (Sheet1) the list of outstanding cheques.

Can anyone give me a lead as how to go about.

Thks for your usual support

Matching unordered list
 

Thks it worked out fine except that uncleared cheques displayed 1/0/1910
do you have one alternative to display some text in place of dates using the
same process.

"bj" wrote:

if in sheet 1 (bank)
check number is in column a
Amount is in Column B
Date is is column C
and sheet 2
shorthand check number in column A
amount in Column B
you want date in Column C

if you have too few checks to have a duplicate in the shorter checknumbers
try
in sheet 2 C1
=sumproduct(--(Sheet1!A$1:A$1000-int(Sheet1A$1:A$1000/10000*10000=A1),Sheet1!C$1:C$1000)
copy down to end of data

if there is a chance of a duplicate
=sumproduct(
--(Sheet1!A$1:A$1000-int(Sheet1!A$1:A$1000/10000)*10000=A1),--(Sheet1!B$1:B$1000=B1),Sheet1!C$1:C$1000)



"Wins07" wrote:

I have two sheets containing unordered serial numbers. In fact it is a bank
reconciliation, one list is generated by the bank containing cheque numbers
in full and not by cheque order but by value date, another sheet contains all
cheques issued, but cheque number have had to be truncated because of
limitation in size of field; the last four numbers are commonly used.

I have to bring the date from the bank statement (sheet2) to a blank colum
in (Sheet1) the list of outstanding cheques.

Can anyone give me a lead as how to go about.

Thks for your usual support

Matching unordered list
 

Equation Starts getting Long
=if(sumproduct(--(Sheet1!A$1:A$1000-int(Sheet1A$1:A$1000/10000*10000=A1),Sheet1!C$1:C$1000)=0,"",=sumproduc t(--(Sheet1!A$1:A$1000-int(Sheet1A$1:A$1000/10000*10000=A1),Sheet1!C$1:C$1000)
)


"Wins07" wrote:

Thks it worked out fine except that uncleared cheques displayed 1/0/1910
do you have one alternative to display some text in place of dates using the
same process.

"bj" wrote:

if in sheet 1 (bank)
check number is in column a
Amount is in Column B
Date is is column C
and sheet 2
shorthand check number in column A
amount in Column B
you want date in Column C

if you have too few checks to have a duplicate in the shorter checknumbers
try
in sheet 2 C1
=sumproduct(--(Sheet1!A$1:A$1000-int(Sheet1A$1:A$1000/10000*10000=A1),Sheet1!C$1:C$1000)
copy down to end of data

if there is a chance of a duplicate
=sumproduct(
--(Sheet1!A$1:A$1000-int(Sheet1!A$1:A$1000/10000)*10000=A1),--(Sheet1!B$1:B$1000=B1),Sheet1!C$1:C$1000)



"Wins07" wrote:

I have two sheets containing unordered serial numbers. In fact it is a bank
reconciliation, one list is generated by the bank containing cheque numbers
in full and not by cheque order but by value date, another sheet contains all
cheques issued, but cheque number have had to be truncated because of
limitation in size of field; the last four numbers are commonly used.

I have to bring the date from the bank statement (sheet2) to a blank colum
in (Sheet1) the list of outstanding cheques.

Can anyone give me a lead as how to go about.

Thks for your usual support