CODEWITHSUNDEEP
google-sheetscountuniquearray-formulascountif
Fanul
Fanul

Reputation: 13

Increment Number By Value With Condition - Array Formula

i'm confused with the following condition, simply i want to have arrayformula or maybe a custom-formula to increment number in a way bound by specific condition based on value in other column, put it simply :

if the group doesn't change and sub-group is different increment number by 1
else if the group doesn't change and sub-group is doesn't change (same) hold value by previous
else if the group change regardless sub-group value reset number back to 1

for ilustration

** notes Number is the result that i want, in example i fill it manually

Group Sub-Group Animal Number
Land poisonus snake 1
land friendly dog 2
land friendly cat 2
land scary lion 3
aquatic friendly nemo fish 1
aquatic predator shark 2

UPDATE (dummy file link) :
https://docs.google.com/spreadsheets/d/1DAPf-DvWz50_DJ0IqAoSHbfEnfg_mN1lNXHcCjkj27M/edit#gid=0

Upvotes: 1

Views: 505

Answers (2)

player0
player0

Reputation: 1

try:

=INDEX(IF(A4:A="",,VLOOKUP(A4:A&B4:B, {UNIQUE(A4:A&B4:B), COUNTIFS(
 REGEXEXTRACT(UNIQUE(A4:A&"×"&B4:B), "(.*)×"), 
 REGEXEXTRACT(UNIQUE(A4:A&"×"&B4:B), "(.*)×"), 
 SEQUENCE(COUNTA(UNIQUE(A4:A&"×"&B4:B))), "<="&
 SEQUENCE(COUNTA(UNIQUE(A4:A&"×"&B4:B))))}, 2, 0)))

enter image description here

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9355

I have entered my solution in cell D1 of the sheet "Erik Help." As I said in the comments to your original post, this is a more complex solution than I can generally offer here on the free, volunteer-run forums. I did choose to develop and share the formula with you, but I will need to leave it to you (and any other future site visitors who may be interested) to study the formula for understanding how it works. Explaining the formula would take longer than writing it.

Here is the formula:

=ArrayFormula({"Number"; IF(A2:A="",,VLOOKUP(LOWER(A2:A&B2:B),QUERY({UNIQUE(FILTER({A2:B,A2:A&B2:B},A2:A<>"")),COUNTIFS(QUERY(UNIQUE(FILTER({A2:B,A2:A&B2:B},A2:A<>"")),"Select Col1"),QUERY(UNIQUE(FILTER({A2:B,A2:A&B2:B},A2:A<>"")),"Select Col1"),SEQUENCE(COUNTA(QUERY(UNIQUE(FILTER({A2:B,A2:A&B2:B},A2:A<>"")),"Select Col1"))),"<="&SEQUENCE(COUNTA(QUERY(UNIQUE(FILTER({A2:B,A2:A&B2:B},A2:A<>"")),"Select Col1"))))},"Select Col3, Col4"),2,FALSE))})

Upvotes: 0

Related Questions