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

formula ?

B

bee123

New Member
I have values like 7,4,3,4,7 in a given column.

How to avoid duplicates from this?

It has to show only 7,4,3.


Please help.


thanks in advance
 
Assume the data (7,4,3,4,7) is in cells A1:A5 -- you can put this formula in cell B1: =IF(COUNTIF(A$1:A1,A1)=1,A1,"") and drag it down to cell B5.


Hope this helps.
 
If you don't want blanks, and don't need the list to be sorted, then in B2:

=INDEX(A:A,MIN(IF(ISNA(MATCH(A$2:A$10,B$2:B2,0)),ROW(A$2:A$10))))


Confirm formula using Ctrl+Shift+Enter.
 
Then you'll need to first split them out using Text-to-Columns. You can then either do a Copy, Paste Special - Transpose to get them into multiple rows, or you can change my formula to:

=INDEX(1:1,MIN(IF(ISNA(MATCH($A1:$K1,$A2:A2,0)),COLUMN($A1:$K1))))

You would put the formula in B2 and then copy to the right as needed.
 
The problem is I cant split it into different rows.


A row contains 7,4,2,3,4,7.


Any chance to get 7,4,2,3 without splitting and put into different rows.?
 
Bee123,

Your first post stated that the values were in a single column. If they are also in a single row, I have to assume that they are just in a single cell. If this is the case, we'll need some way of parsing the data out into the individual components.


OR


The first post was in error, and the data is in multiple columns but in a single row. If so, the formula I posted above should work.


OR


Are you wanting to go from a single cell which contains duplicates to a single cell which lists all the numbers w/o duplicates? If so, is using an UDF (visual basic) an option?


----

Please let us know which is the correct interpretation of your data.
 
Will this function work for you? Install the following UDF

[pre]
Code: 
Function RemoveDupeChar(s As String, Optional d As String = ",") As String
'Removes duplicate characters from a string that is delimited
'2nd arguement can either be provided or will be a comma by default

Dim N As Variant
Dim NewString As String
N = Split(s, d)

NewString = d
'cycle through all the parts
For i = 0 To UBound(N)
'Check to see if word/character is already in list
If Not NewString Like "*" & d & N(i) & d & "*" Then
NewString = NewString & d & N(i)
End If
Next
RemoveDupeChar = Mid(NewString, 2 * Len(d) + 1, Len(s))
End Function
[/pre]
and then in your workbook, the formula is:

=RemoveDupeChar(A1)
 
Good day bee123 if you upload the work book you see Luke M's code does what it says on the tin, it works


https://dl.dropbox.com/u/75495784/bee123.xlsm
 
At first thought I would say no..................but now I have said that some one will prove me wrong
 
Good evening SirJB7

I did download at look at your workbook, and after much scratching of head and searching of data I had no idea what your data in column A was doing, where it came from or what it was to do...............but then I realized the Carlsberg had not kicked in. :)
 
@b(ut)ob(ut)hc

Hi, old dog!

Good evening for you too.

I misread the topic as I didn't notice it was just a single cell and I got that the values were in a column, so I played -never better used this verb- a little with CF. Then I re-read it, so I edited my comment.

Regards!

PS: Lack of Carlsberg, that's the point!
 
I think Luke M has done as you asked it takes second to instal the copied code and enter the formula, if there are no more responses it could be due the the fact that Lukes cosde if the answer and there is no CF way to to it
 
Hi ,


Please note that 4shared is a sign-on service , whereas 2shared is a free service ; if you can upload your file to 2shared or speedy.sh , I can try and help.


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