Posts by dacat1997

Re: Create list(s) from table of data

That worked perfectly. I consider myself advanced, but you have definetly humbled me. I'm very impressed with how you simplified my formula in column A, and your thought process to come up with Column B... WOW! I have much to learn. Thanks again.

The attempt has 3 parts, I have completed part 1 (quite messy though) and part 3. I am attempting this all via formulas without the use of vba.

Part 1 (would appreciate cleanup or simplification) in Column A
Return the lists of Sellers based on the number of locations the Seller services. So if Seller 'A' services 5 locations then it should be shown in 5 rows, followed by Seller 'B', etc.
=IF(ROW()-ROW($A$2)>SUM($N$3:$N$8),"",IFERROR(INDEX(Breakdown!$L$3:$L$8,IF(IFERROR(COUNTIF(A$2:A2,A2)<VLOOKUP(A2,Breakdown!$L$2:$N$8,3,0),1),IFERROR(MATCH(0,COUNTIF($A$2:A2,Breakdown!$L$3:$L$8&""),0)-1,COUNT(COUNTIF($A$2:A2,Breakdown!$L$3:$L$8&""))),MATCH(0,COUNTIF($A$2:A2,Breakdown!$L$3:$L$8&""),0))),""))

Part 2 in Column D
Return list of Locations Serviced by Seller. My mind is completly stuck on this, any help is appreciated.

Part 3 in Column G
Return value of Seller by location.
=IFERROR(INDEX($L$2:$W$8,MATCH(A3,$L$2:$L$8,0),MATCH(E3,$L$2:$W$2,0)),"")

Here is my sample file. forum.ozgrid.com/index.php?attachment/65889/

Thank you for your assistance.

Re: Building an array for use in AutoFilter

I tried the Advanced Filter but again that only gave me the selection that needed to retain and not the inverse. I did make use of the code provided in #3 as this was more efficient than the process I was following. What I ended up doing was creating a hidden tab to take the selection and then copy and paste it to the hidden tab and work off of that sheet instead.

I do appreciate your help. I will take your recommendation in #8 and implement that in the next roll-out of my workbook.

Thanks again.

Re: Building an array for use in AutoFilter

Correct. The attempt is to filter out what was listed in column B so that then i could use .SpecialCells(xlCellTypeVisible) to delete the remaining result and therefore after removing the autofilter i have a clean selection of only items listed in column b.

Thing of it is that I am having to reorganize a data export into a useable format, and this export contains anywhere from 6k to 10k rows of data across 26 columns. So eliminating all unnecessary rows of data save huge amount of time on the clean up process.

Re: Building an array for use in AutoFilter

Ok that works great to filter for what I need to retain, the next step is to eliminate/delete the inverse of the selection from the worksheet.

Hence why I was trying to build the filter to exclude the items that were listed on Sheet1.

StaffFilter(i) = "<>" & C '& ","

Re: Automatic Break when executing code

This is an old thread but I have been able to duplicate the issue and resolve the issue easily.

Hitting CTRL-BREAK while VBA is processing may cause excel to prematurely "break" when the running code is updating the status bar. I have found that by hitting CTRL-BREAK a second or third time before excel chooses to "break" on its own will fix the problem and allow the code to run to completion.

Amazing, took me 6 years to figure this one out.

Ok guys, I need some help. I am trying to setup a subroutine to set the Autofilter Criteria

x=array("apples","oranges") 
Sheet4.Range("8:8").AutoFilter Field:=4, Criteria1:= x, Operator:=xlAnd

This 2 liner works except for the fact that x is now hard coded in the VBA and I need it to change dynamically based on critieria identified on a "user preferences" worksheet.

Sub buildfilter()


Dim C As Range
Dim StaffType As Range
Dim StaffFilter() As Variant


With Sheet1
    Set StaffType = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With


ReDim Preserve StaffFilter(WorksheetFunction.CountIf(Sheet1.Range("A:A"), "x"))
For Each C In StaffType
    If Len(C.Offset(0, -1)) Then
        i = i + 1
        StaffFilter(i) = "<>" & C   '& ","
    End If
Next C


Sheet4.Range("8:8").AutoFilter Field:=4, Criteria1:=StaffFilter, Operator:=xlAnd


End Sub

This is what my current non-functioning attempt looks like. Any assistance would be appreciated. Thank you.

Re: Convert Comma Delimited Cell To An Array

Thank you all for your help. I was thinking a UDF would be necessary but was hoping I could get it done within Excel's base set of functions.

I understand the perfomance issues with arrays but rather than re-engineer all the process before my worksheet, the current layout will have to work. And truth be told, 1 sec to calculate the ton of formulas i have laid out is still impressive.

Thanks again.

Re: Convert Comma Delimited Cell To An Array

Here is a sample of the data and how I thought it would work.

Re: Convert Comma Delimited Cell To An Array

Using a pivot table worked for a little while until the number of calculated items within the pivot table made it faster to just calculate using pencil and paper.

I am not sure how I would utitlize the SUMPRODUCT function to accomplish what I am able to do today. The formula would need to be dynamic and I do not see that happing with the SUMPRODUCT function.

As of right now using the ARRAY is the most effective/efficient since adding additional members to the team only requires and additional ID to be added to the standing array.

My goal is to be able to maintain a list of various team names with the associated team on a separate table and then pull back the list of team members to the SUM IF formula.

Re: Covert Comma Delimted Cell To An Array

Dave, thanks for your reply.

This solution does not address the specific issue that I am trying to solve for.

I intended my post to indicate that I need a solution that would allow VLOOKUP or another function to return an array to the formula instead of a string.

Thank you again for your help.

{=SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.

I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.

{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.

Thank you for your help.

Re: Convert Range To Pseudo Table

Thanks Will,
That worked like a CHAMP

Re: Convert Range To Pseudo Table

I will have to go with the ugly solution since our DBAs are not willing to make any changes.

Re: Convert Range To Pseudo Table

I assume you are referring to being able to create a table on the SQL server; no I don't.

My code currently references each cell in range individually and provides results 1 at a time. I would like to know if its possible to convert the Range to a "Table" that can then result in a single result with multiple records.

.... i want 1 query instead of 1000 queries :shooter:

Private Sub GetData()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim stConn As String
Dim L As Range


On Error GoTo errorhandler


Dim Logins As Range


Set Logins = Range("A2:A" & Range("a65536").End(xlUp).Row)


'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
 
'Create the connection string.
stConn = "DRIVER={sql server};DATABASE=db;SERVER=dbSvr;"


'The raw SQL-statement to be executed.


With cnt
    .Open stConn 'Open the connection.
    .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
    With rst
        For Each L In Logins
            stSQL = "SELECT *.* " & _
                    "FROM TEMPLATES RIGHT JOIN emp ON TEMPLATES.Id = emp.ID " & _
                    "WHERE (((emp.LOGIN) = '" & L & "') AND (emp.UNT > 10));"
            
            .Open stSQL, cnt 'Create the recordset.
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
            
            If Not (rst.EOF Or rst.BOF) Then L.Offset(0, 3).CopyFromRecordset rst


            .Close
        Next L
    End With
    .Close
End With


'clean up
Set rst = Nothing
Set cnt = Nothing
Exit Sub
errorhandler: MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error"


End Sub

Re: Automatic Break when executing code

I havent had this issue in a long time. The only thing different on my computer is that it was upgraded to 1GB of RAM. Maybe that has something to do with it. Other than that, the disk cleanup followed by a restart seemed to work most of the time.

Re: My loop doesn't stop!For the wizard kidds...

Hey Zion, well man believe it or not your code works. The problem is that the controlvalue and the yearcount never really equal. In the vba you provided those variables actually miss equaling each other by 0.01. Hope this helps.

Re: Creating a floating shape

I have found the solution and it is much simpler than I originally assumed.

I have attached a workbook with the VBA commented for you.
Note:the above vba is housed in the ThisWorkbook module.

Hopefully your unfinished project can be resurrected

Re: Creating a floating shape

Here is a copy of my revised code. In my worksheets i am running on rows 100-500 so the offset in the calculation for the box is way obivous and needs to be resolved. This code will run thru creating the shape and output text for all sheets in a given workbook.

Option Explicit


Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)


On Error GoTo Shape_Deleted
Sh.Shapes("TellMe").Delete


Shape_Deleted:
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)


Dim TopIs As Long
Dim LeftIs As Long
Dim i As Integer


Select Case Target.Column
    Case 2
        On Error GoTo Shape_Does_Not_Exist
                
        With Sh.Shapes("TellMe")
            For i = 1 To Target.Row
                TopIs = TopIs + Sh.Cells(i, 1).RowHeight
            Next i
            .Top = TopIs
        
            For i = 1 To Target.Column
                LeftIs = LeftIs + (Sh.Cells(1, i).ColumnWidth * 5.7)
            Next i
            .Left = LeftIs
            .OLEFormat.Object.Text = "Current MU: " & GetSURFInfo(Target.Value, "MGT_UNT_CD")
            .Visible = msoTrue
        End With
    Case Else
        On Error GoTo Shape_Deleted
        Sh.Shapes("TellMe").Delete
End Select


Exit Sub


Shape_Does_Not_Exist:
On Error GoTo 0
Sh.Shapes.AddShape(msoShapeRectangle, 0, 0, 50, 50).Name = "TellMe"
Resume


Shape_Deleted:


End Sub

Thanks for any and all input!!