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

remove duplicate on column and row

trprasad78

Member
Hi Experts
How to remove duplicate entry sample record given below.
as per below data after do remove duplicate final out put should be only 2 number (444 and 33333333)

which option help us to do ?

79246
 

Peter Bartholomew

Well-Known Member
It all depends how far back into the history of Excel versions that you wish to go. Since I use 365 insider beta
Code:
= LET(
  data, TOCOL(dataEntry,1),
  UNIQUE(data)
  )
appears purpose made for the task.
 

p45cal

Well-Known Member
For O365 but not insider:
Code:
=LET(a,C4:H7,b,SEQUENCE(ROWS(a)*COLUMNS(a))-1,c,INDEX(a,INT((b)/6)+1,MOD(b,6)+1),UNIQUE(FILTER(c,NOT(ISBLANK(c)))))
 

p45cal

Well-Known Member
In the attached, the formula in the previous message on Sheet1 cell J4.
Also 3 more possibilities;:
  • a Power Query table that needs a named range myRng to work on and needs refreshing if the source data changes.
  • a user defined function (udf) uniques (needs macro/vba)
  • 2 lambda solutions
 

Attachments

Top