Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Help Needed - Excel data and Running queries

  1. #1

    Help Needed - Excel data and Running queries



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, all

    I can't use access or any other SQL tools...need to develop this in Excel only.

    I am new to the Excel VBA programming. I have an excel sheet (single worksheet) with data. The data is getting refreshed from sharepoint list. I am using 2010 version of Excel.

    Once the data is refreshed, I would like to run some queries on the refreshed worksheet.

    for example, the excel sheet has the following columns

    Package id
    Date1
    Date2
    Date3
    ingredient
    ingredient type


    I am planning to have queries that can run on this data in excel to provide a subset of rows that meets the criteria... The query results must be stored in new worksheet in the same document.

    1. to find the duplicate ingredients by ingredient

    attached the test data and expected results in different tab. appreciate your help.

    Either macro or vba code help.
    Attached Files Attached Files

  2. #2
    Code:
    Public Function DuplicateData()Dim ws As Worksheet
    Dim current As String
    Dim lastrow As Long
    Dim nextrow As Long
    Dim i As Long
    
    
        Set ws = Worksheets("duplicates")
        With ws
        
            .UsedRange.ClearContents
            .Range("A3:E3").Value = Array("Ingredient", "Date 1", "Date 2", "Date 3", "package")
        End With
    
    
        Worksheets("master data").Copy AFter:=Worksheets(Worksheets.Count)
        With ActiveSheet
        
            .Name = "temp"
            .Range("G1").Value = "Flag"
            
            nextrow = 4
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range("G2").Resize(lastrow - 1).Formula = _
                "=IF(COUNTIF(E$1:E2,E2)>1,INDEX(G$1:G1,MATCH(E2,E$1:E1,0)),MAX(G$1:G1)+1)"
            .Columns("A:G").Sort key1:=Range("G2"), order1:=xlAscending, Header:=xlYes
    
    
            For i = 2 To lastrow
            
                If Application.CountIf(.Columns("E"), .Cells(i, "E").Value) > 1 Then
                
                    current = .Cells(i, "E").Value
                    ws.Cells(nextrow, "A").Value = current
                    Do While .Cells(i, "E").Value = current
    
    
                        .Cells(i, "B").Resize(, 3).Copy ws.Cells(nextrow, "B")
                        .Cells(i, "A").Copy ws.Cells(nextrow, "E")
                        nextrow = nextrow + 1
                        i = i + 1
                    Loop
                    
                    nextrow = nextrow + 1
                    i = i - 1
                End If
            Next i
        End With
        
        Application.DisplayAlerts = False
        Worksheets("temp").Delete
    End Function

  3. #3
    Thanks, Bob for the code.

    can you provide steps in incorporting this code....I opened VB editor...and copied the code...saved the file...

    As I said before, I am newbie to the VBA code and how to run this...



    Quote Originally Posted by Bob Phillips View Post
    Code:
    Public Function DuplicateData()Dim ws As Worksheet
    Dim current As String
    Dim lastrow As Long
    Dim nextrow As Long
    Dim i As Long
    
    
        Set ws = Worksheets("duplicates")
        With ws
        
            .UsedRange.ClearContents
            .Range("A3:E3").Value = Array("Ingredient", "Date 1", "Date 2", "Date 3", "package")
        End With
    
    
        Worksheets("master data").Copy AFter:=Worksheets(Worksheets.Count)
        With ActiveSheet
        
            .Name = "temp"
            .Range("G1").Value = "Flag"
            
            nextrow = 4
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range("G2").Resize(lastrow - 1).Formula = _
                "=IF(COUNTIF(E$1:E2,E2)>1,INDEX(G$1:G1,MATCH(E2,E$1:E1,0)),MAX(G$1:G1)+1)"
            .Columns("A:G").Sort key1:=Range("G2"), order1:=xlAscending, Header:=xlYes
    
    
            For i = 2 To lastrow
            
                If Application.CountIf(.Columns("E"), .Cells(i, "E").Value) > 1 Then
                
                    current = .Cells(i, "E").Value
                    ws.Cells(nextrow, "A").Value = current
                    Do While .Cells(i, "E").Value = current
    
    
                        .Cells(i, "B").Resize(, 3).Copy ws.Cells(nextrow, "B")
                        .Cells(i, "A").Copy ws.Cells(nextrow, "E")
                        nextrow = nextrow + 1
                        i = i + 1
                    Loop
                    
                    nextrow = nextrow + 1
                    i = i - 1
                End If
            Next i
        End With
        
        Application.DisplayAlerts = False
        Worksheets("temp").Delete
    End Function

  4. #4
    You now need to assign the macro to the button on your duplicates sheet.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    To create the button, look for the top left button on the Developer-->Controls-->Insert

    As soon as you drop the button on the worksheet it will pop up a dialog asking you to assign the macro to it.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Quote Originally Posted by Ken Puls View Post
    To create the button, look for the top left button on the Developer-->Controls-->Insert

    As soon as you drop the button on the worksheet it will pop up a dialog asking you to assign the macro to it.
    He already did, he just needs to assign the macro to it.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Doh! (I didn't actually open the file.)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Thanks, Ken and Bob for your prompt help.

    I am getting the following errro when I am running...ran it in debug mode .

    Attached the excel sheet for your review and help.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture1.JPG 
Views:	8 
Size:	26.1 KB 
ID:	944  
    Attached Files Attached Files

  9. #9
    working now...I think

    Let me give u an update tomorrow...if that doesn't work. Thanks again to Bob for the macro code.

    do u suggest any good book for learning VBA? i am sure , I will learn lot by reading the forums on this site...

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by komarag View Post
    do u suggest any good book for learning VBA? i am sure , I will learn lot by reading the forums on this site...
    I've always been a big fan of John Walkenbach's Power Programming With VBA book. I just about wore out the spine in my copy. That, coupled with a good forum, will take you miles.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •