Macro to delete certain items then count remaining items #2

**elgato74** · 12-21-2019, 01:18 PM

Dear All,

please have a look at the attached file

In column A, I have 30,000+ entries. You will notice that there are items with names and items with 3-Letter codes + suffix such as STO-1K, DTS-Pano and so on.

I am trying to come up with a macro that does the following:

1. Delete all cells with 3-Letter Codes + Suffix
2. So that only names remain
3. Count those, e.g. Pacino = 446 times

I hope the attached file shows the mission...

Thanks muchly!

**PCI** · 12-21-2019, 01:27 PM

What about Macro to delete 3-Letter Codes + Suffix and a Pivot Table ...?
If it is OK then is needed a header ...!

**elgato74** · 12-21-2019, 01:30 PM

sure, looking forward to your solution

**Marc L** · 12-21-2019, 01:43 PM

H,

can be achieved as it is without any header, I think first a while before posting any entry level code …

**elgato74** · 12-21-2019, 01:48 PM

Originally Posted by Marc L

H,

can be achieved as it is without any header, I think first a while before posting any entry level code …

You wanna help or just be smartypants?

**Marc L** · 12-21-2019, 01:53 PM

First do not quote all previous post, that just clutters ! Thanks.
I do not understand your word but just well reading again my post this time
you must understand it was first an advise it can be achieved as it is without any header and I'm thinking about it ‼

**elgato74** · 12-21-2019, 01:53 PM

Originally Posted by Marc L

First do not quote all previous post, that just clutter ! Thanks.
I do not understand your word but just well reading again my post this time
you must understand it was first an advise it can be achieved without as it is without any header and I'm thinking about it ‼

blah blah blah farewell dude

**Marc L** · 12-21-2019, 01:56 PM

Ok I won't waste time anymore for an already existing easy fast way, good luck …

**PCI** · 12-21-2019, 01:58 PM

Here a possibility, but take care of the header

Option Explicit


Sub Treat()
Dim I  As Integer, II  As Integer, LR As Integer
Dim WkAr1
Dim WkAr2

    LR = Cells(Rows.Count, 1).End(3).Row
    WkAr1 = Range(Cells(1, 1), Cells(LR, 1))
    ReDim WkAr2(1 To LR)
    For I = 1 To UBound(WkAr1, 1)
        If Not (Len(Split(WkAr1(I, 1), "-")(0)) = 3) Then
            II = II + 1
            WkAr2(II) = WkAr1(I, 1)
        End If
    
    Next I
    Cells(1, 1).Resize(LR, 1) = Application.Transpose(WkAr2)
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

**elgato74** · 12-21-2019, 02:04 PM

Wow, that looks great! Thank you. But pardon, how would I sort by "Count of Name" ?

**mikerickson** · 12-21-2019, 02:10 PM

To count the entries, you could use =COUNTIF(A:A, "*"&B1&"*). No need to eliminate the coded rows.

But, I think that your sample results are off, I can only find one entry for "R8 V5 3P", cell A4, but your sample results list 4. (similar for "Holidays")
If I am wrong, what are the other three cells that you consider to be matching "R8 V5 3P".

**PCI** · 12-21-2019, 02:13 PM

how would I sort by "Count of Name" ?

Left click on a name to select name field
Right click > Sort > Sort A to Z

In a general manner see the use of Pivot Table, it is very useful

**elgato74** · 12-21-2019, 02:16 PM

Perfect, thank you and Cheers to the Alsace, Merry Xmas

**PCI** · 12-21-2019, 02:26 PM

You're welcome
Merry Christmas to

Macro to delete certain items then count remaining items #2

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Re: Macro to delete certain items then count remaining items #2

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to delete certain items then count remaining items

Need to get certain line items so that the remaining line items are below threshold

[SOLVED] Calculate remaining items

[SOLVED] Vlookup with remaining items populated below

[SOLVED] Need to count items in column that match multiple data items

Count of Items that are Greater than the Average of those Items

Ascending count of same items in a column containing multiple items

Bookmarks

Bookmarks

Posting Permissions