break inconsistent rows of data into a long list

sn858

New member
Joined
Apr 25, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
office 365
Hi, I've not posted here beforesample data.jpgsample data.jpg, 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 1column 2column 3column 4column 5column 6column 7column 8
email1@emaildeliveremail2@emailpendingfailemail3@emailreceivedeliver
email4@emailreceivependingemail5@email
email6@emailpendingemail7@emailreceiveemail8@emailReceivePendingFail
need to turn into a long list
column 1column 2column 3column 4
email1@emaildeliver
email2@emailpendingfail
email3@emailreceivedeliver
email4@emailreceivepending
email5@email
email6@emailpending
email7@emailreceive
email8@emailReceivePendingFail
 
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.
 
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:
Back
Top