How to match numbers in a column

C

Craig Stone

I have a column of 267 numbers (imported from QuickBooks with amounts of
cash received and cash deposited in the bank) represented by positive and
negative numbers. Theoretically for each positive number there should be a
corresponding negative. Of course they don't balance, therefore I would like
to specifically match each number (all the positives with all the negatives)
to determine which amounts don't have an offset to analyze. I have sorted
all the numbers in ascending order and can manually check off each one but
I'd like to automate this process. Is it possible?
Thanks...jcs
 
F

Frank Kabel

Hi Craig
one idea:
1. Insert a helper column
2. Insert the following formula in the first row of this helper column
=IF(ISNA(VLOOKUP(-A1,$A$1:$A$999,1,FALSE)),"no match";"match found")
Copy this down

Note: If you have duplicate amounts this formula can get wrong results:
e.g. two negative amounts of -41,00 and only one positive amount of
41,00

HTH
Frank

All amount that do not have a corresponding negative value will have a
'1' in the helper column. Now you can sort by this helper column
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Matching numbers in a column 1
Difference between two numbers 2
Matching two columns of data 2
adding multiple values from matched search results 3
Match and delete rows on two different sheets from reference numbers 4
How do I create the formula for this calculation? 1
Excel - Match positive and negative numbers 9
Multi-demensional frequency distribution 4

Top