• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding duplicate in single rows but over the whole table

S

skofield

New Member
Hi,

I hope you can help me. I'm new here and not sure about all the english excel terms but I will try to get across what I need. I have a table with a bunch of data and what I need to know is if there is a duplicate within every single row. As an example, I have this table:

VW Audi BMW Opel
Audi Opel VW Audi
Opel BMW Audi VW
BMW Opel Audi VW

So what I would need is that it makrs the second row because there is "Audi" in there two times. It doesn't really matter if it marks the whole row or the "Audi" itself.
The thing is, the file that I'm currently working on has 399 rows. So it should check the whole table but every row for itself. I hope you know what I mean.

Thanks so far.
 
Oh, sorry. I just need it to mark it for me (red background or something). Only purpose is to find the (possible) duplicate in Excel, all editing on the data will take place in SPSS.
 
OK thanks. I also should have mentioned that my table has over 30 columns. I think the rule you programmed only checks the first cell of a row against every other cell in the row!? So that means if the duplicate is in the 7th and 14th cell of a row it doesn't find it, right?
 
Thanks again. But that's not what I mean. Maybe it get's clearer if I upload my data:

Again. I need to check every single row for itself (every row is one respondent). And not only to compare it with the first and second cell of each row.
 

Attachments

That's great, thanks. Much appreciated. But I don't really understand how it works. Do you maybe have a link where I can read into it? The central thing is this formular: "=ZÄHLENWENN($A1:$BN1;A1)>1" (it's german), isn't it?
 
Hi,

Welcome anytime.

TO start with, CF is nothing but formatting cell. You can format cells directly which will remain static. But if you want to format cell or a group of cells to change as per condition (e.g. duplicates value, top 5 value in a row or column in a month and many such condititonal data which will change.), you need to apply conditional formating.

Now some note:
1. CF works on a TRUE or FALSE, whether the condtion is TRUE or FALSE on applied cells.
2. CF is volatile i.e. it will change everytime there is a change in worksheet. As a result it may slow down your worksheet if you running a huge data.

Now coming to my formula: =COUNTIF($A1:$BN1,A1)>1

Step 1: Select all the cells where you want to apply CF.
Step 2: Insert the fomula in CF rule manager.

If you analyse the formula, it will check for the count of each item in every row between column A & BN. AND IF in the row count is greater than 1 (that mean there is a duplicate of that item) it will highlight it.

So Say row 1 has BMW-AUDI-MERCEDES-LV it will see that every item has a count of 1 so no duplicate it will send a FALSE for each CELL and no formatting take place.

But if you put BMW-AUDI-BMW-LV it will see that BMW count is more than 2 in this row for first BMW so a TRUE will come and cell will highlight and similarly for second BMW also.

I think this will help you a bit.

For getting a basic information on CF please visit below site.
http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/

You should also visit below site to see some cool application where CF is working:
http://chandoo.org/wp/tag/conditional-formatting/

Regards,
 
You must log in or register to reply here.
Back
Top