Showing only rows in one column if found in another

[excelguy2024]

Hello,

I have two columns (Column C and Column D) with around 1 million rows.

- Column C has many repeating URLs in it
- Column D has only unique URLs in it
- There are duplicate values when comparing Column C and Column D

I only want Column C to display the URLs that are found in Column D. Is it possible to remove the other URLs in Column C?

As an example in the attachment, I only want Column C to display these URLs:

https://www. duckduckgo.com/
https://www. yahoo.com/

Thanks!

 

[Joe4]

One million rows is an awful lot of data. A formula approach could be slow performing, based on the amount of data.

There is a really easy way to do this, if you have access to Microsoft Access.
You could simply create two tables/lists, one for each column.
Then do a matched query between the two lists, so it will only return the records from column C that have a match in column D.

I believe that you could also do something similar in Excel by using Power Query, which allows you to do these type of Relational Database operations in Excel.

 

[hagia_sofia]

@Joe4 is right. If you still wanna test the performace of your computer you could try:

=FILTER(C2:C40,COUNTIFS(D2:D5,C2:C40))

 

[excelguy2024]

@Joe4 is right. If you still wanna test the performace of your computer you could try:

=FILTER(C2:C40,COUNTIFS(D2:D5,C2:C40))

Thanks for the responses. Where would I insert the above formula?

 

[hagia_sofia]

E.g. in column E2? This formula will not "remove the other URLs in Column C" (i.e. the original data will stay intact), it will filter and list those that are a match to the data in column C.