I have approx 7000 transactions.
the credits have a 6 digit transaction number
The debits have 10 digits transaction number.
The last six digits of the credit transaction number will match the
last 6 digits of the debit transaction
When a match is made the macro must insert a line above the debit and
add in the details of the credit.

Here is what is should look after a Macro run to match debits and credits
(Bracketed figures are credits)

transaction
number          amount
    012867     (446.00)
8907012867	446.00
    012868     (204.00)
8907012868	204.00
    012869	(200.00)
8907012869	 200.00)
    012875	(129.00)
8907012875       129.00

You can generate your own set of figures to check your macro

If all you are trying to do is get your related transactions on
successive rows, then you could do it by copying all of your
transactions into the same sheet and use a formula like this:

=RIGHT(A1,6)

If the transaction number is in a column other than A, then change A
in this formula to the letter of your transaction column.  Paste the
formula into a blank column in all of the rows that have a
transaction, then sort by this column to get the debits and credits on
successive rows.

Possibly I've altogether misunderstood what you are trying to
accomplish, but hope this helps.

Hi willcodeforfood-ga

Yep it was helpful, but a bit time consuming to find the matched pairs
of debits and credits.  Is there a way to find the matched pairs
faster - or could a formula or macro get all the matched pairs
together by rows all sorted from the ones that did not get matched
(and will need further investigation).

Column A: transaction numbers
Column B: =RIGHT(A4,6)                        <-- formula for B4
Column C: =IF(AND(B3<>B4, B4<>B5), "X", "")   <-- formula for C4

Now any transactions that don't have a match are marked with an X in
Column C.  If you want all of the unmatched transaction numbers
together, then sort on Column C.

Actually if you sort on Column C the formula will stop working, so we
may have to figure another method to get all of your non-matched
transaction numbers together.

Hi, Bob. I've asked peltiertech to come have a look.

hi - willcodeforfood - ga 

I've tried your formula : =IF(AND(B3<>B4, B4<>B5), "X", "")   <-- formula for C4
Now any transactions that don't have a match are marked with an X in
Column C.  If you want all of the unmatched transaction numbers
together, then sort on Column C.
________________________________________________________________________________
Sorry - It failed - many "no match" transactions got a "X" mark!

You will need to try something else

I thought you were looking for transaction without a match, so all
unmatched transactions would have had an X.  If you are wanting to
find the transactions that do have a match, adjust column C's formula
to:
 =IF(OR(B4=B3,B4=B5),"X","")  <-- formula for C4

Now those that have a match will be marked with an X.

You can combine both of the columns into one if you want.

That is,

=RIGHT(A4,6)
=IF(OR(B4=B3,B4=B5),"X","")  

becomes

=IF(OR(RIGHT(A4,6)=RIGHT(A3,6),RIGHT(A4,6)=RIGHT(A5,6)),"X","")

Instead of putting an X, you can also have Excel just highlight the
matches by using the conditional formatting feature in Excel (under
the format menu).

Just FYI, the above if statement only works if the entries are right
next to each other.  To search through the entire list for a match,
you can use the countif function in the if statement.

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

Yours for B4 would be 

=IF(COUNTIF(A:A,A4)>1,"Duplicate","")

See this to highlight the duplicates:

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

Your conditional formatting for A4 would be 

=IF(COUNTIF(A:A,A4)>1,TRUE,FALSE)