Home > Blockchain >  Excel formula for creating new rows based on multiple columns
Excel formula for creating new rows based on multiple columns

Time:10-21

I have a worksheet where a want to create a new row where there is a value filled in in column AA trough AE and ignoring the blank cells.

I have tried a formula as:

=IF(COUNTIF($A$3:A3,A3)=COUNTA(OFFSET($AA$3:$AF$100,MATCH(A3,$AA$3:$AF$100,0),0)),INDEX($AA$2:$AF$100,MATCH(A3,$AA$2:$AF$100,0) 1),A3)

![Result now

Waht i'm trying to achieve:

enter image description here

CodePudding user response:

With ms365:

enter image description here

Formula in F2:

=INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(B2:D7="","",A2:A7&"</s><s>"&B2:D7))&"</s></t>","//s"),SEQUENCE(COUNTA(B2:D7),2))

Or

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(ARRAYTOTEXT(IF(B2:D7="","</s><s>",A2:A7&"</s><s>"&B2:D7)),", ","</s><s>")&"</s></t>","//s[node()]"),SEQUENCE(COUNTA(B2:D7),2))

For a Dutch version:

=INDEX(XML.FILTEREN("<t><s>"&TEKST.COMBINEREN("</s><s>";;ALS(B2:D7="";"";A2:A7&"</s><s>"&B2:D7))&"</s></t>";"//s");REEKS(AANTALARG(B2:D7);2))

Or:

=INDEX(XML.FILTEREN("<t><s>"&SUBSTITUEREN(ARRAYTOTEXT(ALS(B2:D7="";"</s><s>";A2:A7&"</s><s>"&B2:D7));"; ";"</s><s>")&"</s></t>";"//s[node()]");REEKS(AANTALARG(B2:D7);2))
  • Related