Matching two invoice numbers

[paulsolar]

Hi All

I started something last night at 7 and at 11 I gave up and went to bed, I'd had enough. I'm sure there must be a simple way of doing what I was attempting to do, but I cant seem to get it correct.

I've managed everything bar the last part, the last thing I need to do is below.

I have two worksheets "Invoice Record" and "Accountancy Export" in column A of both sheets I have the invoice numbers. What I'm trying to do is if an invoice number is in "Accountancy Export" find the row in "Invoice Record" and add "Yes" in column L. Ideally the search for the match should start at the bottom of the "Invoice Record" and work up and stop when it reaches the first "Yes" in column L.

Any assistance would be greatly appreciated as this is driving me mad!!

cheers

Paul

 

[Trebor76]

Hi Paul,

When you say...

Ideally the search for the match should start at the bottom of the "Invoice Record" and work up and stop when it reaches the first "Yes" in column L.

...it seems to me that you're looking for as macro which though one could be written why not just this formula in Col. L of the Invoice Record tab:

=IF(ISERROR(VLOOKUP(A2,'Accountancy Export'!A:A,1,FALSE)),"","Y")

Regards,

Robert

 

[etaf]

if all you want is a YES if there is a match - then a countif() should do
not sure i understand the

Ideally the search for the match should start at the bottom of the "Invoice Record" and work up and stop when it reaches the first "Yes" in column L.

does it matter which in voice as you are NOT returning anything - just want a match

=countif(Accountancy Export'!A:A, A2)
not ideal to reference full column - so add a range if possible - max likely to be

=IF( countif(Accountancy Export'!A:A, A2)>0 , "yes", "No"}

 

[Mr_Kay]

Hey paulsolar,

I did a small scale test and, if I'm understanding you correctly, this should work for your use case.
Just paste the formula at the bottom into the top row of your L column, make sure the cell reference in the match function is the invoice number on that row, and propagate the formula down.

Proving Grounds.xlsx
	A	B	C
1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10
Invoice Record

Proving Grounds.xlsx
	A	B	C
1
2	12
3	5		Yes
4	14
5	9		Yes
6	111
7	7		Yes
8	4		Yes
9	21
10
Accountancy Export

Cell Formulas
Range	Formula
C2:C9	C2	=IF(ISERROR(MATCH(A2, 'Invoice Record'!A:A, 0)), "", "Yes")

 

[paulsolar]

Hi all

Many thanks for your replies,

I hadn't considered doing it with a formula to be honest. The issue is that when the match is completed I intend to clear the Accountancy Export export sheet ready for the next batch of invoices. I suppose I could do a work around somehow and copy and paste values before I clear the Accountancy Export sheet to preserve the values and add the formula in the macro.

I'll give this a go

many thanks for your help, Cheers, Paul