Formula for finding unique data for lists

**alcorp** · 07-16-2014, 02:53 PM

hi,

so I am basically trying to find the differences for two (large) lists and I can't figure out the formula. I am trying to get unique values for both lists (comparing them left to right and right to left ) and just have the results of the unique values in another column.
I tried using this formula =IF(ISNA(VLOOKUP(B:B,$B$2:$B$778113,1,FALSE)),A2) but I am just getting an out value of FALSE. I tried messing with it and I just can't find info on it.

Does anyone know how I should have it or where I can find info on it? Thanks.

**prabhuduraraj09** · 07-16-2014, 03:03 PM

Hi alcorp,

could you please give me sample input, not the whole data but just 2 or 3 input for me to give a try.

**Pepe Le Mokko** · 07-16-2014, 03:14 PM

Unique or distinct values?

**Gottoplay** · 07-16-2014, 03:21 PM

I sometimes use COUNTIF() for this. You set it to search a value from LIST1 in LIST2. If it finds a match you get a value other then zero.

**sktneer** · 07-16-2014, 03:38 PM

Please find the attached sheet to see if this is what you are trying to achieve.

**alcorp** · 07-16-2014, 03:41 PM

Book2.xlsx

Here is a basic file of what I am trying to do.

**alcorp** · 07-16-2014, 03:42 PM

oh that works really good too but i need it in a combined list

**sktneer** · 07-16-2014, 04:08 PM

Try this Array Formula in E2. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after pasting the formula in the formula cell, hold down the Ctrl+Shift and then press Enter)

In E2

Please Login or Register  to view this content.

and copy down until you get blank cells.

**alcorp** · 07-16-2014, 04:50 PM

I tired using this and it was only one way. I need to have it where if LIST A has a 10 and LIST BE doesn't have it then it will show unique value of 10
and same goes the other way around. If LIST B has a 12, and LIST A doesn't have the 12 then it should show up on the unique value list with the 12 so the list would be 10 and 12

**sktneer** · 07-17-2014, 04:42 AM

Try this Array Formula. (CSE confirmed)

Please Login or Register  to view this content.

**alcorp** · 07-17-2014, 01:07 PM

I tried using your formula but I am still having issues. I am comparing 778,112 rows and I don't know if that makes a difference. I was trying to break down the formula and I am confused on why there is the E column if I am just comparing column A and Column B and presenting the results in the C column.

**sktneer** · 07-17-2014, 01:12 PM

See the attached sheet in post#10. Where is the column E?

**alcorp** · 07-17-2014, 01:46 PM

Okay I tried using the formula and I am still having issues. In the formula it uses the E collumn but I am lost on that.

I uploaded a sample of the data I am using but the acutal one goes down to 778,112 rows which is too large to upload.

One of things I change when I use your formula is is changing the value 6(assuming that's where the end of checking is) to 778,112 and that's it. online example.xlsx
s

**newdoverman** · 07-17-2014, 04:52 PM

Here is the ARRAY formula (Enter with Ctrl + Shift + Enter) with the minor corrections for entry into column C

Formula:

Please Login or Register  to view this content.

**newdoverman** · 07-17-2014, 05:00 PM

I hope that you have some "serious" computing power because this is going to take a while to process with nearly 800,000 rows.

**sktneer** · 07-18-2014, 12:48 AM

There are two issues with the file you attached in the post#13.
1). The formula I suggested is an array formula so you need to confirm it with Ctrl+Shift+Enter not just Enter. (i.e. after pasting the formula in the cell, press F2, hold down the Ctrl+Shift and then press Enter.). When an array formula is entered correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets {}. And if you don't see the curly brackets in the formula bar, repeat the process to enter it correctly by pressing F2 (Function Key) and holding down the Ctrl+Shif together and pressing Enter then.

2). The numbers in column A are numbers stored as Text (not real numbers), while the numbers in col. B are real numbers. You need to take care of this. Here is the modified formula. Notice in the formula I have used $A$2:$A$30*1 rather than $A$2:$A$30 only, because multiplying $A$2:$A$30 by 1 makes $A$2:$A$30 the real numbers.

Please Login or Register  to view this content.

Is this what you are trying to achieve?

**newdoverman** · 07-21-2014, 03:49 PM

Just for "kicks" I added 2 helper columns and a 3rh column with a non-array formula to find the unique values. Maybe a solution like this could work for you. I don't know what the comparative speed of calculation is.

Formula for finding unique data for lists

LinkBack

Thread Tools

Rate This Thread

Display

Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Re: Formula for finding unique data for lists

Thread Information

Users Browsing this Thread

Similar Threads

Dynamic / Unique Data Validation Lists

Help with getting unique output dependent on two data validation lists

MultiLookup + Unique + Sorted Lists in a Drop-Down in the most faster formula

Formula needed for finding unique values

Finding unique names when comparing two lists

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions