Matching debits and credits within range of cells

**drewzilla652** · 10-25-2010, 02:31 PM

I have a spreadsheet with data for many accounts. In one of the columns, I have dollars, and another I have usage.
There are times when there are reversing entries, and I would like to highlight these rows. That is, in the dollars column and the usage column there would be negatives, say -$65 and -450 for dollars and usage.
I would like to highlight that row, as well as the row that has positive $65 and positive $450 for that same account number.

I was considering working down the spreadsheet. When there is a negative number encountered, it will look for the matching positive within that same range of cells represented by the same account number.

Any help would be greatly appreciated (understatement)

**jaslake** · 10-25-2010, 03:42 PM

Hi drewzilla652

Being an old retired Ohio bean counter, I can probably help you with this. A sample of your data file would be very helpful (please scrub all confidential information).

John

**drewzilla652** · 10-26-2010, 08:03 AM

Here is the file. Note the negative entries that offset positive ones in the columns on the far right. My goal is to highlight the matching pairs of offset values.

**jaslake** · 10-26-2010, 08:09 PM

Hi drewzilla652

You mentioned Dollar amounts in a previous post but I don't see any in your sample file.

Two things, if you will:

Post a sample file that includes your ACTUAL file layout
Include several examples of the negative values.

I'm looking for relationships in your data. To do so, I need to see what your data actually looks like.

John

**drewzilla652** · 10-27-2010, 11:23 AM

Instead of dollars, I meant Actual On-Peak Demand.
In the SAMPLE file, I am looking for a solution where rows 4 and 6 would be highlighted.
Thanks VERY MUCH in advance for your help.

**jaslake** · 10-27-2010, 03:56 PM

Hi drewzilla652

This code is in the attached

Please Login or Register  to view this content.

Let me know of issues.

John

**drewzilla652** · 10-28-2010, 08:37 AM

This is absolutely fantastic! Thank you SO much for you help and the time you spent.

**jaslake** · 10-28-2010, 12:35 PM

Hi drewzilla652

Glad it works for you. If you're satisfied with the solution, please mark your thread as solved. A tip of the scales is always appreciated.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

John