Make pasting "(construct complete)" optional

[bthuronyi]

Several formulas in GG tab count the number of parts in a construct based on filled/empty cells, and fudge things to ignore the "(construct complete)" entry pasted from the GG Queuer tool. Instead, we should handle either users pasting that in or not. This could be approached by checking for number of rIDs present across the columns rather than for filled/empty columns, e.g. CONCATENATE and then counting occurrences somehow. To support that, we would need to standardize how many times rIDs appear in the part ID. Recent GG parts have it at the beginning only -- good -- and old parts have it only at the end -- fine -- but some have it in both places, depending on when they were pasted in. Rather than supporting multiple rID occurrences per part, we should sanitize our GG data so all Part entries start with the rID and that's the only occurrence in each. One could approach that using SPLIT formulas to separate out different rID placements and merge them back together in an appropriate order.

[santiagochrist]

Sanitizing Data

• I have made two helper columns that will help us remove the second instance of the rID at the end.
• Helper Column 1 counts the number of times it detects the regex "[r]\d{3}" (e.g., r123).
• Helper Column 2 detects if HC1 has a value of "2", in which case it takes a substring that cuts out the rID at the end.

HC1:

=COUNTA( // count the number of cells from the array that split returns
SPLIT( // divides the GG fragment entry into array elements
REGEXREPLACE(N3,"[r]\d{3}"," $"),"$") // finds an rID and replaces it with '$', at which point creates new cells in the array
) // technical note: the replacement text NEEDS to be " $" since it will not detect rIDs located in the first character of the string

The above code uses cell N3 as an example. Figuring out HC1 took an embarrassingly long time, but HC2 was a little more straightforward.

HC2:

=if(D16=2, // if we detected 2 rIDs
left(C16,len(C16)-8),"") // take the substring without the last 8 characters (i.e., " >> r123" which is 8 characters).

This effectively returns text with only one instance of an rID at the start, which is what we wanted!

[santiagochrist]

After talking with B, we came up with this:

HC1 (optional, need not be used)

=COUNTIF(
SPLIT( // create an array separated by the symbols we introduce below
REGEXREPLACE(G3,"[r]\d{2,4}","$@ "),"@"), // replace rID instances with $@ and then count @, prevents miscounting
"*$*") // count the $ sign

HC2 (data sanitation)

=iferror( // want to throw out a blank if error, e.g., if construct is complete so the below code reads a blank cell
concatenate(
regexextract(N3,"[r]\d{2,4}"), // take the rID (2-4 numbers) and place it at the start of string
" ", // separate with a space
trim(REGEXREPLACE(N3,"[>]*\s*[r]\d{2,4}",""))) // trim to remove blank spaces, then take the string without the second rID if it has it
,"") // throws blank if error

[santiagochrist]

Update

• Data on live CC has been sanitized, comparison with a CC copy looks ok.
• New column: rID count.
• We want this column since it would simplify our new GG formula design of obtaining the rID count (instead of empty cell counts). We don't want every formula to have to retrieve the rID count every single time.

rID Count (thanks B)

=counta(iferror(filter(N3:W3,regexmatch(N3:W3,"[r]\d{2,4}"))))