PDA

View Full Version : Sort Text and Consolidate



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!