CODEWITHSUNDEEP
google-sheetsnumbersgoogle-sheets-formulasequencerepeat
A Lin
A Lin

Reputation: 3

how to auto generate a sequence of words and numbers in google sheet?

I need to categorize a list of "purchase orders" into "page and item numbers". For example, Page1, Item1~7. I have found the way to do 1 simple sequence of numbers from the link below. But I can't figure out the way to do the "page numbers", and for more advance, adding an empty row between each page. Is it possible to just use the formula?

https://i.sstatic.net/F4Z48.jpg

How to generate a repeating sequence of numbers in google sheet?

Upvotes: 0

Views: 1024

Answers (3)

Erik Tyler
Erik Tyler

Reputation: 9355

As mentioned in my comment to the original post, it would have been more efficient to have had a link to a spreadsheet containing the starter data and an indication of where you wanted the results (same sheet? different sheet?). But in the interest of moving on, I'll suppose that your original header, "purchase orders" is in A1 and that your original purchase-order data runs A2:A.

Based upon those assumptions, the following formula will produce the two column headers and all results shown in your desired-results image:

=ArrayFormula({"page_item",A1; IFERROR(VLOOKUP(FLATTEN("P"&SEQUENCE(ROUNDUP(COUNTA(A2:A)/7))&"_#"&SEQUENCE(1,7+1)), {ARRAY_CONSTRAIN(FLATTEN("P"&SEQUENCE(ROUNDUP(COUNTA(A2:A)/7))&"_#"&SEQUENCE(1,7)),COUNTA(A2:A),1),FILTER(A2:A,A2:A<>"")},{1,2},FALSE), {"",""})})

It can go anywhere on the same sheet as written, but it probably makes the most sense to place it in the top cell of some otherwise empty column (e.g., C1).

enter image description here

If you want some other number of sections per page, just change the four instances of 7 in the formula to another number.

Obviously, if your real data isn't in Column A, you'll need to adjust the formula ranges to match your actual range.

As to how it works, that's a bit hard to explain; and this is such a custom functionality that I don't know that it will be widely sought or used by other site visitors. So let's make sure it's what you want first; and then, if you'd like an explanation, report back, and I'll get into it.

Upvotes: 1

p._phidot_
p._phidot_

Reputation: 1952

Assume PO = column A, page item = column b, PO = column c.

in B2 :

=IF(MOD(ROW()-1,8)=0,"","P"&INT((ROW()-1)/8+1)&"_#"&MOD(ROW()-1,8))

Idea : use row to drive the count.. mod(x,8) and small loop count & int(x/8+1) as big loop count.

in C2 :

=IF(MOD(ROW()-1,8)=0,"",OFFSET(A2,-1*COUNTBLANK($B$2:B2),0))

Idea : "how many empty cell in column B so far" = "the amount of shift needed to 'load' column A data".

Please share if it understandable/works/not.

Upvotes: 0

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

I can't figure out how to add empty row every 7th row but I figured out how to make a sequence and build a table without empty rows:

enter image description here

={ARRAYFORMULA("P"&ArrayFormula(CEILING(sequence(counta(B2:B))/7))&"_#"&array_constrain(transpose(split(rept("1|2|3|4|5|6|7|",counta(B2:B)),"|")),counta(B2:B),1)),
array_constrain(B2:B,counta(B2:B),1)}

My solution is available here: https://docs.google.com/spreadsheets/d/1owokwWW5OvN5VwZAWB5HvDxtBT82BqLJUMBnio9Gi2M/copy

Upvotes: 0

Related Questions