CODEWITHSUNDEEP
vbaexcel
monika singh
monika singh

Reputation: 100

Macro for multiline text

I have an excel sheet where in one column Header name 'LOTZ' multiline goods name are listed in single cell (goods quantity changes e.g day 1 have 7 goods & day 2 may have 1 good). I need to create Top 50 Goods in entire year with respective count in separate sheet.

Example :-

enter image description here

Result I want in new inserted sheet:-

enter image description here

i am able to use DATA tab 'text to column' but don't know how to consolidate via macro code.

any help will be highly appreciated.

thanks monika

Upvotes: 0

Views: 95

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Try this:

Sub consolidate()
    Dim r As Range, dict As Object, lotz
    Set dict = CreateObject("Scripting.Dictionary")
    For Each r In Worksheets("Sheet1").UsedRange.Columns("C").Offset(1).Cells
        For Each lotz In Split(r.value, vbLf)
            dict(lotz) = dict(lotz) + 1
        Next
    Next
    With Worksheets.Add
        .Range("A1:B1").value = Array("LOTZ", "Count")
        .Range("A2:A" & dict.Count + 1).value = Application.Transpose(dict.Keys)
        .Range("B2:B" & dict.Count + 1).value = Application.Transpose(dict.Items)
        .Range("A2:B" & dict.Count + 1).Sort .Range("B2"), xlDescending
    End With
End Sub

EDIT

If the column of lotz is not known and you want to find its header in row 1, you can use this instead:

For Each r In Worksheets("Sheet1").UsedRange.Find("LOTZ").EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1)

Upvotes: 1

Related Questions