By -

To convert a column of folder paths into a tree structure of rows, you can use a combination of Excel formulas and VBA code. Here are the steps to follow:

  1. Insert a new column to the right of the column with the folder paths.
  2. In the first cell of the new column, enter the formula =LEN(A1)-LEN(SUBSTITUTE(A1,"\","")), where A1 is the first cell in the column with the folder paths. This formula calculates the number of backslashes in the folder path.
  3. Copy the formula down the entire column to calculate the number of backslashes in each folder path.
  4. Insert a new sheet in the workbook and name it "Tree".
  5. In the "Tree" sheet, create headers for the columns "Level 1", "Level 2", "Level 3", etc. up to the maximum number of levels in your folder paths. For example, if your folder paths have a maximum of 3 levels, create columns for "Level 1", "Level 2", and "Level 3".
  6. In the first cell of each column, enter the corresponding level number. For example, in cell A1, enter "1" for Level 1.
  7. In cell A2 of the "Tree" sheet, enter the formula =LEFT(Sheet1!A2,FIND("|",SUBSTITUTE(Sheet1!A2,"\","|",B2))-1), where Sheet1!A2 is the first cell in the column with the folder paths, and B2 is the level number in the "Tree" sheet. This formula extracts the first level of the folder path.
  8. Copy the formula across the columns for all levels of the folder path.
  9. Copy the formulas down for all rows in the "Tree" sheet to create a tree structure of rows for all folder paths.

Note: To make this process more automated, you can create a VBA macro that loops through the column with the folder paths and populates the "Tree" sheet with the appropriate level values.

Anandan is a SharePoint and Microsoft Azure consultant.He is having 9+ years of experience in the web, cloud, Machine Learning, IoT and mobile technology. He handled multi-platform solution as a consultant with the experience of designing, developing and deploying solutions by leveraging Microsoft, IBM, Apple, and Google technology stack, has played a dynamic role in the development of SharePoint and ML technologies. He writes articles related to SharePoint Online/On-Premises and Machine Learning.

Comments

Post a Comment

Popular posts from this blog

Sharepoint 2013-Minimal Download Strategy.

By -
Image
You might seen the page URL in sharepoint 2013 looks like below. http://sp201323:8085/sites/TeamSite/_layouts/15/start.aspx #SitePages/Home.aspx In the above URL, you are looking for home.aspx to load.But the URL targets start.aspx .Do you know what is the reason behind this? This is nothing but Minimal Download Strategy Feature. Minimal Download Strategy (MDS) is a new technology in SharePoint 2013 that reduces the amount of data that the browser has to download when users navigate from one page to another in a SharePoint site. Benefits of using MDS Using MDS provides several benefits, including: Speed:   Smooth transitions: Browser navigation controls:   Other AJAX-based systems confuse the   previous   and   next   buttons in browsers. Because MDS updates the URL in the browser window, the previous and next buttons work just as they are supposed to. Backward compatibility:   The MDS engine either provides MDS navigation immediately or detects when i
Read more

Bulk update and delete using SPservice in SharePoint

By -
$().SPServices.SPUpdateMultipleListItems({ listName: "States" , CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Active</Value></Eq></Where></Query>" , valuepairs: [["Status", "Inactive"]] }); This call to SPUpdateMultipleListItems will update all of the items in the States list which have their Status = "Active", setting their Status = "Inactive". $().SPServices.SPUpdateMultipleListItems({ listName: "States" , CAMLQuery: "<Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Inactive</Value></Eq></Where></Query>" , batchCmd: "Delete" }); This call to SPUpdateMultipleListItems will delete all of the items in the States list which have their Status = "Inactive".
Read more