Comparing Unsorted Lists Across Multiple Sheets

[farism]

Hello Excel Forum Gang,

First of all, this is my first time on the forum so hello everyone!

To my question: I have a database of Fortune 500 companies over a span of 10 years with each list in an individual sheet of its respective year. I am using the Stocks data function to extract the ticker symbols for each company. It is very often the case that a company appears in more than one Fortune 500 list and, as such, will use the same ticker for other years (given no changes to the company, of course). While it is useful, the function often fails to identify the ticker symbol if the name in the data does not match very closely to that which the Stock function is using. I have already manually identified all the tickers for a given year (2011) in what I call the 'anchor' sheet. The primary problem is that the function works independently for each sheet: if a ticker is identified or manually filled in on one sheet, this does not carry over to other sheets where that same company has made a recurring appearance.

To that end, I want to compare the column of company names with the same column in any given number of other sheets and identify companies which occur more than once. Once identified, I want to fill each missing ticker with its respective symbol from the anchor sheet, bypassing the need to manually add the symbol. I have attached a screenshot of the anchor sheet; the red marked company was a manual addition and this sort of sheet exists for years 2010 to 2020.

Please let me know if I need to clarify any further. Endlessly appreciate any help you can give me.

Thank you!

Fortune 500.PNG

[JohnTopley]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[farism]

Hi John,

Thank you for the clarification. I have attached a sample workbook below with two years of the Fortune 500 list. I manually filled the unidentified company tickers to demonstrate what I am trying to do: fill empty/unidentified tickers with the already identified ones for the matching company from another sheet.

Please let me know if anyone can offer some help. Thank you!

[alansidman]

Unable to see the whole picture as your links fail because you didn't load the other workbook that you are trying to compare to.

[farism]

Hi Alan,

Thanks for the reply. Sorry if I was not clear but I am only working from one Workbook. Within that Workbook are multiple tabs i.e 2010, 2011, that I am trying to compare.

Many thanks in advance!

[alansidman]

With your two sheets, what would your expected results look like? Mock them up manually as I don't believe we understand your needs clearly. Using your two sheets, explain in simple terms step by step what you are trying to achieve.

[alansidman]

Is this the type of result you are looking for?

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Rank	Company	Revenues ($ millions)	Profits ($ millions)	Ticker symbol	2010.Rank	2010.Company	2010.Revenues ($ millions)	2010.Profits ($ millions)	2010.Ticker symbol
2	1	WALMART INC. (XNYS:WMT)	421849	16389	WMT	1	Wal-Mart Stores	408214	14335	WMT
3	2	EXXON MOBIL CORPORATION (XNYS:XOM)	354674	30460	XOM	2	EXXON MOBIL CORPORATION (XNYS:XOM)	284650	19280	XOM
4	3	CHEVRON CORPORATION (XNYS:CVX)	196337	19024	CVX	3	CHEVRON CORPORATION (XNYS:CVX)	163527	10483	CVX
5	4	CONOCOPHILLIPS (XNYS:COP)	184966	11358	COP	6	CONOCOPHILLIPS (XNYS:COP)	139515	4858	COP
6	6	GENERAL ELECTRIC COMPANY (XNYS:GE)	151628	11644	GE	4	GENERAL ELECTRIC COMPANY (XNYS:GE)	156779	11025	GE
7	9	BANK OF AMERICA CORPORATION (XNYS:BAC)	134194	-2238	BAC	5	BANK OF AMERICA CORPORATION (XNYS:BAC)	150450	6276	BAC
8	7	BERKSHIRE HATHAWAY INC. (XNYS:BRK.A)	136185	12967	BRK.A	11	Berkshire Hathaway	112493	8055	BRK.A
9	12	AT&T INC. (XNYS:T)	124629	19864	T	7	AT&T	123018	12535	T
10	8	GENERAL MOTORS COMPANY (XNYS:GM)	135592	6172	GM	15	GENERAL MOTORS COMPANY (XNYS:GM)	104589	N.A.	GM
11	10	FORD MOTOR COMPANY (XNYS:F)	128954	6561	F	8	FORD MOTOR COMPANY (XNYS:F)	118308	2717	F
12	13	JPMORGAN CHASE & CO. (XNYS:JPM)	115475	17370	JPM	9	J.P. Morgan Chase & Co.	115632	11728	JPM
13	11	HP INC. (XNYS:HPQ)	126033	8761	HPQ	10	Hewlett-Packard	114552	7660	HPQ
14	14	CITIGROUP INC. (XNYS:C)	111055	10602	C	12	Citigroup	108785	-1606	C
15	16	VERIZON COMMUNICATIONS INC. (XNYS:VZ)	106565	2549	VZ	13	Verizon Communications	107808	3651	VZ
16	15	MCKESSON CORPORATION (XNYS:MCK)	108702	1263	MCK	14	MCKESSON CORPORATION (XNYS:MCK)	106632	823	MCK
17	19	CARDINAL HEALTH, INC. (XNYS:CAH)	98601.9	642.2	CAH	17	CARDINAL HEALTH, INC. (XNYS:CAH)	99612.9	1151.6	CAH
18	18	INTERNATIONAL BUSINESS MACHINES CORPORATION (XNYS:IBM)	99870	14833	IBM	20	INTERNATIONAL BUSINESS MACHINES CORPORATION (XNYS:IBM)	95758	13425	IBM
19	5	FEDERAL NATIONAL MORTGAGE ASSOCIATION (OTCM:FNMA)	153825	-14014	FNMA
20	17	AMERICAN INTERNATIONAL GROUP, INC. (XNYS:AIG PR A)	104417	7786	AIG PR A
21	20	FEDERAL HOME LOAN MORTGAGE CORP (OTCM:FMCC)	98368	-14025	FMCC

Sheet: Merge1

[farism]

The previous workbook is what the expected results look like. I have included another workbook here showing what the list looks like without my manual changes. As you can see, a number of cells for the ticker symbol read '#FIELD!' in the 2010 tab. Many of these companies are also present in the 2011 list.

What I am trying to achieve is comparing the two lists, identifying matching companies, and then filling in the '#FIELD!' cells with their respective ticker from the 2011 list.

[farism]

Yes! That is exactly what I am looking for. The order of the lists is not important so this is perfect.

Could you shed some light on the solution? Thank you very much

[alansidman]

I brought each sheet into Power Query/Get and Transform Data found on the Data Tab of the ribbon.

I then joined the two tables based upon a Left Outer Join.

The file with this action is attached.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.


Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[farism]

Thank you so much for the insight, Alan! Really appreciate the comprehensive solution and extra information. Will get right on reading more about Power Query - beginning to see just how useful of a functionality it is.

Again, thank you for all the help!

[alansidman]

@farism
If you can obtain a copy of "M is for (Data) Monkey" by Ken Puls and Miguel Escobar, then your progress will advance very quickly. An excellent primer on Power Query.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.