Exact match on a row in two columns.

[Gavin Brown]

Hi all, I am trying to find if the data is the same on any row in the sheet within two column.
example: I have a song list with the artist name in column b1 and the song title in c1. There could be duplicates of this somewhere else in the sheet and I wish to remove them.
with over 30,000 rows this would take a long time to do manually.
is there a formula or something built in that I can use?

thank you in advance for any assistanc.

 

[etaf]

there is also a duplicates function in later version of excel , which will remove duplicates
What version are you using

DATA> Remove duplicates

you could use a concatenate and then a count()
I have done this a few times to flag duplicates and then filter - BUT not actually get the software to remove , until have visually checked
Only because with music i have found same artist and title but the length was different and quality

anyway
I would start a helper column and put &
SO
B1&C1
and just copy down - if you put next to an existing column - small box in right corner of box - double click and it will copy down
then
assuming helper is in D
countif( $D$1:$D$30000, D1) >1
you will not get true false

 

[felixstraube]

Select both columns and go to Data/Data Tools/Remove Duplicates.

Or select the columns you want to remove duplicates in the dialog that pops up

 

[Gavin Brown]

Select both columns and go to Data/Data Tools/Remove Duplicates.

Or select the columns you want to remove duplicates in the dialog that pops up

Thanks but that does not work. I used column B & C.
As you can see from my screen shots I start with certain numbers as a test. after running remove duplicates B4 & C4, B8 & C8 should be removed. However it has removed B7 & C7, B8 & C8 as they both have 2 in B and 3 in C.
So as explained, I would need to remove any that only matched in B & C in the same row of the column.

 

[etaf]

no screen shot
also - did you try my suggestion ?

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[felixstraube]

Before using "Remove duplicates" add filters to you data (you will have to add headers to your column first) Then try again. It should work as expected.
It also will work if you convert your data into a table first and then remove the duplicates.
Make sure that you select only columns B and C in the dialog window that pops up after you clic "Remove duplicates".
You'll see it works just as you need.

 

[Gavin Brown]

Before using "Remove duplicates" add filters to you data (you will have to add headers to your column first) Then try again. It should work as expected.
It also will work if you convert your data into a table first and then remove the duplicates.
Make sure that you select only columns B and C in the dialog window that pops up after you clic "Remove duplicates".
You'll see it works just as you need.

Thank you. The header on the first row worked.

 

[Peter_SSs]

. The header on the first row worked.

That's fine if you want the headers but you shouldn't need to do that if you don't want. Without the headers, when you invoke the Remove Duplicates dialog, make sure the 'My data has headers' box is unchecked

After I click 'OK', this is what I get

23 12 07.xlsm
	A	B	C
1	1	2	3
2	1	2	4
3	1	2	5
4	4	1	3
5	2	6	4
6	9	9	2
7	2
8	8
Remove Dupes (5)

This fits with your requirement ..

after running remove duplicates B4 & C4, B8 & C8 should be removed.

BTW, please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’.)

 

[felixstraube]

After testing again...
You should NOT select only both columns as said before. By selecting one cell in any place in you dataset and clicking "Remove duplicates" an then only selecting the column you want (B and C), it should work as needed.

Or you can select the whole data set before clicking "Remove duplicates".
The only thing that wont work is if you select both columns first. Sorry about that.