Want to delimeted and then transpose but all has to be done by using formula

[Sarthak987654]

Hey Community,

Can anyone help me with this problem I have to convert this file. I have mentioned before and after in the file how I want my data to be cleaned . I have done this manually by using delimited, Transpose and then flash fill.

Can Anyone have a better trick that can help me do this by using the formula I have attached my file in the Thread.
Thanks In Advance

 

[Peter Bartholomew]

The question is, do you have access to Excel 365? If so

= REDUCE({"name","fruit"}, name,
     LAMBDA(list, person,
        LET(
            fruits, TEXTSPLIT(
                XLOOKUP(person, name, fruit), ,
                ","),
            output, HSTACK(IF(ISTEXT(fruits), person), fruits),
            VSTACK(list, output)
        )
     )
  );

 

[AlanSidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"fruit", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "fruit")
in
    #"Split Column by Delimiter"

Excel 2016 (Windows) 64 bit

	B​	C​
11​	name​	fruit​
12​	Sarthak	mango, banana, apple,Passion Fruit
13​	Jignesh	guava, Apricots, Crab apples, Dewberries, Honeydew melon, Jackfruit
14​	Satendra	Nashi Pear, Apricots, Persimmon, Paw Paw, Honeydew melon, Peach, Pomegranate, Pineapple
15​	Uday	mango, Grapes, Persimmon, Voavanga
16​
17​	name	fruit
18​	Sarthak	mango
19​	Sarthak	banana
20​	Sarthak	apple
21​	Sarthak	Passion Fruit
22​	Jignesh	guava
23​	Jignesh	Apricots
24​	Jignesh	Crab apples
25​	Jignesh	Dewberries
26​	Jignesh	Honeydew melon
27​	Jignesh	Jackfruit
28​	Satendra	Nashi Pear
29​	Satendra	Apricots
30​	Satendra	Persimmon
31​	Satendra	Paw Paw
32​	Satendra	Honeydew melon
33​	Satendra	Peach
34​	Satendra	Pomegranate
35​	Satendra	Pineapple
36​	Uday	mango
37​	Uday	Grapes
38​	Uday	Persimmon
39​	Uday	Voavanga

Sheet: Sheet1

 

[Peter Bartholomew]

The nice thing about @AlanSidman's code is that it is accessed from the PQ user interface and generated automatically.



If you had to be able to write the code from scratch it would not be fun!
Bear in mind that PQ needs to be refreshed manually; that can be a plus or a minus depending on your desired workflow.