You are here:

Excel/Showing Duplicate rows even city are fliped

Advertisement


Question
QUESTION: Hello Tom,

We have fare sheet, where origin,destination, farebasis and amount is mentioned.
e.g

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.

ANSWER: Rakesh,




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)

countifs(C:C,B2,B:B,C2,D:D,D2)>0

should be a logical test you can use in conditional formatting to flag the situation you describe.  

--
Regards,
Tom Ogilvy



---------- 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,

Thanks
Rakesh

Answer
Rakesh,

You can use and array formula

=if(($C$2:$C$100=$B2)*($B$2:$B$100=$C2)*($D$2:$D$100=D2),Row($D$2:$D$100))

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.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.