I have five worksheets with names: "2017", "2016", "2015", 2014", "2013". In every worksheets i have table with column name: "Company name" and other data. I want to find and select names of those companies that are present (repeated ) in every of five years. Is there a formula that can do this job???
Answers (1)
Assuming the structure is the same (for example company name in A).
Add another sheet, put 2013 - 2017 in cells B1:F1.
In A2:A(n), merge all company names uniquely (remove dupes).
In B2:F(n), this is the formula (for cell B2):
=iferror(vlookup($A2,INDIRECT(B$1 & "!" & "$A$1:$A$10000"),1,false),0)
Don't have excel so your mileage may vary.
For repeated & complex tasks, you should consider learning python pandas.
Thank you. It works. ;)