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 in Others by Kithuzzz
• 27,740 points
32 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 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

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

I'm copying a sheet to another workbook ...READ MORE

8 hours ago in Others by Kithuzzz
• 27,740 points
12 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
• 51,240 points
136 views
0 votes
1 answer

Excel - add all X in column and then multiply

Use countif for the count then multiply ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 51,240 points
75 views
0 votes
1 answer

Quadratic and cubic regression in Excel

You need to use an undocumented trick ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 51,240 points
199 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,640 points
165 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
• 51,240 points
254 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
• 51,240 points
172 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
• 51,240 points
255 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
• 51,240 points
177 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
• 51,240 points
1,046 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