All Questions
41 questions
5
votes
2
answers
152
views
concatenate values and delete the remaining rows using arrays
the below code used to:
Concatenate the values on a specific column "N" depend on the value of column "A" then delete the remaining rows.
It works, but with range of 30k rows the ...
- 275
3
votes
1
answer
105
views
Iteratively paste values from spreadsheet tabs into empty spreadsheet
Basically below code is taking first 7 tabs from my current sheet and pasting them as values in a blank excel sheet with same tab names and saving them in a specific folder by filename I specified. ...
- 31
4
votes
2
answers
1k
views
Loop between two Arrays and change values based on condition
Instead of using 45 IF conditions , I put my two ranges into variant arrays.
Then I used the below code to loop between them and change values of the first array ...
- 275
6
votes
1
answer
460
views
Fastest function to `Remove Duplicate Lines` per each cell
The below function is used to Remove Duplicate Lines per each cell.
It works without problem, but it is slow with a range of only one column and 17k rows.
Actually, ...
- 275
0
votes
2
answers
183
views
VBA - Compare & Consolidate Dynamic Arrays
Background
The CSV files being used can be 6+ mill records in size, using a small sample size to proof the methodology.
Why are you doing this on excel? Access or SQL would be quicker
My company has ...
- 1
1
vote
1
answer
175
views
Python - Efficiently pick random data from an array, generate random UUIDs and save it all in an Excel table
I wrote the following prototype:
...
- 205
2
votes
1
answer
772
views
=PRINTF("{1} - {2}", ... ) in Excel without VBA - LAMBDA functions with variable number of arguments
The TEXTJOIN function has a really nice interface:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252])
... where ...
- 2,625
1
vote
1
answer
104
views
Looking for a more condensed method of assigning values by using arrays and fixed ranges
I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.
The Macro is within ...
- 41
2
votes
1
answer
129
views
Output the rounded sum of each column in selection
I've written something (i.e. frankensteined from stack exchange) that appears to work but I haven't done much testing on the edge cases. Signed up here for some feedback on what optimizations or other ...
- 23
5
votes
1
answer
484
views
Filter a two dimension array
This is the function I've wrote to filter a two-dimension array.
I use it mainly on forms with user-defined filter (categories, dates, search bar, etc).
It works fine, but it's ugly.
Do you have ...
- 354
5
votes
4
answers
217
views
Check non-continuous range for equal values
Background
I'm trying to check equality between cells in an irregular, non-continuous range of cells. For this purpose I would like to know if all cells are equal to the first ...
- 218
1
vote
1
answer
104
views
Comparing information to a directory of active enployees
The code is doing everything that I need it to. It's taking much too long though upwards of a couple minutes to complete the macro. Does anyone know how I code optimize this VBA code to run quicker? I'...
3
votes
0
answers
31
views
Speeding up a complex array function in excel
I have a complex array function that takes way too much time to calculate, which is why I'm looking for ways to make it more efficient. Basically, I have a transaction sheet in which I list all goods ...
- 31
6
votes
1
answer
406
views
Excel's Dynamic Array Functions...In VBA (No Win 32)
In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
- 698
3
votes
0
answers
2k
views
Alternative for VLOOKUP/Index-Match in VBA
I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-...
- 698