Help with automating a table with two rows of similar data into one row

Question

From This:

To this:

Answer 1

For sake of discussion, assume you want the result stored in a worksheet named Target.

One easy way to accomplish this is with a macro. Select the top left cell of your data and invoke the macro. In the macro:

• Assign the selected cell to a range object named data. Assign the worksheet Target to a worksheet object named result. Assign result.range("A1") (or whatever starting position you like) to a range object named out.
• Loop:
• --Copy data.range("A1:D1") to out.range("A1:D1").
• --Add data.range("E1") and data.range("E2") and store the sum in out.range("E1").
• --Copy data.range("E1") to out.range("F1")
• --Copy data.range("D2") to out.range("G1")
• --Copy data.range(("E2") to out.range("H1")
• --Set data to data.offset(2,0)
• --Set out to out.offset(1,0)
• Until data is empty

Answer 2

Hi,

I create a sample about your requirement and you can use Power Query to get the result you need:

1. Select the Range- go to Data- From Table/Range:
   
2. Open Power Query editor- select the Link column- Go to Transform tab- Pivot Column- Select the CLICKS from Values Column list:
   
3. Go to Add Column tab- Select Custom Column option- enter the Custom column formula- Double Campaign1Url1 from Available columns list, symbol "+" and Double Campaign1Url2 from Available columns list:
   
4. Go to Transform tab- Unpivot Campaign1Url1 column and Campaign1Url2 column one by one (select Campaign1Url1 column and click Unpivot Columns then select Campaign1Url2 column and click Unpivot Columns):
   
5. You can change the Column manually- Go to Home tab- select Close and Load to...:
   

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,

Assuming there are only 2 URL's for each JMID and DLISTIDZ combination on each day, this M code in Power Query works.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Custom1 = Table.FromList(Table.Split(Source,2), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Table.AddIndexColumn([Column1],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"date", "JMID", "DLISTIDZ", "Link", "clicks", "Index"}, {"date", "JMID", "DLISTIDZ", "Link", "clicks", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Custom", {"Index", "date", "JMID", "DLISTIDZ"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-IN"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"date", type datetime}, {"JMID", Int64.Type}, {"DLISTIDZ", Int64.Type}, {"Link 1", type text}, {"clicks 1", Int64.Type}, {"Link 2", type text}, {"clicks 2", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Total clicks", each [clicks 1]+[clicks 2])
in
    #"Added Custom1"

Answer 4

Hi Nicholas,

I know it's too late for this reply, but I still hope you see it and find it useful. Below is a solution by a single formula. Apply it in cell G2.

=LET(d, A2:E15, rx, --REGEXEXTRACT(D2:D15, "\d+$"), ff, FILTER(d, rx = 1), sf, FILTER(d, rx = 2), HSTACK(ff, TAKE(sf,, -2), TAKE(ff,, -1) + TAKE(sf,, -1)))

Hope this helps.

Answer 5

Just my opinion is that a hard-coded key from a text link may not be the best option.

Here, just a few ending numbers were changed. By ignoring that, I believe it would be much more efficient (as in H11).