How to compare two lists for common values in Excel?

[@wayneedmondson1065]

Hi Chandoo.. great tutorial. CF, COUNTIFS and PQ all great ways to find the common values and as you said, each has its own best use situation. Thanks for the lesson and for sharing your vast knowledge of EXCEL which is much appreciated. Thumbs up!!

[@chadlee9251]

Awesome video!! Super helpful for my job. Needed to compare two separate lists that were over 700 rows long. Needed to find duplicate part numbers and order the ones that were not in the previous list. Awesome break down!!

[@AkaExcel]

Beautufull Solutions Chandoo!

[@heidijansson2050]

Hi Chandoo Thank you very much! This was very helpful to me😃

[@AnthonyGimme5]

Great Video. Sorted the Problem.

[@sakeenasammi1768]

Beautifully explained

[@ddp2049]

This was a great video. The only difference is that I have is that one of my columns I am comparing changes all the time. for example, the column name is 01/04/2024 Balance compared to 01/05/2024 Balance. how can I make the column dynamically so that when the date balance column changes it changes in the M code ?

[@pavanranga2362]

how can i get unmatched rows separately? and how can i add the data having different headings like Jul data from video?

[@julianoronha7056]

This is awesome - thank you so much!

[@chandoo_]

You're very welcome!

[@gitaromar]

Hello, Chandoo. Thank you for making my life easier yet again! I do have one scenario I'm hoping you can help with. Is there a way to compare two lists where some values may repeat but only count them up to the frequency with which they match? For example, one list has a value that repeats once, but the reference list may repeat that value 2 or 3 times. In that case, I would like to only count the first 2 matches. Thank you again

[@deepasingh6795]

Hey Chandoo , how to filter out common value from two different excel sheet in same workbook?

[@enniojohndiniz5816]

Hi Chando. How can I compare two lists to find approximate matches on SKU?

[@vijaychauhan5447]

Sir Your MS Excel knowledge is Very Excelent. Can you give me same knowledge MS Access?

[@tet1473]

@chandoo, could you please create some videos on Invoke functions?

[@chandoo_]

Sure. Watch out for the video.

[@GopiNath-fp5ly]

In simple data with more than one column can we use CONCAT function in the helper column and conditional formatting.

[@chandoo_]

Good one Gopi 👍 Please keep in mind that if columns have weird values (say one column has Gopi iPhone and other column is blank, in other table you have Gopi in one column and iPhone in next, CONCAT approach will match them.

[@AjayAnandXLnCAD]

👍

[@deepk82]

@Chandoo - I have opportunity numbers in both previous and current week data with values. Now, I want to know what all opportunitie's value changed, which one are rolled out or moved out ?

[@chandoo_]

Good question Deepak. I will research a bit more and reply

[@shoaibrehman9988]

It will be very useful in data analysis thanks

[@chandoo_]

You are welcome

[@legendinvest6531]

What do I do if there are duplicate values in one row alone but I don’t want them highlighted. Just the values that appear in both columns.

[@chandoo_]

You can use the same "conditional formatting" option and then change the rule from "unique" to "duplicate" in the rule setting.

[@legendinvest6531]

@@chandoo_ i don’t think you get what I’m saying. I don’t need values that appear multiple times in a column to be highlighted, just values that appear in 2 columns to be highlighted.

[@chandoo_]

In that case you need to use COUNTIFS() or something else. See this article. - chandoo.org/wp/compare-2-lists-in-excel/

[@txreal2]

PQ is great! Unfortunately, I can't figure out why my PQ (at work) don't have Merge Queries as New in the Merge Queries drop-down. They use Office 2016 Pro Plus. Can somebody help? It would be a big time saver for me doing different joints. Just querying. Thanks.

[@chandoo_]

I think this is one of the new features they launched. If you want merge as new, simply duplicate an exiting query and then merge.

[@txreal2]

@@chandoo_ I"ll try that. Thanks! PS. I enjoyed your training on "50 Ways to Analyze Data"

[@PalashBiswasxl]

Nice Boss...

[@venkatasubramaniam3643]

I am getting one problem in comparing combination of different data type columns. How to compare using Power Query between 2 different data type columns ?

[@rautorama]

Champion!

[@FRANKWHITE1996]

Thanks