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

T

trentonirons

New Member
Joined
Mar 27, 2017
Messages
17
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.:confused:
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

This seems like a lot of work. Would it make sense to just use the "Remove Duplicates" tool?

Be sure your data set in the Data tab is formatted as an actual table (select entire range, Insert, table). Then click anywhere in the list, open the Data tab on the ribbon (i.e., Home ... Insert ... Page Layout ... ... Data) and click "Remove Duplicates."

If you really need it in a second tab, while leaving the original list as it is, copy and paste the list to the second tab (Paste-Special-Values) if it was the result of formulas) and repeat the above on the list copy in tab 2.
 
Last edited:
Upvote 0
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

What do you mean exactly by "this is autofiilled down.."? Are you using a macro to insert the formulas?

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

ErikTyler - No, unfortunately not. This is a dynamic table that is based off of selections from a dropdown. The dropdown is setting a value in another tab which is also dynamic. The formula I am trying to create should list unique values from another column unless the reference cell is blank, in which case, the cell with this formula should also be blank.
 
Upvote 0
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

Marcelo - No, I just used the 'series' feature to pull this formula down to the exact number of rows that there are unique values (i.e I know there are 1393 unique values, the formula starts on Row 8, so I pulled this formula down from Row 8 to Row 1400). Would a macro be better? Can I e-mail you the spreadsheet so that you have better context of what I'm talking about?
 
Upvote 0
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

It always helps greatly to see what you're looking at. Can you post some visuals for us?
 
Upvote 0
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

How? I don't see the option to attach the file or a picture.
 
Upvote 0
Re: Formula to list unique values in another tab giving me a list of duplicates. Please help!!

I have to leave in 5 min, but just to check the unique count try this array formula
=SUM(IF(FREQUENCY(MATCH(List1,List1,0),ROW(List1)-MIN(ROW(List1))+1),1))
Ctrl+Shift+Enter

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

Nevermind, figured it out. Below is a screenshot of the source data that the problem formula should be pulling values from. Values have been blacked out to protect sensitive information:

-- removed inline image ---


Below is a screenshot of the formula working until it goes wrong. Cell A916 is where things start crashing. 1333 was referenced in Cell A8. This continues down to the bottom of where I pasted the formula:


-- removed inline image ---


Does this help? Would other screenshots be necessary?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Populating multiple sheet names using Index, Match and Indirect
Replies
7
Views
154
Fluff
Fluff
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
439
Peter_SSs
Peter_SSs
C
  • Question
Index/Match | Index/Small Formula
Replies
5
Views
338
Eric W
Eric W
P
  • Solved
Unique Countifs
Replies
3
Views
192
Fluff
Fluff
F
  • Question
Want to return a unique list of values from a column that contains an array formula
2
Replies
14
Views
611
Fluff
Fluff

Forum statistics

Threads
1,214,915
Messages
6,122,212
Members
449,074
Latest member
cancansova

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top