Forum Discussion
Bring the lines in the column one level up
When bit more steps.
1) Delete cell B1 with shifting other cells up
2) Select column B, Find & Select->Go To Special -> Blanks
3) Delete->Delete Sheet Rows
4) 2) Select column A and B, Find & Select->Go To Special -> Blanks
5) In formula bar type
=HYPERLINK(A1)
if A2 is our first blank cell. That shall be a link on the cell previous for first non-blank one.
6) Press Ctrl+Enter
Result as here
The actual file is something like this:
There could be two values in second column corresponding to one value in the first column:
| \\contoso.com\testDFS\example | |
| \\server1\example | |
| \\server2\example | |
| \\contoso.com\testDFS\example1 | |
| \\server1\example1 | |
| \\contoso.com\testDFS\example2 | |
| \\server3\example2 | |
| \\contoso.com\testDFS\example3 | |
| \\server2\example3 | |
| \\contoso.com\testDFS\example4 | |
| \\server3\example4 | |
| \\contoso.com\testDFS\example5 | |
| \\server3\example4 |
I want the end result to look like this:
| \\contoso.com\testDFS\example | \\server1\example |
| \\contoso.com\testDFS\example | \\server2\example |
| \\contoso.com\testDFS\example1 | \\server1\example1 |
| \\contoso.com\testDFS\example2 | \\server3\example2 |
| \\contoso.com\testDFS\example3 | \\server2\example3 |
| \\contoso.com\testDFS\example4 | \\server3\example4 |
| \\contoso.com\testDFS\example5 | \\server3\example4 |
Attaching the file.
When bit more steps.
1) Delete cell B1 with shifting other cells up
2) Select column B, Find & Select->Go To Special -> Blanks
3) Delete->Delete Sheet Rows
4) 2) Select column A and B, Find & Select->Go To Special -> Blanks
5) In formula bar type
=HYPERLINK(A1)
if A2 is our first blank cell. That shall be a link on the cell previous for first non-blank one.
6) Press Ctrl+Enter
Result as here
Thanks it is working fine. However where the data in the sheet ends, i.e. the last row, it has copied the same value in hundreds of rows like below where there was no data before:
\\contoso.com\testDFS\example5 \\server3\example4 There were say 1000 rows and it has copied the same value as shown above till row: 1792
That means your cells below the data are not actually blank, most probably it was some data below which deleted. If you press Ctrl+End you'll be positioned somewhere in row 1792.
Two ways to avoid.
First is to clean such cells. You may check if you have Inquiry (File->Options->Add-ins->COM add-ins (at the bottom)->Go) and activate it. In Inquiry tab on the ribbon click on Clean Excess Cell Formatting.
Alternatively you may add one more step - select not entire column but the range where is your data, let say B1:B20. Manually or by Find & Select->Go To->type in Reference B1:B20->Ok. After that Find & Select->Go To Special->Blanks, etc. as before.