CODEWITHSUNDEEP
if-statementgoogle-sheetstransposegoogle-sheets-formulaarray-formulas
Evgeniy
Evgeniy

Reputation: 2595

How to transpose into the "same" column?

I have a table like

<table class="tg">
  <tr>
    <th class="tg-0lax">date</th>
    <th class="tg-0lax">organic</th>
    <th class="tg-0lax">referrer</th>
    <th class="tg-0lax">direct</th>
  </tr>
  <tr>
    <td class="tg-0lax">01.01.2019</td>
    <td class="tg-0lax">12345</td>
    <td class="tg-0lax">123</td>
    <td class="tg-0lax">23</td>
  </tr>
  <tr>
    <td class="tg-0lax">25.01.2019</td>
    <td class="tg-0lax">23456</td>
    <td class="tg-0lax">234</td>
    <td class="tg-0lax">34</td>
  </tr>
  <tr>
    <td class="tg-0lax">03.03.2019</td>
    <td class="tg-0lax">34567</td>
    <td class="tg-0lax">345</td>
    <td class="tg-0lax">56</td>
  </tr>
  <tr>
    <td class="tg-0lax">15.04.2019</td>
    <td class="tg-0lax">45678</td>
    <td class="tg-0lax">456</td>
    <td class="tg-0lax">78</td>
  </tr>
</table>

I want to get the data into this view, where all data are placed into the same three columns, not dependently of whether points in the first column are repeated:

<table class="tg">
  <tr>
    <th class="tg-0lax">type</th>
    <th class="tg-0lax">source</th>
    <th class="tg-0lax">date</th>
  </tr>
  <tr>
    <td class="tg-0lax">organic</td>
    <td class="tg-0lax">12345</td>
    <td class="tg-0lax">01.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">referrer</td>
    <td class="tg-0lax">123</td>
    <td class="tg-0lax">01.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">direct</td>
    <td class="tg-0lax">23</td>
    <td class="tg-0lax">01.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">organic</td>
    <td class="tg-0lax">23456</td>
    <td class="tg-0lax">25.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">referrer</td>
    <td class="tg-0lax">234</td>
    <td class="tg-0lax">25.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">direct</td>
    <td class="tg-0lax">34</td>
    <td class="tg-0lax">25.01.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">organic</td>
    <td class="tg-0lax">34567</td>
    <td class="tg-0lax">03.03.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">referrer</td>
    <td class="tg-0lax">345</td>
    <td class="tg-0lax">03.03.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">direct</td>
    <td class="tg-0lax">56</td>
    <td class="tg-0lax">03.03.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">organic</td>
    <td class="tg-0lax">45678</td>
    <td class="tg-0lax">15.04.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">referrer</td>
    <td class="tg-0lax">456</td>
    <td class="tg-0lax">15.04.2019</td>
  </tr>
  <tr>
    <td class="tg-0lax">direct</td>
    <td class="tg-0lax">78</td>
    <td class="tg-0lax">15.04.2019</td>
  </tr>
</table>

The "normal" transposing is pretty close to what I want, but even not exactly this, and I miss the point, how to pivot the data.

Another one example:

enter image description here

Got an error: enter image description here

What I'm doing wrong? The formula is:

=ARRAYFORMULA({"type","source","date";SPLIT(TRANSPOSE(SPLIT(CONCATENATE(IF(B2:D<>"","♠"&B1:D1&"♦"&B2:D&"♦"&A2:A, )),"♠")),"♦")}) The line breaks are from the formula away - I've deleted them. Could the error cause be that my Google Spreadsheets used in Germany - formula language issue?

Upvotes: 0

Views: 48

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA({"type", "source", "date";
 SPLIT(TRANSPOSE(SPLIT(CONCATENATE(IF(B2:D<>"", 
 "♠"&B1:D1&"♦"&B2:D&"♦"&A2:A, )), "♠")), "♦")})

0

Upvotes: 1

Related Questions