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

index ignoring duplicates and blank cells

Rodrigues

Member
All
I'm working on file attached and I'm struggled to make the formula work ignoring dulicates values and blank cells.
Please, could someone help me.
Thanks in advance and regards
R
 

Attachments

  • Book1.xlsx
    9.9 KB · Views: 4
Hi Narayan
Sorry, still showing duplicates, cells highlighted in yellow. I'm looking for similar on example on columns J,K,L.
If I could look to the entire columns it will be better, beause the file has hundreds of thousabds rows.
Thanks again
R
 

Attachments

  • Book1 (14).xlsx
    11 KB · Views: 1
Remove duplicates and ignore blank cells

In H3, copy across and down :

=IFERROR(INDEX($B$3:$B$296605,MATCH(1,MMULT(ISNA(MATCH($B$3:$B$296605,H$2:H2,0))*($B$3:$B$296605>0)*($A$3:$A$296605=H$2),1),0)),"")

Regards
Bosco
 

Attachments

  • RemoveDuplicatesAndIgnoreBlank.xlsx
    12 KB · Views: 5
Hi Narayan
Sorry, still showing duplicates, cells highlighted in yellow. I'm looking for similar on example on columns J,K,L.
If I could look to the entire columns it will be better, beause the file has hundreds of thousabds rows.
Thanks again
R
Hi ,

Sorry , my mistake ; I overlooked the remove duplicates part.

Narayan
 
All
Apologies to disturb you again with this.
The formula works fine, the only trouble I'm having is that, excel it's painfull slow to run it as I have 500000+ rows to calculate, I'm wondering if there is any other (faster) way to do it?
Thanks
R
 
All
Apologies to disturb you again with this.
The formula works fine, the only trouble I'm having is that, excel it's painfull slow to run it as I have 500000+ rows to calculate, I'm wondering if there is any other (faster) way to do it?
Thanks
R
1] The other faster way is using helper column.

2] See attached file and check should it can help.

Regards
Bosco
 

Attachments

  • RemoveDuplicatesAndIgnoreBlank(1).xlsx
    12.7 KB · Views: 5
Back
Top