Reputation: 93
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
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
)
)
)
Upvotes: 0
Reputation: 11968
According your snippet you can use following formula:
=ArrayFormula(IF(COUNTIF(B$2:B$9,$A13:$A20),$A13:$A20,""))
Upvotes: 1