Hi,
I'm new here, but I'm hoping you can help me with the following problem.
Example table:
<tbody>
</tbody>
What I would like to do:
Extract rows where the cell in column A contains exactly 5 backslashes to another worksheet. In the example that would be rows 2, 6 and 9.
The array formula that I have so far:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))=5,INDEX(A$2:A$11,SMALL(IF(LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))=5,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(Sheet2!A$2:A2))),"")
Problem:
The rows are extracted, but have gaps between them. I would like to see:
<tbody>
</tbody>
Instead of what I get now:
<tbody>
</tbody>
Question:
How can I adjust my formula so that the extracted rows appear as in my second example?
Thank you in advance for any assistance.
All the best,
Sander
I'm new here, but I'm hoping you can help me with the following problem.
Example table:
A | B | C | D | |
1 | Directory | Size (MB) | Files | Folders |
2 | \\example\home\user1\ | 12 | 20 | 3 |
3 | \\example\home\user1\A\ | 2 | 2 | 0 |
4 | \\example\home\user1\B\ | 5 | 9 | 0 |
5 | \\example\home\user1\C\ | 5 | 9 | 0 |
6 | \\example\home\user2\ | 10 | 30 | 2 |
7 | \\example\home\user2\A\ | 6 | 20 | 0 |
8 | \\example\home\user2\B\ | 4 | 10 | 0 |
9 | \\example\home\user3\ | 20 | 50 | 2 |
10 | \\example\home\user3\A\ | 12 | 10 | 0 |
11 | \\example\home\user3\B\ | 8 | 40 | 0 |
<tbody>
</tbody>
What I would like to do:
Extract rows where the cell in column A contains exactly 5 backslashes to another worksheet. In the example that would be rows 2, 6 and 9.
The array formula that I have so far:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))=5,INDEX(A$2:A$11,SMALL(IF(LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))=5,ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(Sheet2!A$2:A2))),"")
Problem:
The rows are extracted, but have gaps between them. I would like to see:
A | B | C | D | |
1 | Directory | Size (MB) | Files | Folders |
2 | \\example\home\user1\ | 12 | 20 | 3 |
3 | \\example\home\user2\ | 10 | 30 | 2 |
4 | \\example\home\user3\ | 20 | 50 | 2 |
<tbody>
</tbody>
Instead of what I get now:
A | B | C | D | |
1 | Directory | Size (MB) | Files | Folders |
2 | \\example\home\user1\ | 12 | 20 | 3 |
3 | ||||
4 | ||||
5 | ||||
6 | \\example\home\user2\ | 10 | 30 | 2 |
7 | ||||
8 | ||||
9 | \\example\home\user3\ | 20 | 50 | 2 |
10 | ||||
11 |
<tbody>
</tbody>
Question:
How can I adjust my formula so that the extracted rows appear as in my second example?
Thank you in advance for any assistance.
All the best,
Sander