Extract Common Text

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
I have a folder in which I retain downloaded teaching aids that are either xlsm or xlsx; and YouTube & website links as well as various documents related to the reason for the folder name.
Following is the folder name including the sub-folder names.
Main folder: SpecialExcelSheets
Sub-folders as follows:
AdvancedFiltering
AUTO-Fill PDF Forms
AutoSetTabOrderSaveLoadData
Calendars
Financial-ExpenseTracking
Find Date Get Row Number
Horizontal-VerticalTabs
HyperlinkFromDropDown
Miscellaneous
Misc-Websites
MultipleDropDownLists
Numbers2Words
Range-CellAddress-VLookUp-Text
RegexFormula
RGB-Colors
Security
SlideOutMenu

This list of sub-folders is likely to change, as in additional reasons for new folder names depending on some specific function etcetera.
Therefore I want to extract from the list in column B (acquired from Power Query) the sub-folders' name and have that appear in one cell, but not as I have shown in the merged cells that follow from column B.
Upon completion of this, I will have columns A & B hidden, and then any time I refresh the Power Query the changes show up on this sheet, including in addition to a new sub-folder but also a sub-folder name that was changed for whatever reason.
Forgot to mention. When I did the Power Query and edited the sheet I removed all columns showing dates and file names. I am not interested in the file names but only the folder names.
This worksheet includes a tab for each sub-folder where I have links to the various excel sheets, websites, and YouTubes associated with the specific sub-folder name.
ConsolidatedListSpecialExcel.xlsm
ABC
1IndexFile Name
21F:\Finances\CSOB2\SpecialExcelSheets\
32F:\Finances\CSOB2\SpecialExcelSheets\
43F:\Finances\CSOB2\SpecialExcelSheets\
54F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\AdvancedFiltering
65F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
76F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
87F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
98F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
109F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1110F:\Finances\CSOB2\SpecialExcelSheets\AdvancedFiltering\
1211F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\AUTO-Fill PDF Forms
1312F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1413F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1514F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1615F:\Finances\CSOB2\SpecialExcelSheets\AUTO-Fill PDF Forms\
1716F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\AutoSetTabOrderSaveLoadData\
1817F:\Finances\CSOB2\SpecialExcelSheets\AutoSetTabOrderSaveLoadData\
1918F:\Finances\CSOB2\SpecialExcelSheets\Calendars\Calendars
2019F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2120F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2221F:\Finances\CSOB2\SpecialExcelSheets\Calendars\
2322F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\Financial-ExpenseTracking\
2423F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2524F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2625F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2726F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2827F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
2928F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3029F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3130F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3231F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3332F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3433F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3534F:\Finances\CSOB2\SpecialExcelSheets\Financial-ExpenseTracking\
3635F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3736F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3837F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
3938F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4039F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4140F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4241F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4342F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4443F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4544F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4645F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4746F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4847F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
4948F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
5049F:\Finances\CSOB2\SpecialExcelSheets\Find Date Get Row Number\
FNames-Links
Cell Formulas
RangeFormula
A2:B50A2=VLOOKUP(FolderNames!A5,FolderNames!A:A,COLUMN(FolderNames!A:A)-COLUMN(FolderNames!A:A)+1,0)
Named Ranges
NameRefers ToCells
FolderNames!ExternalData_1=FolderNames!$A$4:$B$163A2:A50
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
So the combined code would be as follows:?

