Formula to list unique values in another tab giving me a list of duplicates.

[trentonirons]

Hi all,

I have a column in Tab 2 (Dashboard) that is supposed to return unique values from Tab 1(Data). Here is the formula that I'm using:

{=IFERROR(INDEX(List1,MATCH(0,COUNTIF($A$7:A7,List1), 0)),"")} - this is autofilled down from Tab 2 A8:A1400 -

'List1' resides in Tab 1 and is accurate, clean, and has no blanks or formatting issues. I know for a fact that in 'List1' there are 1393 unique values. I pasted the above formula in Tab 2 in Cell A8 down to cell A1400 to allow space for all the unique values. In cells A8:A453, I get unique values until cell A454:A1400, when I get the same value, '1333' which is just one of the unique values from the list. I have been troubleshooting this for two full days now and I am feeling like I am missing something simple. What mistakes could I be making? I'd be more than happy to e-mail the spreadsheet to anyone willing to help.

 

[inactiveuserps07]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

No image is showing, just a page-full of gobbledygook.

 

[Marcelo Branco]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

I appreciate it, Marcelo. However, I need it to return each unique value until there are no more, then return "". Not return the count of unique values.

I know, i know, the formula is just for testing purposes. Could you please tell us what the formula returns?

M.

 

[inactiveuserps07]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

And is List1 sorted so that all duplicates are grouped together (or can it be)?

 

[inactiveuserps07]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

@trentonirons, do List1 and List2 start in the same cell relative to their sheets (e.g., do they both start in A7)?

Are both lists formatted as tables (List1 and List2)?

And what is the column header name for each?

 

[inactiveuserps07]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

@trentirons, try this solution, which I designed to work no matter where your two lists reside on their respective sheets:

1. Make sure your two lists start in the same cell (e.g., A7) within their respective sheets.

2. Format them as tables, including headers and all data. Name the first one List1 and the second List2 (I believe you've already done part of this if not all; I'm just covering all the bases.)

3. For the sake of ease, for now, name (or rename) the column header of each list Column1. You can change it (or change it back) to something else after doing the next steps.

4. Paste this formula into the first position of List2, just below the header:

=IF(ROW()=ROW(List2),INDEX(List1[Column1],1),IF(OR(INDIRECT(ADDRESS(ROW()-1,COLUMN()))=INDEX(List1[Column1],ROWS(List1)),INDIRECT(ADDRESS(ROW()-1,COLUMN()))=""),"",INDEX(List1[Column1],SUMPRODUCT(MAX((List1[Column1]=INDIRECT(ADDRESS(ROW()-1,COLUMN())))*((ROW(List1[Column1]))-COUNTBLANK(INDIRECT("Data!"&ADDRESS(1,COLUMN(List1))&":"&ADDRESS(ROW(List1),COLUMN(List1))))))))))

5. Copy-drag down as far as you need or want. Everything after the last match will fill with null.

 

[B___P]

Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

Hi trentonirons,
give a look at this thread https://www.mrexcel.com/forum/excel-questions/1007089-kludge-fix-speed-section-code.html there are a few point to consider,
Cheers