Forum Discussion
Sam55
Nov 30, 2018Copper Contributor
Bring the lines in the column one level up
I have run a dfsview command and I have the list of my DFS shares names like below: Column1 Column2 \\contoso.com\testDFS\example ...
- Dec 01, 2018
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
SergeiBaklan
Nov 30, 2018MVP
You may delete first cell in second column with shifting cells up. After that you have empty every second row. To remove them select both columns, when Home->Filter&Select->Go to Special. Here select Blanks->Ok. After that Home->Delete->Delete Sheet Rows.
Play on the copy of your data first.
- Sam55Dec 01, 2018Copper Contributor
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.
- SergeiBaklanDec 01, 2018MVP
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
- Sam55Dec 01, 2018Copper Contributor
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