My feed

to access all these features


Excel help - anyone any good with finding matches?

13 replies

Happyotamus · 04/10/2020 14:30

I have a list of 4000 numbers, and a list of 600 numbers, some of which are duplicated in the 4000 numbers- how do I automatically highlight all the duplicates please?

I did a while back with text and it worked seamlessly but today it's being a twit so I guess it is actually me being stupid and getting the format wrong.

I am not looking for the find and replace function - it's a specific formula I need.

OP posts:
milienhaus · 04/10/2020 14:33

Use the MATCH function which will output the number where it’s found in the big list if there is a match or #N/A if not. If you’d prefer to output true or false for match or not use ISNUMBER(MATCH).

TorkTorkBam · 04/10/2020 14:35

If you will then get more elaborate VLOOKUP is useful.

MumUndone · 04/10/2020 14:35

I would probably do a v look up between the two sheets and see which numbers come across so are the duplicates

Happyotamus · 04/10/2020 14:36

Thank you - with that one can I input the entire range of 600 or do I need to go 1 by 1?

Very, very grateful for your help!

OP posts:
TorkTorkBam · 04/10/2020 14:36

The right function depends on what you intend to do after you have found a match.

Teensandfuture · 04/10/2020 14:36

Or do a vlook up function of 600 in 4000, not duplicate will appear as n/a

Teensandfuture · 04/10/2020 14:38

You just drag formula down in your 4000 list, that's all, then filter out n/as, untick them and you have all your duplicates on show

2020hello · 04/10/2020 14:39

Not sure of the list but can't you select all then remove duplicates..

TorkTorkBam · 04/10/2020 14:39
Mercedes519 · 04/10/2020 14:39

Put the 600 in one column, the 4000 in another. Put a vlookup against the 4000 looking up the 600 numbers. If it’s there the number will appear. If it doesn’t then n/a will appear.

Alternatively add the 600 to the 4000 but colour them to highlight them. Then use conditional formatting to highlight the duplicates sorting by the numbers.

mygrandadsvest · 04/10/2020 14:40

You can highlight duplicate values easily in conditional formatting without using formulae

Happyotamus · 04/10/2020 14:40

Ok so if there's a match, I need to copy the rows that match is on for all matches as the other columns in those rows contain data that we need.

(Thank you so much!)

OP posts:
Ginfordinner · 04/10/2020 14:40
  1. Sort them into order
  2. Highlight the column with duplicate numbers
  3. Select Conditional Formatting in the the Home tab
  4. Select Highlight cell rules
  5. Select Duplicate Values

    Anything duplicated will be highlighted in red
Please create an account

To comment on this thread you need to create a Mumsnet account.