英文:
Formula to Split Cells and add additional row with that data in Google Sheets
问题
我需要一个Google Sheets公式来将第一个表中的数据转换为第二个表中的数据。在第1列中，将以“,”分隔的任何数字拆分，并将数据放在新行下面，然后将其余列中的数据复制到第1列中的数据旁边，查看表2以了解它应该是什么样子的。
我已经有下面的公式，它运行良好：
=ArrayFormula(LET(range, CurrentTable!A2:F, headers, OFFSET(range,-1,,1,), col_1, INDEX(range,,1), col_2, INDEX(range,,2), combine, TOCOL(SPLIT(FILTER(IF((col_1="") * (col_2<>""), "X", col_1), col_2<>""), ", ", 1, 0)&"~"& BYROW(FILTER(OFFSET(range,,1,,COLUMNS(range)-1), col_2<>""), LAMBDA(r, JOIN("~", r))), 1), list, SPLIT(REGEXREPLACE(FILTER(combine, NOT(REGEXMATCH(combine, "^~"))), "^X", ""), "~", 1, 0), {headers; list}))
但我需要修改这个公式以执行3个额外的操作：
- 我需要它忽略Col1中的空白，现在它只是原样返回它。
- 我希望它排除不包含数字或数字组合（例如23,34,22）的字段，所以我想排除任何单词（例如Not allocated，但它可以是任何单词等）。不像简单的包含纯数字值23,34,22。
- 如果Col2中存在"Sub Div1"，我想创建一个新的列并填充为"Sub Div1"。谢谢。
如果这些修改对您有帮助，您可以将它们添加到您的公式中。
英文:
Wondering if anyone can help.
Essential I need a google sheets formula that will transform the data in the first table to the second table. In column 1 split any numbers separated by ",", put the data in a new row underneath and then copy the data in the rest of the columns next to the data in column 1, see the table 2 what it should look like.
I've got the formula below and it works well:
=ArrayFormula(LET(range, CurrentTable!A2:F, headers, OFFSET(range,-1,,1,), col_1, INDEX(range,,1), col_2, INDEX(range,,2), combine, TOCOL(SPLIT(FILTER(IF((col_1="") * (col_2<>""), "X", col_1), col_2<>""), ", ", 1, 0)&"~"& BYROW(FILTER(OFFSET(range,,1,,COLUMNS(range)-1), col_2<>""), LAMBDA(r, JOIN("~", r))), 1), list, SPLIT(REGEXREPLACE(FILTER(combine, NOT(REGEXMATCH(combine, "^~"))), "^X", ""), "~", 1, 0), {headers; list}))
But I need to amend the formula to do 3 additional things:
- I need it to ignore blanks in Col1, right now it just brings it back as is.
- I want it to exclude any fields that don't contain a number or a combination of numbers (i.e 23,34,22), so I want to exclude any words (such as Not allocated, but it could be any word) etc. Not simple as include just numeric value as 23,34,22 isn't strictly a numeric value.
- I want to a new column created and populated with 'Sub Div1', if that is present in col2. Thanks.
Table 1
| Area Code | City | Account Name | Account Number | Color | Start Date |
|---|---|---|---|---|---|
| 101 | Dallas | Ford | 10001 | Red | 2004 |
| 99,68 | Houston | Toyota | 10002 | Blue | 2005 |
| 70 | San Diego | Land Rover | 10003 | Green | 2006 |
| 73 | Sub Div3 - Seattle | Mini | 10004 | Black | 2007 |
| 78 | Auburn , Birmingham & Little Rock | Bentley | 10005 | Purple | 2008 |
| 9, 33 | Miami & Tampa | Nissan | 10006 | Yellow | 2009 |
| 110 | Detroit | Tesla | 10007 | Orange | 2010 |
| 73,9,33 | Sub Div1 - Chicago & Detroit | Tesla | 10007 | Orange | 2010 |
| Sub Div1 - Atlanta | Honda | 10009 | Pink | 2012 | |
| 99,68 | Sub Div1 - Baton Rouge | Seat | 10010 | Grey | 2013 |
| Not Allocated | Sub Div2 - Boston/New York/Washington | GM | 10011 | White | 2014 |
Table 2
| Area Code | SD | City | Account Name | Account Number | Color | Start Date |
|---|---|---|---|---|---|---|
| 101 | Dallas | Ford | 10001 | Red | 2004 | |
| 99 | Houston | Toyota | 10002 | Blue | 2005 | |
| 68 | Houston | Toyota | 10002 | Blue | 2005 | |
| 70 | San Diego | Land Rover | 10003 | Green | 2006 | |
| 73 | Sub Div3 - Seattle | Mini | 10004 | Black | 2007 | |
| 78 | Auburn , Birmingham & Little Rock | Bentley | 10005 | Purple | 2008 | |
| 9 | Miami & Tampa | Nissan | 10006 | Yellow | 2009 | |
| 33 | Miami & Tampa | Nissan | 10006 | Yellow | 2009 | |
| 110 | Detroit | Tesla | 10007 | Orange | 2010 | |
| 73 | Sub Div1 | Sub Div1 - Chicago & Detroit | Tesla | 10007 | Orange | 2010 |
| 9 | Sub Div1 | Sub Div1 - Chicago & Detroit | Tesla | 10007 | Orange | 2010 |
| 33 | Sub Div1 | Sub Div1 - Chicago & Detroit | Tesla | 10007 | Orange | 2010 |
| 99 | Sub Div1 | Sub Div1 - Baton Rouge | Seat | 10010 | Grey | 2013 |
| 68 | Sub Div1 | Sub Div1 - Baton Rouge | Seat | 10010 | Grey | 2013 |
答案1
得分: 1
=let(Λ,index(reduce(wraprows(,7,),sequence(match(,0/(A2:A<>""))),lambda(a,c,let(Σ,split(index(A2:A,c),","),{a;
{tocol(Σ),if(sequence(counta(Σ)),{ifna(regexextract(index(B2:B,c),"^Sub Div1"),index(B2:F,c)})}})))),
{{A1,"SD",B1:F1};filter(Λ,--choosecols(Λ,1)))
英文:
You may try:
=let(Λ,index(reduce(wraprows(,7,),sequence(match(,0/(A2:A<>""))),lambda(a,c,let(Σ,split(index(A2:A,c),","),{a;
{tocol(Σ),if(sequence(counta(Σ)),{ifna(regexextract(index(B2:B,c),"^Sub Div1")),index(B2:F,c)})}})))),
{{A1,"SD",B1:F1};filter(Λ,--choosecols(Λ,1))})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论