I have a spreadsheet with a column of hundreds of accounting values, both debits and credits. Is there a way to have Excel pull all of the values that have an exact matching negative value in this same column into another column?
For example, in the list 45,65,87,93,200,-45,-93,-7
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi
It depends on whether you can sort the list or not. if you can't sort the list, and assuming that list is in A1:A100, I would enter this in B1:B100 (starting from B1):
=IF(ISERROR(VLOOKUP(-A1,$A
in this way you have blank cells or the values that have an exact negative match in the list. It is still necessary to copy and paste values only and sort column B to get the non-blank values grouped together though.
Try this to return the address of the matched item: (I put this into B1, and dragged all the way to the end of the numbers listed in A)
=IF(ISERROR(ADDRESS(MATCH(
Once you have the cell references, you can use indirect() to get the value of the matched cell.
Hope this helps...
Here is an alternative solution. When you run the macro against the selected cells it links each matching value to it's negative counterpart with a note stating where the matching value is. Click the link to select the counterpart.
To use, add the code below to any general code module, select the transactions, and run the macro "LinkTransactions". To remove the links run the macro "UnlinkTransactions".
Public Sub LinkTransactions()
Dim SourceCell As Range
Dim MatchCell As Range
Selection.Hyperlinks.Delet
On Error Resume Next
For Each SourceCell In Selection
Set MatchCell = Nothing
Set MatchCell = Selection.Find(-SourceCell
If Not MatchCell Is Nothing Then
SourceCell.Hyperlinks.Add SourceCell, "", MatchCell.Address, "Matching value found in cell " & MatchCell.Address(False, False)
End If
Next SourceCell
End Sub
Public Sub UnlinkTransactions()
Selection.Hyperlinks.Delet
End Sub
To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.
To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.
Kevin
Business Accounts
Answer for Membership
by: zorvekPosted on 2008-04-25 at 12:50:08ID: 21442719
You can use conditional formatting. Select the values and choose the menu command Format->Conditional Formatting. Select Formula is and enter this formula:
=COUNTIF(A1:A100,-A1)>0
where A1:A100 is the selection and A1 is the active cell (first cell selected). Choose a color to highlight cells that have a matching value.
Kevin