=IF(COUNTIF('PRDA Data'!S$2:'PRDA Data'!S2,'PRDA Data'!S2)=1,'PRDA Data'!S2,"")

This formula is used to eliminate the duplicate from another sheet. Now the issue is Duplicates are removed from the above formula. But when I drag wherever the duplicates are found this gives a blank row. How to eliminate the blank rows and make this as a continuous row update.

Image with the issue i am facing:

Here is my Data how it looks Sheet 1

Final Sheet

I have added 2 images. Sheet 1 has duplicate data and the final sheet needs to have unique value data.

Updated with a video

And this is formula i am using

=IFERROR(INDEX('PRD Data'!S2:'PRD Data'!$S$60,MIN(IF(IF(ISERROR(MATCH('PRD Data'!S2:'PRD Data'!$S$60,$K$4:K4,0)),'PRD Data'!S2:'PRD Data'!$S$60,9^9)=9^9,9^9,ROW('PRD Data'!S2:'PRD Data'!$S$60)-ROW('PRD Data'!$S2)))),"")

Please check this Link for the video

  • Welcome to SuperUser! It's not clear from this question what you are trying to do. Please describe clearly what you want to accomplish with this formula. Give us details about what your data is and what you want it to be at the end. If you just want to remove duplicates from your data, try using built-in tools like this: support.office.com/en-ie/article/… – HackSlash Jan 10 at 20:49
  • I have edited the question back, please check and let me know how can we achieve this. – Arvind Jan 10 at 21:03

Use the Excel feature "Remove Duplicates"

  • To filter for unique values, click Data > Sort & Filter > Advanced


  • To remove duplicate values, click Data > Data Tools > Remove Duplicates

SOURCE: https://support.office.com/en-ie/article/filter-for-unique-values-or-remove-duplicate-values-ccf664b0-81d6-449b-bbe1-8daaec1e83c2

  • I have tried this. as my sheet 1 is a formula field and when i try this it gives an error. And moreover i have suggested the same to my team with this link, But they need a formula which will be invoked through integration later. – Arvind Jan 10 at 21:16

Excel's built-in feature of remove duplicates is good and easy as already suggested above.

However based on what best I understand your question, I suggest a formula based solution. In this example the input data (with duplicates) is in Cells C2:C16. The Unique data is available in E2 and below.

In E2 put the following formula.


We just want to start with very first number as it is.

Now in E3 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula shall now be enclosed in curly braces to indicate that it's an array formula.


Now drag it down upto E16.

This column E shall now reflect only the unique values from the Column B

Note the following

  • Leave at least one row free above the data. This is where last part of the formula ROW($C2) comes into picture. This should be the row immediate above the start row.
  • This formula is not very flexible or that much scalable. If you have input data from C2:C16 then in INDEX and MATCH it should correctly reflect C3:C16. If you remove a few items from the input column and then do not update this reference in the formula, then the last row in output may give an unwanted 0.
  • As a restriction for the formula to work correctly, align on the column rows. Do not refer to lesser or excess rows in the formula.
  • There could be a further enhancement using OFFSET to fix this but it may be a bit more complicated.

    enter image description here

  • Patkim, Can we do this with out dragging – Arvind Jan 11 at 10:48
  • I am not too sure what your perspective is when you want to do it without dragging. You have to drag the formula only once to apply it correctly to all applicable cells. You do not do this every time, as long as your input cell range is fixed. It’s a single formula that is referenced across cells correctly mapping an applicable range, which keeps on incrementing as you go down the cells. In above screenshot, the formula was already applied to all cells. I have just shown dragging as one applicable step in the process. – patkim Jan 11 at 11:05
  • Please check even after dragging i get the blank space :( Can you help me with that. – Arvind Jan 11 at 12:26
  • If I am going wrong somewhere please help me !! it means a lot to me . – Arvind Jan 11 at 13:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.