# Posts by dacat1997

• ## Create list(s) from table of data

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.

• ## Create list(s) from table of data

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/

• ## Building an array for use in AutoFilter

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.

• ## Building an array for use in AutoFilter

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.

• ## Building an array for use in AutoFilter

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.

Code
``StaffFilter(i) = "<>" & C '& ","``
• ## Automatic Break when executing code

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.

• ## Building an array for use in AutoFilter

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

Code
``````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.

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

• ## Convert Comma Delimited Cell To An Array

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.

• ## Convert Comma Delimited Cell To An Array

Re: Convert Comma Delimited Cell To An Array

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

• ## Convert Comma Delimited Cell To An Array

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.

• ## Convert Comma Delimited Cell To An Array

Re: Covert Comma Delimted Cell To An Array

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.

• ## Convert Comma Delimited Cell To An Array

{=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.

• ## Convert Range To Pseudo Table

Re: Convert Range To Pseudo Table

Thanks Will,
That worked like a CHAMP

• ## Convert Range To Pseudo Table

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.

• ## Convert Range To Pseudo Table

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.

• ## Convert Range To Pseudo Table

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:

• ## Automatic Break when executing code

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.

• ## My loop doesn't stop!For the wizard kidds...

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.

• ## Creating a floating shape

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

• ## Creating a floating shape

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.

Thanks for any and all input!!