Dilep Dev
Dilep Dev

Reputation: 93

Formula to move/shift items in a 2d-table, so that they are in same rows of the matching 1d-table items?

I have a table Google Sheet with more than 100 rows and columns. Each column is alphabetically sorted, The key table/column contains all the unique items in the table (also sorted alphabetically). Each item in the table has a unique URL attached to it (col1 item A has a different URL from col2 item A). Note: key table/column items have no URLs attached

I need to move these items to the matching key table item rows, while keeping their columns are unchanged. Is there a way to do this using a single Gsheet formula (or 2, a easier way). The changes don't need to be done on the table itself.

current and required table formats

<style>
  table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
  }
  
  td,
  th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
  }
  
  tr:nth-child(even) {
    background-color: #dddddd;
  }
</style>

<h3>Current Table</h3>
<table>
  <tr>
    <th>Key</th>
    <th>col 1</th>
    <th>col 2</th>
    <th>col 3</th>
    <th>col 4</th>
    <th>col N</th>
  </tr>
  <tr>
    <td>
      <B>A</B>
    </td>
    <td>A</td>
    <td>A</td>
    <td>B</td>
    <td>A</td>
    <td>C</td>
  </tr>
  <tr>
    <td>
      <B>B</B>
    </td>
    <td>B</td>
    <td>D</td>
    <td>E</td>
    <td>D</td>
    <td>E</td>
  </tr>
  <tr>
    <td>
      <B>C</B>
    </td>
    <td>C</td>
    <td>E</td>
    <td>G</td>
    <td>G</td>
    <td>F</td>
  </tr>
  <tr>
    <td>
      <B>D</B>
    </td>
    <td>D</td>
    <td>G</td>
    <td></td>
    <td>H</td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>E</B>
    </td>
    <td>F</td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>F</B>
    </td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>G</B>
    </td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>H</B>
    </td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
</table>

<br>

<h3>Required Table</h3>
<table>
  <tr>
    <th>Key</th>
    <th>col 1</th>
    <th>col 2</th>
    <th>col 3</th>
    <th>col 4</th>
    <th>col N</th>
  </tr>
  <tr>
    <td>
      <B>A</B>
    </td>
    <td>A</td>
    <td>A</td>
    <td></td>
    <td>A</td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>B</B>
    </td>
    <td>B</td>
    <td></td>
    <td>B</td>
    <td></td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>C</B>
    </td>
    <td>C</td>
    <td></td>
    <td></td>
    <td></td>
    <td>C</td>
  </tr>
  <tr>
    <td>
      <B>D</B>
    </td>
    <td>D</td>
    <td>D</td>
    <td></td>
    <td>D</td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>E</B>
    </td>
    <td></td>
    <td>E</td>
    <td>E</td>
    <td></td>
    <td>E</td>
  </tr>
  <tr>
    <td>
      <B>F</B>
    </td>
    <td>F</td>
    <td></td>
    <td></td>
    <td></td>
    <td>F</td>
  </tr>
  <tr>
    <td>
      <B>G</B>
    </td>
    <td></td>
    <td>G</td>
    <td>G</td>
    <td>G</td>
    <td></td>
  </tr>
  <tr>
    <td>
      <B>H</B>
    </td>
    <td></td>
    <td></td>
    <td></td>
    <td>H</td>
    <td></td>
  </tr>
</table>

Upvotes: 1

Views: 74

Answers (2)

kishkin
kishkin

Reputation: 5325

Here you go:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      (ROW(H3:H10) - ROW(H3)) * COLUMNS(B3:F10) + (COLUMN(I3:M3) - COLUMN(I3)),
      {
        FLATTEN(
          IF(
            B3:F10 = "",
              "",
                (
                    VLOOKUP(
                      B3:F10,
                      {A3:A10, ROW(A3:A10)},
                      2,
                      0
                    )
                  - ROW(A3)
                )
              * COLUMNS(B3:F10)
              + COLUMN(B3:F10)
              - COLUMN(B3)
          )
        ),
        FLATTEN(B3:F10)
      },
      2,
      0
    )
  )
)

enter image description here

Upvotes: 0

basic
basic

Reputation: 11968

According your snippet you can use following formula:

=ArrayFormula(IF(COUNTIF(B$2:B$9,$A13:$A20),$A13:$A20,""))

enter image description here

Upvotes: 1

Related Questions