# How to display value of circular reference formula

1. ## How to display value of circular reference formula

Hi guys,

I have an array formula in column B cells that removes duplicates from column A. Unfortunately, column B displays 0 instead of the real values because it's a circular reference formula.
I've tried changing the number of iterations and maximum change, this time I get my values but in an disorderly way compared to the values in column A.
I would like to remove duplicates from column A and keep them from smallest to biggest. What values should I use for number of iterations and maximum change?
Here attached is the workbook sample.

2. ## Re: How to display value of circular reference formula

Your sample does not include any formulas, so it is difficult to determine what circular logic you are using for this. Though, I'm not sure that I see that circular logic is really needed. If the circular logic works, then I don't see that "number of iterations" or "max change" will really impact the result of this calculation.

Is it necessary to use a formula for this? My inclination for this sort of thing is to use the Data-> Remove duplicates command, or Data->Advanced Filter command, or to use a pivot table (Insert-> Pivot table) to get a list of unique entries. I find these are easier to use on the rare occasion when I need to remove duplicates. Is there a reason why these kinds of built in solutions are not desirable?

3. ## Re: How to display value of circular reference formula

Sorry also I didn't realize the formula was badly copied, let me upload again the workbook.

Actually that workbook is only an extract of a bigger workbook which contains a lot of data that will be updated periodically, that's why I need a formula to automatically remove those duplicates without having to always remove duplicates manually using commands you mentioned above. I didn't want to upload the original workbook as I'm still working on it and haven't given it a clean look yet, it really looks ugly for now. But if you're interested in having a look at it to understand why I desperately need to use such a formula, I can upload it for you.

4. ## Re: How to display value of circular reference formula

Try

B2

Formula:
`Please Login or Register  to view this content.`

Drag down

5. ## Re: How to display value of circular reference formula

Thanks a lot Mr. Ankur. It works perfectly.

6. ## Re: How to display value of circular reference formula

Thanks for feedback and Rep. Please mark thread as solved if you have got you solution ...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1