killorb
09-08-2017, 08:06 AM
I have 8,000 lines (maybe more) that I would like to sort and consolidate down to only the unique line, and maybe indicate how many instances of the lines have consolidated.
/RandomText/RandomText/RandomText/RandomText/RandomText/RandomText/nciDAHtSP
The string length could be shorter or longer. I am only concerned with the text after the last / (foward slash). That text could any length and text and/or numbers.
mdmackillop
09-08-2017, 08:32 AM
Can you post a sample workbook showing data ane expected outcome. Go Advanced / Manage Attachments
killorb
09-08-2017, 08:53 AM
Thanks added attachment.
The outcome would be stripping out the data before the last forward slash.
Sort all the unique name (after the last slash).
Count all the instances per each unique name.
Consolidate the unique names
Show all the unique names and how many instances of the name was consolidated down.
offthelip
09-08-2017, 09:54 AM
try this:
Sub test()
Dim strg As String
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 2 To lastrow
strg = inarr(i, 1)
slashp = InStrRev(strg, "/")
nam = Mid(strg, slashp + 1)
inarr(i, 1) = nam
Next i
Range(Cells(1, 2), Cells(lastrow, 2)) = inarr
Set myrange = Range(Cells(1, 2), Cells(lastrow, 2))
Set Sortkey = Range(Cells(1, 2), Cells(lastrow, 2))
myrange.Sort key1:=Sortkey, order1:=xlAscending, MatchCase:=False, Header:=xlYes
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
Range(Cells(1, 2), Cells(lastrow, 3)) = ""
outarr = Range(Cells(1, 2), Cells(lastrow, 3))
cstrg = ""
cnt = 0
indi = 1
For i = 2 To lastrow
If cstrg = inarr(i, 1) Then
cnt = cnt + 1
Else
indi = indi + 1
outarr(indi, 1) = cstrg
outarr(indi, 2) = cnt
cstrg = inarr(i, 1)
cnt = 1
End If
Next i
indi = indi + 1
outarr(indi, 1) = cstrg
outarr(indi, 2) = cnt
Range(Cells(1, 2), Cells(lastrow, 3)) = outarr
End Sub
killorb
09-28-2017, 06:08 AM
Sorry the delay. This is exactly what I needed. Thank you!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.