replicate data based on textsplit result

[ExcelNewbie2020]

Sirs, using excel formula how to replicate data based on the result of of textsplit values.

based on my table below, columns A to E got replicated because of textsplit from column F. thank you

Sample_practice.xlsx
	A	B	C	D	E	F
1	No	make	Date	code	loc	place
2	1	A	2023-03-05	ABC123	india	uk,us,india
3	2	b	2022-12-25	DEF245	uk	malta,us,uk
4	3	c	2022-12-12	HIJ256	us	rus,us
5
6
7
8
9
10
11
12
13	expected result
14	No	make	Date	code	loc	place
15	1	A	2023-03-05	ABC123	india	uk
16	1	A	2023-03-05	ABC123	india	us
17	1	A	2023-03-05	ABC123	india	india
18	2	b	2022-12-25	DEF245	uk	malta
19	2	b	2022-12-25	DEF245	uk	us
20	2	b	2022-12-25	DEF245	uk	uk
21	3	c	2022-12-12	HIJ256	us	rus
22	3	c	2022-12-12	HIJ256	us	us
Sheet2

 

[Legacy 143009]

Hi,

This can be an option if you are OK with VBA:

Sub test()
  Dim myArray As Variant, tmpArray As Variant, tmpValue As Variant, i As Long, j as long
  myArray = Range("A2:F4")
  ReDim tmpArray(1 To UBound(myArray, 2), 1 To 1)
 
  For i = 1 To UBound(myArray, 1)
    tmpValue = Split(myArray(i, 6), ",")
    For Each tmpVal In tmpValue
      For j = 1 to 5
        tmpArray(j, UBound(tmpArray, 2)) = myArray(i, j)
      Next
      tmpArray(6, UBound(tmpArray, 2)) = tmpVal
      ReDim Preserve tmpArray(1 To UBound(myArray, 2), 1 To UBound(tmpArray, 2) + 1)
    Next
  Next
  ReDim Preserve tmpArray(1 To UBound(myArray, 2), 1 To UBound(tmpArray, 2) - 1)
 
  Range("A15").Resize(UBound(tmpArray, 2), UBound(tmpArray, 1)).Value = Application.Transpose(tmpArray)
End Sub

 

[RoryA]

It's also a built-in function with power query, if that's an option.

 

[ExcelNewbie2020]

It's also a built-in function with power query, if that's an option.

thank you sir, if its not possible with excel formula that can be an option

 

[Georgiboy]

Here is a potential formula option:

DatatoChange.xlsx
	A	B	C	D	E	F
1	No	make	Date	code	loc	place
2	1	A	44990	ABC123	india	uk,us,india
3	2	b	44920	DEF245	uk	malta,us,uk
4	3	c	44907	HIJ256	us	rus,us
5	4	d	44907	dave	isr	tur,hun
6
7
8
9	expected result
10	No	make	Date	code	loc	place
11	1	A	44990	ABC123	india	uk
12	1	A	44990	ABC123	india	us
13	1	A	44990	ABC123	india	india
14	2	b	44920	DEF245	uk	malta
15	2	b	44920	DEF245	uk	us
16	2	b	44920	DEF245	uk	uk
17	3	c	44907	HIJ256	us	rus
18	3	c	44907	HIJ256	us	us
19	4	d	44907	dave	isr	tur
20	4	d	44907	dave	isr	hun
Sheet3

Cell Formulas
Range	Formula
A11:F20	A11	=LET( data,A2:F5, n,LEN(TAKE(data,,-1))-LEN(SUBSTITUTE(TAKE(data,,-1),",",""))+1, tc,TOCOL(IF(SEQUENCE(,MAX(n))<=n,BYROW(DROP(data,,-1),LAMBDA(x,TEXTJOIN(",",,x))),#N/A),3), FILTERXML("<t><s>" & SUBSTITUTE(tc&","&TEXTSPLIT(TEXTJOIN(",",,TAKE(data,,-1)),,","),",","</s><s>") & "</s></t>","//s[" & SEQUENCE(,6) & "]") )
Dynamic array formulas.

 

[ExcelNewbie2020]

Here is a potential formula option:

DatatoChange.xlsx
	A	B	C	D	E	F
1	No	make	Date	code	loc	place
2	1	A	44990	ABC123	india	uk,us,india
3	2	b	44920	DEF245	uk	malta,us,uk
4	3	c	44907	HIJ256	us	rus,us
5	4	d	44907	dave	isr	tur,hun
6
7
8
9	expected result
10	No	make	Date	code	loc	place
11	1	A	44990	ABC123	india	uk
12	1	A	44990	ABC123	india	us
13	1	A	44990	ABC123	india	india
14	2	b	44920	DEF245	uk	malta
15	2	b	44920	DEF245	uk	us
16	2	b	44920	DEF245	uk	uk
17	3	c	44907	HIJ256	us	rus
18	3	c	44907	HIJ256	us	us
19	4	d	44907	dave	isr	tur
20	4	d	44907	dave	isr	hun
Sheet3

Cell Formulas
Range	Formula
A11:F20	A11	=LET( data,A2:F5, n,LEN(TAKE(data,,-1))-LEN(SUBSTITUTE(TAKE(data,,-1),",",""))+1, tc,TOCOL(IF(SEQUENCE(,MAX(n))<=n,BYROW(DROP(data,,-1),LAMBDA(x,TEXTJOIN(",",,x))),#N/A),3), FILTERXML("<t><s>" & SUBSTITUTE(tc&","&TEXTSPLIT(TEXTJOIN(",",,TAKE(data,,-1)),,","),",","</s><s>") & "</s></t>","//s[" & SEQUENCE(,6) & "]") )
Dynamic array formulas.

that worked.. thank you

 

[Georgiboy]

You're welcome, thanks for the feedback.