I'm trying to dynamic align column B to column A, but by matching a substring, and not by matching the whole content of the cell.
I have two column, with filenames in each (all with the same format, basically: Show.name.SxxExx.optional.title.source.codec-group.extention), but they might be a bit different, so i want to align them by matching the SxxExx in them which will always be the same.
I have this formula that returns the correct file name from B if it finds the same substring in A, in the same row, new column
=LOOKUP(9.99999999999999E+307,SEARCH(List2,A1),SINGLES)
'SINGLES' is the list in B column (file names to return if the exact 'SxxExx' substring extracted from it is found in column A.
'List2' is a column with a simple MID formula to extract the SxxExx number from column B file names (although i know it can be done by regex somehow and also have the correct regex, i just don't know what to do with it (it's "\.s[0-9][0-9]E[0-9][0-9]\." )
However this formula does not align them dynamicly in the same column they are already (column B).
I found this VB code (written by MickG here - thanks for that MickG!) that aligns by matching the whole string BUT will work only a cell in column B is EXACT as one in column A. what i need is to change it so it will align if only the SxxExx substring match (not case sensitive).
so i want to kind of merge, if you will, these two , into one vba code, and align B to A by matching the SxxExx substring. (xx can be any number, 2 digits)
I googled to death but i just don't have the knowledge to do it.
align by substring.xls
o and im using win-7, excel 2007, if it matters.
Hoping someone here can help
I have two column, with filenames in each (all with the same format, basically: Show.name.SxxExx.optional.title.source.codec-group.extention), but they might be a bit different, so i want to align them by matching the SxxExx in them which will always be the same.
I have this formula that returns the correct file name from B if it finds the same substring in A, in the same row, new column
=LOOKUP(9.99999999999999E+307,SEARCH(List2,A1),SINGLES)
'SINGLES' is the list in B column (file names to return if the exact 'SxxExx' substring extracted from it is found in column A.
'List2' is a column with a simple MID formula to extract the SxxExx number from column B file names (although i know it can be done by regex somehow and also have the correct regex, i just don't know what to do with it (it's "\.s[0-9][0-9]E[0-9][0-9]\." )
However this formula does not align them dynamicly in the same column they are already (column B).
I found this VB code (written by MickG here - thanks for that MickG!) that aligns by matching the whole string BUT will work only a cell in column B is EXACT as one in column A. what i need is to change it so it will align if only the SxxExx substring match (not case sensitive).
Code:
Sub MG17Sep38
Dim Rng1 As Range, Rng2 As Range, Dn As Range
Dim n As Long, RngAll, Ray
Set Rng1 = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Set Rng2 = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
Set RngAll = Union(Rng1, Rng2)
ReDim Ray(1 To Rows.Count, 1 To 2)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In RngAll
If Not .Exists(Dn.Value) Then
n = n + 1
.Add Dn.Value, n
If Dn.Column = 1 Then
Ray(n, 1) = Dn.Value
Else
Ray(n, 2) = Dn.Value
End If
Else
If Dn.Column = 1 Then
Ray(.Item(Dn.Value), 1) = Dn.Value
Else
Ray(.Item(Dn.Value), 2) = Dn.Value
End If
End If
Next
RngAll.ClearContents
Range("A1").Resize(.Count, 2) = Ray
End With
End Subso i want to kind of merge, if you will, these two , into one vba code, and align B to A by matching the SxxExx substring. (xx can be any number, 2 digits)
I googled to death but i just don't have the knowledge to do it.
align by substring.xls
o and im using win-7, excel 2007, if it matters.
Hoping someone here can help
Last edited:
