Results 1 to 3 of 3

Thread: break inconsistent rows of data into a long list

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte sn858's Avatar
    Join Date
    Apr 2019
    Posts
    1
    Articles
    0
    Excel Version
    office 365

    break inconsistent rows of data into a long list

    Hi, I've not posted here beforeClick image for larger version. 

Name:	sample data.jpg 
Views:	23 
Size:	65.7 KB 
ID:	9062Click image for larger version. 

Name:	sample data.jpg 
Views:	23 
Size:	65.7 KB 
ID:	9062, so sorry if this isn't very clear.

    I've got a list of email addresses with additional information associated to them in random length rows as shown below (dummy email addresses used).

    I want to be able to capture each email into it's own row, with the additional information alongside it in additional columns (an email's associated data always follows after each email).
    I know I can identify each email address as it will always have an "@" in it, but the number of additional bits of information varies between each email address.

    Can anyone advise how best to do this? I assume some sort of looping in VBA (i don't know VBA but would look to adapt something in existence perhaps?)

    I've got about 270 rows, and some of the rows go over 300 columns across.

    hope this makes sense.

    thanks

    Steve


    EXAMPLE FROM MY EXCEL - I'm running Office 365

    range of data formatted as below
    column 1 column 2 column 3 column 4 column 5 column 6 column 7 column 8
    email1@email deliver email2@email pending fail email3@email receive deliver
    email4@email receive pending email5@email
    email6@email pending email7@email receive email8@email Receive Pending Fail
    need to turn into a long list
    column 1 column 2 column 3 column 4
    email1@email deliver
    email2@email pending fail
    email3@email receive deliver
    email4@email receive pending
    email5@email
    email6@email pending
    email7@email receive
    email8@email Receive Pending Fail

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    155
    Articles
    0
    Excel Version
    2019
    Pictures
    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.


    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.


    2. Make sure that your desired results are also shown (mock up the results manually).


    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).


    4. Try to avoid using merged cells as they cause lots of problems.


    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    155
    Articles
    0
    Excel Version
    2019
    Would a presentation as follows work for you?

    Data Range
    A
    B
    1
    Email
    Value
    2
    email1@email
    deliver
    3
    email2@email
    pending
    4
    email2@email
    fail
    5
    email3@email
    receive
    6
    email3@email
    deliver
    7
    email4@email
    receive
    8
    email4@email
    pending
    9
    email6@email
    pending
    10
    email7@email
    receive
    11
    email8@email
    Receive
    12
    email8@email
    Pending
    13
    email8@email
    Fail


    If yes, then using Power Query and the following Mcode will provide that solution

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"column 1", type text}, {"column 2", type text}, {"column 3", type text}, {"column 4", type text}, {"column 5", type text}, {"column 6", type text}, {"column 7", type text}, {"column 8", type text}}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
        #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Email", each if(Text.Contains([Value],"@")) then [Value] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Email"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Value], "@")),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Email", "Value"})
    in
        #"Reordered Columns"
    Last edited by alansidman; 2019-04-25 at 04:10 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •