0

I have a Google Sheet in the following format:

ID VAL
1 dataH
2 dataW
3 dataX
4 dataC
5 dataG
6 dataL
... ...

ID and VAL are on cells A1 and B1, respectively.

In A2, I have the following formula: =ArrayFormula(if(ISBLANK(B2:B), "",ROW(A2:A)-1)), which puts in a number in the ID column if there is data in the VAL column.

However, I'd like to be able to sort data, for example from A to Z, while keeping ID constant. However, when I sort VAL, the cell containing the formula, ID 1, gets dragged down to its proper position, but then leaves all cells in the column above it blank.

I have 2 possible solutions: somehow have the formula in A1 (ID) and display ID instead of 1; or have the formula in C1 and output the results starting from A2.

Is there a way to do one of the 2 solutions above?

2 Answers 2

1

  Problem

  1. You don't want your formula to change row position when you sort the range
    AND
  2. You want the IDs to remain paired with the same VAL as they were before the sort.
    Interpretation 1
     

Restoring original sort orders

Your approach to creating IDs (by incrementing from 1) is a similar problem to trying to ensure that you can restore your original sort order.

  1. If your data doesn't include an index column or one that can double as the index (eg. date/time) that can be used to restore the original sort order, then you must manually add one.
  2. Any dynamically generated index using a formula will suffer the same problem as your ID formula regardless if it is moved outside the sort range.

Different Approaches

  1. You could manually number the ID column (similar to @User0's answer)
  2. You could maintain a separate index and perform lookups.
  3. You could manually add IDs as you add new VALs, and have a formula to keep track of the next available number.

This last (#3) might be the simplest approach if you are manually adding new data. It can even be combined with @User0's approach when you add larger sets of data.

For example,

  • For manual row-by-row additions, use a formula in E1 to return the next available ID and use that Next ID value as your manually entered ID for the new VAL
    =MAX(A:A)+1
    
    MAX Function

    =MAX(A:A)+1
  • For adding multiple rows in one go, the formula can be adapted to create a sequence of numbers starting from the first available ID and one additional for each additional VAL missing an ID. Then you would copy and paste (values only) the IDs into the ID column. For example:
    =LET(nextId, MAX(A:A)+1, 
        needsId, MAX(SUMPRODUCT(A:A="",B:B<>""),1),
        SEQUENCE(needsId,1,nextId))
    
    LET  MAX  SEQUENCE  SUMPRODUCT

    longer formula
  • This would allow you to sort your data including the IDs without worry. example of formula
2
  • 1
    Interpretation 1 is pretty much what I want, where the ID moves with the data.
    – bigyihsuan
    Commented Jun 16 at 0:36
  • The only alternative is to use an Apps Script
    – Blindspots
    Commented Jun 16 at 2:01
0

somehow have the formula in A1 (ID) and display ID

=ARRAYFORMULA(IF(ROW(A1:A)=1, "ID", IF(LEN(B1:B), ROW(A1:A)-1, )))

However, this will not solve your issue. The best you can do is to turn the output of array formula into static values via CTRL + C and then re-pasting it with CTRL + SHIFT + V

Also, the ID could be assigned already beforehand so when you sort it, it will act like static:

=ARRAYFORMULA(IF(ROW(A1:A)=1, "ID", IF(LEN(B1:B), 
 VLOOKUP(B1:B, {SORT(B1:B), ROW(A1:A)}, 2, 0), )))

0

If none of the above is acceptable you will need to have formula per cell... paste in A2 cell and drag down:

=IF(LEN(B2), ROW()-1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.