VBA Code:
let fxNewPaths = (t as table, i as number) =>
let
iActual = “L” & Text.From(i),
iNext = “L” & Text.From(i+1),
AddCol1 = Table.AddColumn(t, “Lx”, each try Table.SelectColumns(Table.AddColumn(Table.SelectRows(Folder.Contents(Record.Field(_, iActual)), each [Content] is table)[[Folder Path],[Name]], iNext, each [Folder Path] & [Name]), {iNext}) otherwise null),
Expand = Table.ExpandTableColumn(AddCol1, “Lx”, {iNext})
in
Expand,
FPath = Excel.CurrentWorkbook(){[Name=“FPath”]}[Content]{0}[Column1],
FirstRow = Table.FromColumns({{FPath}}, {“L0”}),
LiG = List.Generate(() => [FR = FirstRow, i = 0],
each List.NonNullCount( Table.Column([FR], “L”& Text.From([i]))) <> 0,
each [FR = fxNewPaths([FR], [i]), i = [i]+1],
each [FR]),
TBL = List.Last(LiG),
#”Unpivoted Columns” = Table.UnpivotOtherColumns(TBL, {}, “Attribute”, “Folder Path”),
#”Removed Columns” = Table.RemoveColumns(#”Unpivoted Columns”,{“Attribute”}),
#”Removed Duplicates” = Table.Distinct(#”Removed Columns”),
#”Sorted Rows” = Table.Sort(#”Removed Duplicates”,{{“Folder Path”, Order.Ascending}})
in
#”Sorted Rows”
#”Inserted Text After Delimiter” = Table.AddColumn(#”Sorted Rows”, “Text After Delimiter”, each Text.AfterDelimiter([Folder Path], “\”, {0, RelativePosition.FromEnd}), type text)
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
Power Query:
let    fxNewPaths = (t as table, i as number) =>
        let
            iActual = "L" & Text.From(i),
            iNext = "L" & Text.From(i+1),
            AddCol1 = Table.AddColumn(t, "Lx", each try Table.SelectColumns(Table.AddColumn(Table.SelectRows(Folder.Contents(Record.Field(_, iActual)), each [Content] is table)[[Folder Path],[Name]], iNext, each [Folder Path] & [Name]), {iNext}) otherwise null),
            Expand = Table.ExpandTableColumn(AddCol1, "Lx", {iNext})
        in
            Expand,


    FPath = Excel.CurrentWorkbook(){[Name="FPath"]}[Content]{0}[Column1],
    FirstRow = Table.FromColumns({{FPath}}, {"L0"}),
    LiG = List.Generate(() => [FR = FirstRow, i = 0],
                    each List.NonNullCount( Table.Column([FR], "L"& Text.From([i]))) <> 0,
                    each [FR = fxNewPaths([FR], [i]), i = [i]+1],
                    each [FR]),
    TBL = List.Last(LiG),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(TBL, {}, "Attribute", "Folder Path"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Folder Path", Order.Ascending}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Sorted Rows", "Text After Delimiter", each Text.AfterDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}), type text)
in
    #"Inserted Text After Delimiter"

but I still suggest From Folder option if you want filenames
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Awesome help Sandy, thanks loads.
I will give this a shot someday when the need presents itself.
This code is copied and inserted into my Word doc for this subject.
One final question. You labeled this code with "Power Query:"
Am I to assume this code is to be inserted in some location during a power query edit session, such as indicated by these images:
Probably in the Advanced Editor?
1597921755085.png

1597921772129.png

1597921783721.png
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
use Alt A PN O Q then Alt H Q
this is a place for the code

btw. pictures are too small to recognise something there;)
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Original images now inserted larger, are they any better?
I tried this use Alt A PN O Q then Alt H Q but it does not seem to get me to a place I recognize.
images follow of what I experienced doing this use Alt A PN O Q then Alt H Q
After Alt A PN I get this
1597923023290.png

After Alt A I get this
1597923165485.png

There is no PN
After Alt A then P nothing happens (probably is supposed to call up Power Query, but mu version of Excel is 2013).
This is where I think I should end up to insert the code:
1597923353976.png


1597922673029.png
 

Attachments

  • 1597922606678.png
    1597922606678.png
    64.8 KB · Views: 1

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
ops, 2013 with PQ add-in :eek:
so you need blank query, open advanced editor then replace code there with code copied from the post

btw. update your profile (Account Details) about Excel version
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
165
Office Version
  1. 2013
Platform
  1. Windows
Okay, now makes perfect sense
ops, 2013 with PQ add-in :eek:
so you need blank query, open advanced editor then replace code there with code copied from the post

btw. update your profile (Account Details) about Excel version
Profile updated, and then some.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
if you update Office to newer version some things will change
Thanks for updating profile
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top