SOLVED
Home

Bring the lines in the column one level up

Sam55
Occasional Contributor

I have run a dfsview command and I have the list of my DFS shares names like below:

Column1                                                                  Column2

\\contoso.com\testDFS\example       

                                                                                   \\server1\example

 

The colum2 data is one line down than column1 data. I have thousands of lines like this.

How can I bring the contents of Column2 one line above so that the contents of column1 and column2 appear on the same line?

5 Replies

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.

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.

Solution

When bit more steps.

1) Delete cell B1 with shifting other cells up

2) Select column B, Find & Select->Go To Special -> Blanks

image.png

3) Delete->Delete Sheet Rows

image.png

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.

image.png

6) Press Ctrl+Enter

Result as here

image.png

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies