Split and group values in excel

0 votes

Hello, I have a column of values with various suffixes after a dot. I want to group the values according to the value after the dot. For instance, I need to separate all values that finish in. one column in pdf and values in.xls as another column, etc, 

enter image description here

How to do this is my doubt.

Jan 22, 2023 in Others by Kithuzzz
• 38,010 points
323 views

1 answer to this question.

0 votes

variant using scripting.dictionary:

Sub test()

    Dim dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.comparemode = vbTextCompare
    
    Dim cl As Range
    
    For Each cl In [A1:A6]
        If Not dic.exists(Split(cl.Value, ".")(1)) Then
            dic.Add Split(cl.Value, ".")(1), cl.Value
        Else
            dic(Split(cl.Value, ".")(1)) = dic(Split(cl.Value, ".")(1)) & "|" & cl.Value
        End If
    Next cl
    
    Dim x%, i%, dKey, sVal
    
    x = 3
    For Each dKey In dic
        i = 1
        For Each sVal In Split(dic(dKey), "|")
            Cells(i, x).Value = sVal
            i = i + 1
        Next sVal
        x = x + 1
    Next dKey
        
End Sub

Demo:

enter image description here

answered Jan 22, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Finding duplicate values in 5 excel sheets and results to be moved in the sixt one

See this article: VBA duplicated values coloring. I ...READ MORE

answered Feb 10, 2023 in Others by narikkadan
• 63,420 points
184 views
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
272 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
6,623 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,420 points
681 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
662 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,346 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,199 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
938 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,420 points
924 views
0 votes
1 answer

Converting Textboxes Link and/or Formula to Values in a Copied Sheet using Excel VBA

Copy the values using Range and Value2 With ActiveSheet.UsedRange ...READ MORE

answered Jan 31, 2023 in Others by narikkadan
• 63,420 points
328 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP