I have a spreadsheet with two columns of data. The first column contains an
8 digit number to identify each factory. The first 6 digits represent the
main factory and the remaining 2 represent the feeder plants for the factory.

The second column contains text that identfies if the factory(s) are "Open"
or "Closed".

The goal is to automate a way to determine if a factory and all it's feeder
plants have closed. Since a factory can have a dozen feeder plants, my
spreadsheet contains over 3000 rows.

How would I write VBA code to loop thru the columns to determine if all
factory/plant combinations are closed?

Hi,
Assuming data goes from row 1 to row 3000
in column C, enter:
=SUMPRODUCT((LEFT(A1,6)=LEFT($A$1:$A$3000,6))*($B$ 1:$B$3000="OPEN")*1)
This will return the number of open feeders. (a bit repetitives since it
will be on each row)
Just filter for the zeroes to get factories with no open feeders.

Regards,
sebastienm

"TEB2" wrote:

I have a spreadsheet with two columns of data. The first column contains an
8 digit number to identify each factory. The first 6 digits represent the
main factory and the remaining 2 represent the feeder plants for the factory.

The second column contains text that identfies if the factory(s) are "Open"
or "Closed".

The goal is to automate a way to determine if a factory and all it's feeder
plants have closed. Since a factory can have a dozen feeder plants, my
spreadsheet contains over 3000 rows.

How would I write VBA code to loop thru the columns to determine if all
factory/plant combinations are closed?