Macros can do almost anything, but why just Macro?
Power Query can do it much easier than coding, see an example attached.

Hi Catalin,

You are amazing! 

Wow! This is awesome and it works like a charm. Thank you so much!!!

Yes it is working correctly without any issue. I just have below question, please can you check.

Some sources show different Country for same company so I extract data from DB using SQL by below structure

The result using Power Query as below.

So I changed the IDs to make each row has it is own ID and I got good result as I want the Trusted Data per row then I will get  DISTINCT for "Company & Country" then I will take Country which have the big count of sources as below.  Do you think is there any issue will happen if I do this?

How kind you are to help me.

Thanks;

Marsil

Indeed, if the ID's are not unique there will be an issue, there is a grouping step that will group all rows with the same ID.
If you make the ID's unique, there will be no problem

Hi Catalin,

Great so I will make the ID's unique.

Last question please.

I will use this file for other types of data like I will compare Found Year, Phone, ....etc.

So I face as issue to determine the count of sources which I past it in "M column" as I use this function =COUNTIFS(R3:AD3,"*",R3:AD3,"<> ")

as it is count the characters not numbers, so can you help me to find other function work with characters and numbers please.

Or If there option on Power Query to count the sources and fill M Column ?

Thanks for helping me accomplish my goal.

Thanks;

Marsil

Between Added Custom 1 and Added Custom2 steps, add a new column with this formula:

=List.Sum([Custom][Count])

The full step should look like:
= Table.AddColumn(#"Added Custom1", "Total Count", each List.Sum([Custom][Count]))

That's a huge help - thanks!

I added above formula and it is running without any issue.

I did other thing and I want to know your comment please.

I removed Changed Type step due sometimes I will want to remove column from sources or change it's names as the sources not fixed as may be I got from other sources like Amazon, yahoo finance, ...etc.

Also may be I will add more columns for sources.

I tested the result and it's okay, but I want your help to review this update in attached file to make sure there is no issue on any step in code as it is first time to make edit in Power Query.

Thanks is not enough, but really don't know what else say to you. Anyway have to say thanks again.

Thanks;

Marsil

You're welcome.

If works, there is nothing to review.

Code is already using Unpivot Other Columns in order to handle any new possible columns, if the source has ZID and Company.

The only issue can come from source data, if it contains columns other than HQ columns, those will be counted as HQ.

I think above issue won't happen as I removed Changed Type step to make the file don't depend on columns names.

Example I changed the columns names as below and it is working without any issue.

Are u intent this or other thing? as I will depend on this file on urgent project.

Thanks;

Marsil

I didn't say that you will have different HQ column names, what I said is that you should not have other column than HQ columns, regardless of the HQ columns names.

For example, if besides HQ column you have another new column like "Company Manager", or "Count of Floating Ducks", the query will not return the correct counts.

Ofcourse, when I work on this, I will work per feature foe example after I finish HQ and get the final result, I will start with another features like Phone.

I won't work in different values on the same time.

below table will cause issue.

But below one is correct

Thanks again. Couldn't have done it without you.

Tahnsk;

Marsil

Ok, what needs to be done with the tables you mentioned? Will have the same Trusted Data process with same counts?

Or there is a different process for emails?

First,  I wanted to send my thanks for all your assistance.

Here all details about what I will do, please check.

I build profile for many companies contain many features like Company Headquarter, Phone, Address, Founded Year, Stock Exchange Name, Email, EmployeesCount, Industry, FiscalYear, Logo and MarketCap

And I do this via extract these data from many sources like The Wall street, Blomberg, …etc. and will verify the accuracy by comparing the data which I extracted it.

So the file which you sent me will help on comparison to get the Trusted Data, I think all of features can use the same Trusted Data process.

But I think there are two feature won’t be done with Trusted Data process, please check below.

• As not all sources write the address with same format.

Ex: CompanyName: News Corp

The Wall street_Address: 1211 Avenue of the Americas New York New York 10036 United States

Alacrastore_Addres: 1211 Avenue of the Americas New York, NY 10036 United States

Marketscreener_Address: 1211 Avenue of the Americas New York, NY 10036

Owler_Address: 1211 Avenue of the Americas New York, New York10036

• Logo, I got the image URLs and I downloaded it.

Please can you help how I can do this as I try to get any idea.

Thank you very much; your support is greatly appreciated.

Thanks;

Marsil

With so unpredictable address variations, it's hard to count them properly with a decent precision unfortunately.

You might be able to do something by using fuzzy matching, but it's not very reliable.

In attached example, I had to reduce the similarity threshold to 60%, that can bring wrong results.