Warm tip: This article is reproduced from stackoverflow.com, please click
google-sheets javascript

How to make dynamic consecutive numbers in one column

发布于 2020-03-31 23:01:01

I have a list of bills. I must continuously name them according to my bank accounts.

Normally I would sort my sheet by BANK ACCOUNT and DATE, then tag the NUMBER COLUMN by using the "blue square", and after that, I sort by date again.

I wanna program a script function to iterate through the column NUMBER which defines and read the ACCOUNT column and tag the NUMBER row accordingly. I thought I make a "settings"-column somewhere to define the look of the "auto-formatter":

IF BANK {YY}-BANK-{NUMBER} IF CASH {YY}-CASH-{NUMBER}

In the end, it should look like the example below:

 DATE   NUMBER       ACCOUNT
|------|------------|-------|
 1. Jan  18-CASH-01   CASH
 1. Jan  18-BANK-01   BANK
 2. Jan  18-CASH-02   CASH
 3. Jan  18-CASH-03   CASH
 4. Jan  18-BANK-02   BANK

I made a demo sheet that is free to change or clone. The basic formula is implemented. Only the formula to iterate is missing

https://docs.google.com/spreadsheets/d/1aJRqODUMyJai-aVyTj3_konvc8qpL8idKEHNCs9cxSo/edit?usp=sharing

Has anyone a suggestion or starting point for me? I am not good at google sheets API.

Thank you very much in advance

Questioner
Jan
Viewed
60
分享
JPV 2020-01-31 23:29

Maybe this can be done with a formula? In F1 I entered

={"Format"; ArrayFormula(if(len(D2:D), text(C2:C7, "yy")&"-"& UPPER(D2:D)&"-"&iferror(SORT(ROW(D2:D),SORT(ROW(D2:D),D2:D,1),1)-MATCH(D2:D,SORT(D2:D),0)-ROW()+1),))}

See if that works for you?

EDIT, to match the names of the backaccounts to a range, use vlookup

={"Solution A"; ArrayFormula(if(len(D2:D), text(C2:C7, "yy")&"-"& 
VLOOKUP(D2:D, SETTINGS!A10:C, 2, 0)&"- 
"&iferror(SORT(ROW(D2:D),SORT(ROW(D2:D),D2:D,1),1)- 
MATCH(D2:D,SORT(D2:D),0)-ROW()+1),))}
Jan 2020-01-31 23:02:59

WoW!!! 🙃It works out of the box! Thank you! But I still have trouble to fit your solution with my "settings"-sheet. In reality, I am determined to a specific name in my "Bill Type"-column and wanna be able to rename that accordingly from my "settings"-page. For example: instead of "PAYPAL" it must be renamed as "PP". "Bank Account Company" must be renamed to "BANK1". I hope you understand? is that also possible with your "oneliner"? Or must that be done with a function? Thank you again!

JPV 2020-01-31 23:29:57

Edited my post. Please note that selecting 'type' in col D will return error because it is not in the vlookup range in settings.

Jan 2020-01-31 23:34:13

AWESOME! 🥳 I can't believe that you fixed that in lightning speed (I am sitting here since hours 😀). Thank you so much!

JPV 2020-01-31 23:36:04

Most welcome @Jan !

Jan 2020-02-01 00:49:59

ehm.. maybe you can have a look through my "original sheet" (not the demo). I renamed all demo ranges to variables, which are matching exactly to that in my original sheet. I kept all relations the same. but somehow my original one is telling me only "Formula parse error." There are only these two vars and that one column. I dont understand why that is happening. :-( I will delete this link after a solution was found... ORIGINAL: Everything is in "Einstellungen" (settings) and "A->A3" sheet... docs.google.com/spreadsheets/d/…

Related issues

1
Getting .json is not a function on Promise.all w/fetch
2
Javascript for loop only giving one output
3
find array value that index matches another array values
4
React
5
Communication between two components using ReactJS
6
Figma Plugin: callback on file updated
7
indexof() partial match issue
8
React useState hook and submit in multi step form
9
How do i get this firebase data to use it outside the loop?
10
How to define a font size that scales with browser zoom level?