Add value to Column F for each unique value in Column B, where Column C (next row) = 0

[blvd nights]

So my Excel sheet looks like this:

A......B......C......D......E......F
x......1......1......x......x
x......1......1......x......x
x......1......1......53....x
x......1......1......x......x
x......1......1......x......x
x......1......1......99...x
x......1......0......45....x
x......1......0......x......x
x......1......0......x......x
x......2......1......x......x
x......2......1......x......x
x......2......1......x......x
x......2......1......4.....x
x......2......0......x......x
x......3......1......x......x
x......3......1......66...x
x......3......0......x......x
x......3......0......x......x
x......3......0......463...x
x......3......0......x......x
x......4......1.....222..x
x......4......0......x......x
x......4......0......x......x
x......4......0......x......x
x......5......1......x......x
x......5......1......x......x
x......5......1......x.....x
x......5......0......x......x
x......5......0......x......x
x......6......1......x......x

etc...


Basically, i need a script to add a '1' or "YES" to column F (empty now), in each row which is marked BOLD.

So we are selecting each row with a unique COLUMN B value, but the last one of its kind which has COLUMN C = '1' (before the first '0' turns up).

Selection of rows in which F has to be marked are:

A......B......C......D.........E........F
x......1......1......99........x......"YES"
x......2......1......4..........x......"YES"
x......3......1......66........x......"YES"
x......4......1......222.......x......"YES"
x......5......1......x..........x......"YES"

etc...

Kind of nasty to explain, appreaciate if someone can help. It is a worksheet with over 900.000 records.

Regards blvdnights

[ARGK]

Hi
Paste this code into a module in your workbook and it should do the trick. This assumes your data is in columns A to E and the result will be populated in column F.

Please Login or Register  to view this content.

Good luck.
Tony

[watersev]

hi blvd nights, welcome to ExcelForum, option to make it basing on the data sample provided, please check attachment, press Run button or run code "test"

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.