You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Showing Duplicate rows even city are fliped

Advertisement

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

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

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.