Excel/Showing Duplicate rows even city are fliped
QUESTION: Hello Tom,
We have fare sheet, where origin,destination, farebasis and amount is mentioned.
Sr.no. ORI DEST FAREBASIS Concatenate value AMOUNT
1 NYC BRU LRTUS NYCBRULRTUS 748
2 NYC BRU BRTUS NYCBRUBRTUS 248
3 NYC BRU KRTUS NYCBRUKRTUS 480
4 NYC MAN LRTUS NYCMANLRTUS 987
5 NYC AMS LRTUS NYCAMSLRTUS 748
6 BRU NYC LRTUS BRUNYCLRTUS 243
7 NYC LON VRTUS NYCLONVRTUS 471
8 LON NYC VRTUS LONNYCVRTUS 852
9 MAN NYC LRTUS MANNYCLRTUS 562
my intent is to remove dups data for ORI, DEST and FAREBASIS column only
for normal purpose we used concatenate function and then conditional formatting and duplicate value
but in above case i want the system should show/highlight the data as dups when origin city goes to destination & destination city goes to origin,
e.g in above case (Sr no.1 and 6 , 4 and 9 , 7 and 8) should be highlighted by system. (as only origin and destination is fliped)
hope i explained clearly.
assume ORI is in column B and DEST is in column C and FAREBASIS is in column D with Sr. No 1 in row 2.
Using the new Countifs function introduced in Excel 2007 (which handles multiple conditions)
should be a logical test you can use in conditional formatting to flag the situation you describe.
---------- FOLLOW-UP ----------
QUESTION: Thanks, its working,
Is there any possibility to find out row1 duplicate is in row6, row4 duplicate is in row9.....something like that
as the formula /way you shown in above reply, will only show TRUE AND FALSE, from that we can only estimate that FALSE is a unique value present in data sheet and TRUE has some duplicate value , but how to find in which rows,
You can use and array formula
If you enter that in a cell in row 2. Then select that formula in the formula bar and hit F9, it should return an array of 100 values with false in the non match positions and the row number where there are matches. (this is an array formula and should be entered with Ctrl+shift+Enter although you don't have to do that just to use the F9 evaluation. )
How you want to use that I can't say - but that is how you can determine what the rows are. Normally that would be the main formula in a larger formula that picks our the numbers using SMALL or LARGE.