• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Sarthak987654

New Member
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
 

Attachments

Peter Bartholomew

Well-Known Member
The question is, do you have access to Excel 365? If so
Code:
= REDUCE({"name","fruit"}, name,
     LAMBDA(list, person,
        LET(
            fruits, TEXTSPLIT(
                XLOOKUP(person, name, fruit), ,
                ","),
            output, HSTACK(IF(ISTEXT(fruits), person), fruits),
            VSTACK(list, output)
        )
     )
  );
 

Attachments

AlanSidman

Well-Known Member
An alternative is with Power Query

Code:
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
Sarthakmango, banana, apple,Passion Fruit
13
Jigneshguava, Apricots, Crab apples, Dewberries, Honeydew melon, Jackfruit
14
SatendraNashi Pear, Apricots, Persimmon, Paw Paw, Honeydew melon, Peach, Pomegranate, Pineapple
15
Udaymango, Grapes, Persimmon, Voavanga
16
17
namefruit
18
Sarthakmango
19
Sarthak banana
20
Sarthak apple
21
SarthakPassion Fruit
22
Jigneshguava
23
Jignesh Apricots
24
Jignesh Crab apples
25
Jignesh Dewberries
26
Jignesh Honeydew melon
27
Jignesh Jackfruit
28
SatendraNashi 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
Udaymango
37
Uday Grapes
38
Uday Persimmon
39
Uday Voavanga
Sheet: Sheet1
 

Peter Bartholomew

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

81053

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.
 
Top