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

A simple question about data filtering

V

virtualized

New Member
Hi Everyone


I want to filter out duplicate data from a single column in Excel worksheet.

The data is in the following form:


(6,1)

(7,1)

(9,1)

(1,6)


In this data (1,6) and (6,1) mean the same thing. So i want to filter out one of them and the resultant data in the column should only contain 3 values:


(6,1)

(7,1)

(9,1)


I tried to use the "Advanced Filter" but did not succeed.


Any help in this regard is highly appreciated.


Best Regards
 
Two helper columns needed. The first, we'll use to compare both sets of numbers:

=MAX(MID(A2,2,1)&MID(A2,4,1),MID(A2,4,1)&MID(A2,2,1))


Assuming the previous formula is in col B, the next helper formula is:

=IF(COUNTIF($B$2:B2,B2)>1,"Duplicate","")


Note that I had to make some (broad) assumptions on your data for the first column, namely that all the numbers are a single digit. If they're not, you'll need to manipulate the text to stip out the correct section, but the general principle remains the same.
 
Dear "Luke M"


Thank you for your earlier reply. I tried your solution but it did not work, maybe because of the following reason:

The data is in the following form:


(6,1) 74

(7,1) 25

(9,1) 35

(1,6) 74


The value (6,1) 74 is in cell A2 and so on. Now the values in Cells A2 and A5 are the same with only difference that in one case the value in bracket is (6,1) while the other is (1,6).

Can you please suggest some modifications so that in the end i get values only in the first 3 cells like below:


(6,1) 74

(7,1) 25

(9,1) 35


Thanking you once again for your time.


Best Regards
 
Dear "Key Master" & "Luke M"


Thank you for your earlier replies.


Can you please guide me what change should be made if the data is of more than one digit (two or max 3, the maximum digit in my data is "100"). E.g.


(6,10)

(10,6)


Your solution works perfectly fine if the data is of single digits e.g. (6,1) & (1,6). But if the number of digits exceed 1 i.e 2 (e.g. 10) or 3 (e.g. 100) then there are problems.


Thank you once again for your time and help.


Best Regards
 
Sure. It's a bit longer, but works on same principle. Helper col #1 formula is:

=MAX(MID(A2,2,FIND(",",A2)-2)&LEFT(MID(A2,FIND(",",A2)+1,999),FIND(")",MID(A2,FIND(",",A2)+1,999))-1),

LEFT(MID(A2,FIND(",",A2)+1,999),FIND(")",MID(A2,FIND(",",A2)+1,999))-1)&MID(A2,2,FIND(",",A2)-2))
 
Dear Luke M


Thank you once again. I typed in the new formula but MS Excel showed an error and i applied the auto correction suggested by MS Excel.

But, still the formula only works for single digit numbers (6,1) & (1,6).

But if the number of digits exceed 1 i.e 2 (e.g. 10) or 3 (e.g. 100) then there are problems.

Just to emphasize again, the maximum number in my data is 100. So the numbers are either 1 or 2 or 3 digits long.


Best Regards
 
Virtualized,


The formula is working just fine on my end...even with larger numbers. I'm working with the premise that your data looks like any of the below:


(6,10) some text

(10,5)

(100,100) some text

(10,6)


The formula, copied directly, should not have generated an error, so I'm not sure what the auto-correction did. Can you eplain further what the problems are? Also, anytime you have a formula that gives an error, it helps if you post it so we can see what you've tried/what went wrong.
 
Dear Luke M


Below is the error which i get when i paste the formula in the Excel worksheet:


Microsoft Office found an error in the formula you entered. Do you want to accept the correction proposed below?

=MAX(MID(A2,2,FIND(",",A2)-2)&LEFT(MID(A2,FIND(",",A2)+1,999),FIND(")",MID(A2,FIND(",",A2)+1,999))-1))


. To accept the correction, click Yes.

. To close this message and correct the formula yourself, click No.


Is it possible that i send you an Excel file containing Data in about 8 to 10 cells in order to explain the problem clearly? I don't know whether it can be uploaded here on this Forum or not.


Best Regards
 
Virtualized

It looks like you have only copied the first line of Lukes formula

Lukes formula is

[pre]
Code: 
=MAX(MID(A2,2,FIND(",",A2)-2)&LEFT(MID(A2,FIND(",",A2)+1,999),FIND(")",MID(A2,FIND(",",A2)+1,999))-1),
LEFT(MID(A2,FIND(",",A2)+1,999),FIND(")",MID(A2,FIND(",",A2)+1,999))-1)&MID(A2,2,FIND(",",A2)-2))
[/pre]
 
You must log in or register to reply here.
Back
Top