Select Data from Multiple Sheets, Save on one - Remove Duplicates

**snuffnchess** · 02-27-2018, 03:01 PM

I have a workbook with about 50 tabs in it.. (Each tab is a date yyyy-mm)

What I am trying to do is take the data that is on each of those tabs, and combine it on to one worksheet, and remove the duplicate company names

Here is the catch. When the duplicate company names are removed, I also want to know what the most RECENT DATE is that the company appeared.

So if Company A is on Tab 2017-02 and on Tab 2017-08, for it to show "Company A", 2017-08.

If it is possible to add how MANY times a company appears across all of the sheets that would be great too.

**JeteMc** · 03-02-2018, 11:35 AM

I imagine that you are looking for a straight forward solution and I hope you get one. In the meantime perhaps this will help.
As modeled in the attached file on the master worksheet:
1) Populate a column for each monthly tab using the formula: =IF('2018-1'!A2="",NA(),'2018-1'!A2)
2) Populate a single list of companies using the formula:

Formula:

Please Login or Register  to view this content.

3) Populate a list of unique companies using the array entered formula*:

Formula:

Please Login or Register  to view this content.

4) Find the most recent date using the array entered formula*:

Formula:

Please Login or Register  to view this content.

5) Count the number of times a company appears using: =IF(A2="","",COUNTIFS(J$2:J$13,A2))
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.