CODEWITHSUNDEEP
excelfunctionexcel-formula
jeffry
jeffry

Reputation: 327

Increment column value when certain conditions are met in Excel

I am trying to create a formula that increments values for 2 columns based on the previous row, the caveat being that it should increment by 1 when a certain value is present and increment by 2 when another value is present.

I have shown the expected output in columns ID and NUMBER.

You can see 3 sets of data that repeat, this is highlighted by the ID column, this value should increment by 1 every 24 rows - 63, 64, 65 - this can be done manually but if there is a formula for this it that would be very useful.

The main part of this question is column NUMBER, when column C changes to 'emr_p_file' the NUMBER column should increment by 2 but when the next row changes to 'L2_*' then it should only increment by 1.

Row 1 values for ID and NUMBER can be static, from row 2 onwards they should be calculated by a formula of some sort.

Is something like this possible in excel, if so please help.

CSV export: link:https://pastebin.com/zkjsB9L7

enter image description here

Upvotes: 0

Views: 2859

Answers (1)

Terry W
Terry W

Reputation: 3257

The starting ID and NUMBER in cell F1 and G1 will be static, in cell F2 enter the following formula and drag it down:

=F1+(MOD(ROW(1:1),24)=0)

the logic is to use ROW function to return the row number from previous row, and use MOD function to find out if it is fully divisible by 24, if so increase the previous ID by 1 (which is TRUE returned by the equation in the brackets).

in cell G2 enter the following formula and drag it down:

=G1+IF(C2=C1,0,IF(FIND("_",C1)=3,2,1))

the logic is to use nested IF functions to find out if the data set is the same as the previous data set, if so returns 0, if not means there is a change of data set. The next IF will find out if the old data set starts by LR or emr, if former return 1 otherwise 2.

I notice that your example starting Row 49 is NOT increasing the values in Column G: NUMBER as prescribed. Can you please clarify if you provided incorrect expected outcome for those rows or if there is any missing criteria you did not mention in your post?

Upvotes: 1

Related Questions