locked
converting a visual basics expression to power query (remove repeating strings) RRS feed

  • Question

  • I'm running a large line item sales report and want to be able to show number of unique orders. I am able to do this using visual basics using the expression below.

    Sub RemoveRepeatingStrings()

        Dim BaseStr As String, CurrStr As String
        Dim EndRow As Long

        EndRow = Range("E" & Rows.Count).End(xlUp).Row
        BaseStr = Range("E1").Value

        Application.ScreenUpdating = False

        For Iter = 2 To EndRow
            CurrStr = Range("E" & Iter).Value
            If CurrStr = BaseStr Then
                Range("E" & Iter).Value = vbNullString
            Else
                BaseStr = Range("E" & Iter).Value
            End If
        Next Iter

        Application.ScreenUpdating = True

    End Sub

    Is it possible to convert this to some sort of power query? The Visual Basics keeps freezing up Excel.

    Thanks,

    Jack

    Monday, December 7, 2015 9:24 PM

Answers

  • Hi Ehren, I was getting to that :), but trying to understand his data first. In VBA, there is a Range method that can be used to remove duplicates. For example, if there is no adjacent column data, then Jack can replace his code with:

    Sub RemoveDuplicates()

         Sheet1.Range("E1").CurrentRegion.RemoveDuplicates Columns:=1

     End Sub

    Where Sheet1 in the code name for the sheet (must be replaced by the actual name in Jack's scenario).  If there is adjacent data, then the code can be modified:

    Sub RemoveDuplicates()

         Dim EndRow As Long: EndRow = Sheet1.Range("E" & Rows.Count).End(xlUp).Row
         Sheet1.Range("E1:" & "E" & EndRow).RemoveDuplicates Columns:=1

     End Sub

    To summarize, there are three ways to remove duplicates in Excel:

    1) Select the column range containing the duplicate data, then choose Data-->Remove Duplicates in the Ribbon.

    2) Use the VBA code presented.

    3) Use the link that you provided to remove duplicates in Power Query.

    Tuesday, December 8, 2015 10:53 PM

All replies

  • Hi Jack,

    I would recommend that you declare all of your variables (Iter is not declared). Anyway, are there gaps in the data that you have in the E column, or is the range contiguous?


    Also, are there adjacent columns with data?
    Tuesday, December 8, 2015 8:50 PM
  • Hi Jack. Have you tried the Remove Duplicates operation?

    https://support.office.com/en-us/article/Remove-duplicates-Power-Query-d9cffc69-dc5d-4d94-8b66-72779688874d

    Ehren

    Tuesday, December 8, 2015 9:51 PM
  • Hi Ehren, I was getting to that :), but trying to understand his data first. In VBA, there is a Range method that can be used to remove duplicates. For example, if there is no adjacent column data, then Jack can replace his code with:

    Sub RemoveDuplicates()

         Sheet1.Range("E1").CurrentRegion.RemoveDuplicates Columns:=1

     End Sub

    Where Sheet1 in the code name for the sheet (must be replaced by the actual name in Jack's scenario).  If there is adjacent data, then the code can be modified:

    Sub RemoveDuplicates()

         Dim EndRow As Long: EndRow = Sheet1.Range("E" & Rows.Count).End(xlUp).Row
         Sheet1.Range("E1:" & "E" & EndRow).RemoveDuplicates Columns:=1

     End Sub

    To summarize, there are three ways to remove duplicates in Excel:

    1) Select the column range containing the duplicate data, then choose Data-->Remove Duplicates in the Ribbon.

    2) Use the VBA code presented.

    3) Use the link that you provided to remove duplicates in Power Query.

    Tuesday, December 8, 2015 10:53 PM
  • My goal is to just remove the duplicates from that column, but leave the remaining values in the same cell coordinates they are already located in. By doing that, I'll be able to do a "count" within a pivot table of that column that would give me the total # of orders within this line item level report. I still want all the other data that removing duplicates from the entire sheet would do. 

    Does that clarify what I'm trying to accomplish?

    Tuesday, December 15, 2015 12:19 AM
  • Would creating a discount count measure for the column in Power Pivot satisfy your requirement?
    Tuesday, December 15, 2015 4:54 AM
  • I'm not quite sure what you mean by creating a discount count measure. Could you please clarify?

    Thanks,

    Tuesday, December 15, 2015 4:48 PM
  • Sorry, that should have read distinct count. :)
    Tuesday, December 15, 2015 4:59 PM
  • Hi Jack, 

    Is this still an issue or have any of the responses helped you to arrive at a solution?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, January 16, 2016 9:40 PM