I have a below thing to replicate the rows into multiple rows where the fields are separated with comma.

SCRIPTNM	TABLE NAME
scriptA.ksh	DB.TABLE1, DB.TABLE2
scriptA.exp	DB.TABLE4

to replace as below:

SCRIPTNM	TABLE NAME
scriptA.ksh	DB.TABLE2
scriptA.ksh	DB.TABLE2
scriptA.exp	DB.TABLE4

I can try to split the rows to columns using comma delimiter, but the below rows should not effect.

CodePudding user response：

Formula-based solution for Excel 2010, as tagged:

Assuming:

1. Data in A1:B3 (with headers in row 1)
2. The range B2:B3 does not contain any empty cells

This array formula in F2:

=IF(ROWS(B$2:B2)>SUM(1 LEN(B$2:B$3)-LEN(SUBSTITUTE(B$2:B$3,",",""))),"",INDEX(A$2:A$3,IFERROR(1 MATCH(ROWS(B$2:B2)-1/2,MMULT(N(ROW(B$2:B$3)>=TRANSPOSE(ROW(B$2:B$3))),1 LEN(B$2:B$3)-LEN(SUBSTITUTE(B$2:B$3,",","")))),1)))

Copy down until you start to get blanks for the results.

Non-array formula in F2:

=IF(F2="","",TRIM(MID(SUBSTITUTE(INDEX(B$2:B$3,MATCH(F2,A$2:A$3,0)),",",REPT(" ",99)),99*(COUNTIF(F$2:F2,F2)-1) 1,99)))

Copied down as required.

CodePudding user response：

Formula based alternative solutions, (Array Formulas needs to be keyed with CTRL SHIFT ENTER)

• Formula used in cell D2 --> To Get SCRIPTNM

=IFERROR(IF(ROW(A1)=1,A2,
IF(COUNTIF($D$1:D1,D1) 1>SUMPRODUCT((MID(
VLOOKUP(D1,$A$2:$B$3,2,),ROW($1:$104),1)=",")*1) 1,
INDEX($A$2:$A$3,MATCH(D1,$A$2:$A$3,) 1),D1))&"","")

• Formula used in cell E2 --> To Get TABLE NAME

=IFERROR(TRIM(MID(SUBSTITUTE(
VLOOKUP(D2,$A$2:$B$3,2,),",",REPT(" ",100)),
(COUNTIF($D$1:D2,D2)-1)*100 1,100)),"")

---

Or, One more way,

• Formula used in cell G2 --> To Get SCRIPTNM

=IFERROR(INDEX($A$2:$A$3,MATCH(TRUE,COUNTIF(G$1:G1,$A$2:$A$3)<
(LEN($B$2:$B$3)-LEN(SUBSTITUTE($B$2:$B$3,",","")) 1),0)),"")

• Formula used in cell H2 --> To Get TABLE NAME

=IFERROR(TRIM(MID(SUBSTITUTE(
VLOOKUP(G2,$A$2:$B$3,2,0)&", ",", ",REPT(" ",100)),
COUNTIF($G$2:G2,G2)*100-99,100)),"")

---