I have a list where amounts displayed are either Debits or Credits. I would like to match identical Amounts irrespective of whether it is debit or credit. The values in Column 1 should also be identical.
Example
Col1 Col2
1 A 300
2 B 500
3 C 100
4 A -300
To match column 1 and Column 2. and in this case it would highlight
A 300
A -300
Thanks in advance for your help.
Last edited by kris26; 09-02-2013 at 07:47 AM.
So are you wanting BOTH Col A and Col B matched (regardless of plus or minus figure in Col B) - is that right?
Last edited by Ursul; 08-30-2013 at 08:34 AM.
Try In CF rules.
=COUNTIF($A$2:$A$7,$A2)>1
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
yes. refer to my example
Thanks for your reply
thanks for your reply.
Please refer to my example
only A should be highlighted
A 300
A -300
Pls see my example sheet..Originally Posted by kris26
@Fotis I think we are finding this confusing but I don't think Kris26 wants all the "A"s highlighted but only "A" that has the same number regardless of those numbers be exactly the same or if one is 'plus' and the other 'minus' i.e. so A 300 and A -300 would be a match. I don't think s/he wants only the "A"s highlighted but only "A" that has 2 numbers (plus or minus) the same.
So let's take your example and change the cell B5 to read "-100", then only row 2 and row 5 would be highlighted and NOT also row 7 even though it has "A" in col A because it has a unique number.
I think even I have made this confusing but I am hoping it clarifies this.
@ Ursul
As you see, OP does not clarify what he needs. Anyway..in this case we can use this formula in CF rules.
Please Login or Register to view this content.
@ Fotis - cool formula, but please explain the 'ABS' - never seen that before
Hi there@ Fotis - cool formula, but please explain the 'ABS' - never seen that before
Of course!
ABS function,returns the absolute value of a number. The absolute value of a number
is the number without its sign.
Syntax
=ABS(number)
Number is the real number of which you want the absolute value.
http://office.microsoft.com/en-us/ex...010342132.aspx
@ Fotis
Worked with Excel for years but never come across that one before.
Thank you, appreciated.
You are welcome and thanks for the feed back.
Also welcome to Excel Forum. Here is a great school!
Hi
Thanks for the reply.I think you got it. I am attaching an example and the expected result.
hope it would be clear what I really want.
As I am new to excel I would be grateful if you could explain how to use your formula to highlight same (conditional formatting) as my list consist of thousands of rows.
Thanking you in advance.
Hi there
Sure.....As I am new to excel I would be grateful if you could explain how to use your formula to highlight same (conditional formatting) as my list consist of thousands of rows.
My formula is this.
As i explained before the usage of ABS function, in simple English formula says:Please Login or Register to view this content.
If in range A2:A12000 find the value of A2 AND in range B2:B12000, the value of B2, then give us a true value or else a false value.
So putting the formula in CF rules the true results are highlighted!
Thanks a lot.
was really helpful.
You are welcome and thanks for the feed back.
As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Hi, i just tried this formula that Fortis1991 posted on a General Ledger and found that is has some setbacks. It seems to also mark as "TRUE" values that not only match with different mathematical signs (+/-) but also the same sign. This is at least a problem for my usage. Can anyone help me with this?
For instance "A -10 and A -10" would be matched. I want only "A 10 and A -10" to be matched as they are debit/credit.
We can help you, BUT.
Do not hijack old threads. Start your OWN (Rule 4).
A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.
Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks