How to pass a file as _input

I’m trying to create a VBA Macro that must read a input file, and get the list of spreadsheets, but the input must be passed dynamically, it can’t be hard coded. I’m first trying to read a hard coded file: Structure: Macro | _input/ | | sample.xlsx | macro.xlsm Code: Dim wb As Workbook Sub […]

How to count fields in a column and display in another cell

In excel I have 4 columns, we are only really interested in the 3 columns which are Column B (Date) format is dd/mm/yyyy Column C (Time) and Column D (Duration) For each date I’m trying to determine the following The total number of records for each date (Display in column E) The first time of […]

Replace a newline in TSQL

I would like to replace (or remove) a newline character in a TSQL-string. Any Ideas? The obvious REPLACE(@string, CHAR(13), ”) just won’t do it…

Macro to Auto Fill Down to last adjacent cells

I want my macro to populate my formulas all the way down the sheet. Every time I run the macro it stops at row 13662 because that was the last row when I was recording it, but the last row changes constantly. Is there a way to do this? The columns I need to do […]

How to read XLS file & convert it to CSV format

I have below data in my XLS file’s 1st Column <table border=”1″> <tr> <th align=”left”>First Name</th> <th align=”left”>Gender</th> <th align=”left”>Nationality</th> </tr> <tr> <td align=”left”>Mike</td> <td align=”left”>M</td> <td align=”left”>Singaporean</td> </tr> I would like to convert it to CSV file so output should be in CSV file First Name Gender Nationality Mike M Singaporean I alerady seen […]

Excel regex match and return multiple references in formula

I’ve created a function that will return the Nth reference which includes a sheetname (if it’s there), however it’s not working for all instances. The regex string I’m using is ‘[\w ]+[‘]!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5}) I’m finding though it won’t find the first reference in either of the below examples: =’Biscuits Raw Data’!G783/’Biscuits Raw Data’!E783 =IF(‘Biscuits Raw Data’!G705=””,””,’Biscuits […]

How to insert value with same id in one cell in PHPExcell

I have an excell file generated from php request. SO what i have inside excel file is: username, project, process. Here is first table username: uid| username| salary| —+———+——-+ 1 | bob | 0 | 2 | barikan | 0 | 3 | beny | 0 | Second table process: projectNo| process | proc_leader| ———+———+————+ […]

Join rows based on unique ID

I have 32.000 rows with data. Some data are in a different place and I want to join them with something that I can apply to all rows and not manually. Each “group” have the same ID, in this example is “XPTO” I have something like this now (but with more columns): I want it […]

Concatenate and Justify in Excel

I have the data below: I would like to locate the maximum length of the string of texts in the first column, and from there count the number of spaces to position the second concatenated item to align. My attempt to concatenate has the below formula =A1&REPT(” “,40-LEN(A1))&TEXT(B1,”0.00%”) and only got this so far as […]

Populate a validated cell if another cell contains specific text

I have a spread sheet that includes a cell containing text. There is another cell that has data validation applied, lets say the list of acceptable values is “A,B,C,D”. Lets say the cell/column containing the text is F4 through to the last populated cell in the column. Lets say the cell/column containing the validation list […]

MS Excel Spreadsheet is the best Office Software, Excel VBA and Excel Formulas make Spreadsheet work faster.