Problem With Conditional Formatting

[jeffkirk]

Attached is a listing of broker transactions for a month. The first column represents what was settled by the end of the month, as per the broker. The second column represents the total transactions for the month as per my register. The broker downloads daily my trades; I do not enter anything manually. Inasmuch as column one represents only settled trades, the cells that are in column one should also be in column two. However, when I do a Conditional Formatting to highlight duplicate cells in each column, the total of the duplicates in the second column does not equal those in the first column.

Any help?

[Richard Buttrey]

Not sure why you think that. The matches are the same in each column. See attached. I've also conditionally formatted A & B t show which match

[jeffkirk]

Thanks Richard. What is confusing me is that when I do a conditional sort with all of the green cells on top in the second column, I get a total of ($5,845.54) in the green cells (as per the sum feature on the task bar), as compared to the total of $241.96 in the first column. What am I doing wrong?
Thanks again.

[Richard Buttrey]

I presume you're refferring to a Conditional Filterin not a conditional sorting.

When I filter column B for Green I see the total 241.96 so I'm not sure how you arrive at your number.
That's why I put in the column E numbers. These identify where there is a column B cell match with column A, i..e it's the same as the CF that paints the cells green.

In order to be getting a large negative number like you mention I guess somehow you're picking up the non matched cells in B219:B473 althoug they are not exactly the number you mention

[jeffkirk]

I presume you're refferring to a Conditional Filterin not a conditional sorting.

When I filter column B for Green I see the total 241.96 so I'm not sure how you arrive at your number.
That's why I put in the column E numbers. These identify where there is a column B cell match with column A, i..e it's the same as the CF that paints the cells green.

In order to be getting a large negative number like you mention I guess somehow you're picking up the non matched cells in B219:B473 althoug they are not exactly the number you mention

Thanks again. I will check again.

[jeffkirk]

OK, Sheet 2 shows the results of the following procedure: I first highlight Columns A and B. I then click "Conditional Formatting", then "Highlight Cell Rules", then "Duplicate Values", then ok to "Duplicate values with Light Red Fill with Dark Red Text". I then perform a custom sort of Column B to list all of the shaded cells at the top of the list. Summing all of these red cells, I get ($5,724.16). In addition, there are 6 more cells shaded in Column B than are entries in Column A. I must be overlooking something.
Thanks again for your help and patience. art the top

[bebo021999]

Look like it is "Duplicate in pair".
You are trying to mark duplicate in B column, including duplicate itself.
For example: see below image: 4 & 6 in B, does not exist in A, but duplicate itself in B. 1 extra Number 3. All should not be highlighted
Therefore, using these:
For column A:
=COUNTIF($A$2:$A$229,A3)=COUNTIF($B$2:$B$484,A3)
Apply to A only

For column B:
=COUNTIF($B$1:$B2,B2)<=COUNTIF($A$2:$A$229,B2)
Apply to B only

Total duplicate in both column should be equal 430.99

[jeffkirk]

Thanks Bebo.
A little background: Column A represents cleared, i.e. "settled" transactions as per the statement from my broker. There are more transactions listed on the statement ("unsettled") but they are separate and not part of this exercise inasmuch as they occurred after the "cutoff" date the broker uses (usually 2-3 days before the end of the month). Column B represents transactions that I download into Quicken on a daily basis electronically. These latter transactions occurred on each day of the month, including the days after the "cutoff" date used by the broker in his statement. What I am trying to do is "isolate" transactions in Column B that match the "settled" transactions in Column A. If there are duplicates in Column B, that is ok. The total $$ amount of the broker's settled transactions (228 in number) is $241.96. In the end that should be total of the column B cells that are highlighted (disregarding duplicate cells, if any).
In a nutshell, I am trying to match All of the "SETTLED" transactions as per my broker's satement to the correspomding daily transactions in Quicken; hence, I would like all of the broker's transactions matched up with corresponding entry in Quicken, with the total in . There may be duplicates, that's ok.
I hope this helps, and again, thanks.