Excel question - identifying records in data sets
Discussion
Well this makes for a boring first post!
if you get them all in one list, highlight the column then go to Data > Remove duplicates that should do the trick.
ETA;
I think I've read that wrong, as above if you Vlookup against the list then filter by N/A's that should be your 20
if you get them all in one list, highlight the column then go to Data > Remove duplicates that should do the trick.
ETA;
I think I've read that wrong, as above if you Vlookup against the list then filter by N/A's that should be your 20
Edited by Ellb123 on Tuesday 13th February 09:19
=match($A1,$B$1:$B$80,0) it looks for an exact match of what's in the reference cell A, in column B, and returns its location, or an error if not present.
column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
number2 said:
=match($A1,$B$1:$B$80,0) it looks for an exact match of what's in the reference cell A, in column B, and returns its location, or an error if not present.
column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
Ok, 100 in column A, 80 in column B and copy equation into cell c and copy down. column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
Excel didn't like the formula for some reason, says there's a problemo with it
akirk said:
Yes thanks, this did it. Copied =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate") into column C and that identified duplicates. It'd be better if removed dupes too but this is still ok.Frimley111R said:
Yes thanks, this did it. Copied =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate") into column C and that identified duplicates. It'd be better if removed dupes too but this is still ok.
If there are duplications within a column as well:there is a built in excel de-duplication option:
https://support.office.com/en-us/article/filter-fo...
it simply doesn't do it across two separate columns, data needs to all be in one column...
so either de-dup. each column first, or de-dup using the above method and then excel to de-dup the final results
if the duplication is only between the columns, then the above formula works to identify the duplicates (which excel will not do) and then you can use excel's filtering or sorting etc. to easily remove the ones you don't want...
Frimley111R said:
number2 said:
=match($A1,$B$1:$B$80,0) it looks for an exact match of what's in the reference cell A, in column B, and returns its location, or an error if not present.
column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
Ok, 100 in column A, 80 in column B and copy equation into cell c and copy down. column A1:A100 has your 100 rows in, column B1:B80 has the 80 rows, and column C1:C100 has the formula above. Type it in C1 and copy it down to C100. Records that are not in the 80, will have an error in the cell. [you don't have to use columns A to C, just a guide]
Excel didn't like the formula for some reason, says there's a problemo with it
would a COUNTIF not be the most simple way of doing this other than copying and pasting the data into 1 column?
If the records are in multiple columns then CONCATENATE them all into 1 final column on both sheets then do a COUNTIF using the concatenated column as the benchmark and the other work sheet's concatenated column as the range.
If the records are in multiple columns then CONCATENATE them all into 1 final column on both sheets then do a COUNTIF using the concatenated column as the benchmark and the other work sheet's concatenated column as the range.
Gassing Station | Business | Top of Page | What's New | My Stuff