Copy rows of data to another worksheet - only - if listed several times

[Kasperlindoe]

I’m creating a list of companies and some data for each quarter, like so (there are several more variables (as indicated below), but these are the only ones necessary for this little challenge):

Name Quarter Industry Variable X Variable Y
Company A Q1 Something A …. ….
Company B Q1 Something A … …
Company B Q1 Something B …
Company C Q2 Something B …
Company B Q2 Something A …
Company B Q2 Something B
Company A Q2 Something A
Company B Q3 Something A
Company B Q3 Something B
Company C Q3 Something B
Company B Q4 Something A
Company C Q4 Something B

I need Excel to automatically (or by running a macro) copy each full row of data to an adjacent worksheet – ONLY – when a company has been listed in a certain industry in four different quarters (the order is not important – could be Q3, Q4, Q1, Q2). As illustrated above, each entry is made randomly as the data comes in each quarter.

Here’s what the result would look like, taken from the list above:

Name Quarter Industry Variable X Variable Y
Company B Q1 Something A … …
Company B Q2 Something A … …
Company B Q3 Something A …
Company B Q4 Something A

The only company that has been added to the final list is Company B (with every complete row of data), since that’s the only company showing up with a specific industry (Something A) in at least 4 different quarters. Since Company B has been registered in industry “Something B” in only three quarters, those rows will not be added (unless a new entry is made with industry “Something B” in Q4). Company C will be added in the following quarter, since that’s when it has been registered in four quarters in the same industry (on the first list, it has been registered only in Q’s 2, 3 and 4).

The logic behind all this, is that I want to do some measurements and comparisons on companies that can be traced back at least four quarters, in the same industry – otherwise it would distort the result, since data from a new company that hasn't been registered in all quarters in the same industry would not be an image of the overall development, but merely an inflated result...

Can anyone help me with this tricky little challenge?

[ravishankar]

Hi
Assuming your data is in sheet1, paste the following codes in the macro window(Alt f11)

Please Login or Register  to view this content.

run the macro. it copies each row,where a company name appears 4 times.
ravi

[Kasperlindoe]

Hi Ravi,

Thanks for the help - I'll try it out and see what happens...

/Kasperlindoe