MS Excel help - Finding and listing Duplicates

OzyOly

OzyOly

OzyOly

Soldato
Joined
5 Feb 2006
Posts
3,386
Location
15,000ft.
Hey there,

this is kinda embarrassing but I barely use Excel and I'm stuck.

I'm trying to find the collision in a hash function so I have two columns, A and B, each with 15k+ rows, strings in A, integers in B.

What I want to do it extract a list of AB whose members are those who have matching integers (B's).

I've used conditional formatting to highlight duplicates but copying and pasting each entry by moving through 15k strings is taking a looooong time. Can anyone help?
 
What do you need from this? A total number of the matching strings and integers or do you need each one to be individually listed?

If it's the total number then pivot tables might do what you want.
 
What I want is a list of strings and integer pairs where the integers match.

For example,

and 123456
be 123456
hello 12344
world 12344
yo 12344

if you see what I mean? Basically the list of integers that are duplicated with their matching strings?
 
So just to check that I'm getting this, you'd want to see how many different strings match a set integer e.g. for 123456 there are two potential strings ("and" + "be")?

If that's all then a pivot table should do what you need, you'd just have to ensure there was a label to both columns and put the integer and strings in the rows box with the strings also in the values box.
 
semi-pro waster said:
So just to check that I'm getting this, you'd want to see how many different strings match a set integer e.g. for 123456 there are two potential strings ("and" + "be")?

If that's all then a pivot table should do what you need, you'd just have to ensure there was a label to both columns and put the integer and strings in the rows box with the strings also in the values box.
Click to expand...

VLOOKUP
 
semi-pro waster said:
Vlookup will just tell you if it appears in the array at all though, I'm not sure if that's what OzyOly wants. It can be a great formula to use but I'm always tempted by the simplest possible option that does the necessary where Excel is concerned.[/QUOTEU

VLOOKUP can be used to bring back a value in a particular column. A combination of VLOOKUPS and IF() statements should be easy enough.

Will take no time to scroll down to 15k either.
Click to expand...
 
Thank you for all your help guys. In the end I had to use a python script to work it out for me as excel kept freezing.
 
You must log in or register to reply here.
Back
Top Bottom