I used the macros found at http://www.contextures.com/xlSort02.html to sort columns. This solution works fine, the invisible rectangle is really good to my interests. Here's how it works: macro #1 creates invisible rectangles at the top of the columns used, at row 1 where the headers are, and assigns a second macro to the rectangles. This second macro sorts the data table based on the column whose rectangle is clicked. The problem is that this solution is for data tables, it sorts the entire table and I want to sort the one, single and only column where I click the invisible rectangle.
This is a simple question but I have been playing around with the syntax(unsuccesssfully) for a while. I want to do is sort a column (not the whole sheet). the column selection being determined by the activecell. I know I can use
I have Column A where user inputs data. Whenever a user enters a new value in Column A, I need to copy that data into Column B and then sort the whole column. Any suggestions on how to do this using a macro?
I'm having an issue where I have a macro (see attached file) that is supposed to auto-fill and sort. Twhen it tries to run the auto-fill on a sheet in its blank/template state. It will take my formulas in columns AC:AZ and fill them up to the header row.
[Code]....
This code was hobbled together using record macro and my minor understanding of coding.
I have a table of 5 columns (each of 50 length) that is automatically refreshed (i am getting the data from web)after an interval of 5 minutes. I want the first column to be sorted alphabetically after every 'refresh' command, that has been executed by the excel. i have tried recording the macro and then running it on keystroke of 'ctrl + m'...the table surely gets sorted out but i can't be pressing ctrl+m every 5 minutes. auto-sort that is required to be executed after the auto refresh command..
I am trying to figure out how I can sort multiple customers in a single cell, and then assign the customers to the outside salesman. The basic idea is in column A I have the customers for a specific job. Assume I have three customers (Alpha, Omega & Gamma) and they are all in ONE cell. I need to sort this and assign each customer to each of my outside salesmen (Bob, Ted, Fred) in another column. The other component to this question is that Bob, Ted & Fred may have MORE than one customer in that single cell........
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
I'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I needed to match the width a merged area of seven columns to a single column width (for row autofitting). Adding the column unit values and setting the single column to that value produced a significantly narrower width.
The documentation mentions that the column width unit is scaled to the font type and size and the absolute width is given in points.
This is set by the normal style setting in Excel Options or by VBA application.standardfontsize = 8 (in this case).
For instance, ten columns of Arial font 8 at 8.5 units you would think to be equivalent to a single column of 85 units. In points, the difference is 420 vs. 386.25, or 33.75 points.
Well, the standard character zero has a width at this setting of 4.5 points and 1 unit is 8.25 points, leaving 3.75 points for margins.
Then (10-1) margins allowances time 3.75 points resolves the difference.
Determining the margin allowances is straightforward, and reveals that the gradation with size is stepped by MS design. For instance, sizes up to 11 use 3.75 points for margins and increasing points for characters (except between 9 & 10). Sizes 12 through 18 use 5.25 points, 20 & 22 use 6.75, 24 & 26 use 8.25, etc.
I have created a table for this purpose, however I rarely use a "normal" other than 8, so I can probably use that set in programming.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
Now i want the total column in the second table to update and add the numbers as i update the rounds in the first one Which i can do through SUM or SUBTOTAL. However i want to sort the total column so the highest number is at the top and everytime i do it changes the name column not the total column.
I am creating a tool that is populating information off of another excel document and presenting the information in a different format then the data originally appears. Basically I am unsure how to create a formula, for example, in cell A1, that as I drag it down (A2, A3, etc) the column letter in the formula advances but the row number remains the same. In another words as the formula moves into cell A2, the "Sheet1!D3" changes to "Sheet1!E3", where as normally it would advance to "Sheet1!D4". I just started back up in excel, im sure this is way easier then I am making it seem but I have been unable to come up with a solution.
I have a file which is divided into 2011, 2012, 2013 and 2014 years in column A. I want these data in different column according to year. For example, Column E(2011), Column F(2012), Column G(2013) and Column H(2014).
Attaching sample a file with sample data to be converted.
It is also to be in consideration that data in column B and C should not be repeated and all year data should come in front of it.
I want this by coding or formula only. I dont want to use pivot table for this.
Macro for a repetitive task. I have data in every 3rd column (max of 11 columns) that I want to extract and append the data in column A in sheet 2. The data contains some blank cells and I'd like to remove them also.
I've attached an example in this workbook : tractdata.xlsx
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
I have a contiguous multi column, multi row numeric range that I want to copy (and sort in ascending order) into a single column on another sheet in the same workbook. What VBA code could achieve this, or alternatively can this be achieve via formulas?
I have a column which represents by each cell value's number the priority of each row in the table.
What I need to do is create an embedded code that updates the numbers in that column when any value in that column is changed.
For example:
Where the cell values in the column are..
1 2 3 4 5 6
and we change the fifth cell's value to 2
1 2 3 4 2 6
now there are two cells in the column with the same value, we want to keep the value we just changed in the 5th cell but update every other cell that is following the value of 2....
1 3 4 5 2 6
then I would like to resort the table by these new priorities.
similarly if the change is to increase rather than decrease the priority value...where the 3rd cell was increased from 3 ...
1 2 3 4 5 6
changed to 5..
1 2 5 4 5 6
the new change would become...
1 2 5 3 4 6
in this case the 4 becomes 3 and the previous 5 becomes 4 which keeps their relative place in the priority ranking.
I would like this to then resort the table based on this column.
I would like this to execute on the exit of the cell when a cell in the column is changed.
I would like to use the value displayed in column H under the column header after filters have been applied. There will always only be one row displayed after filtering. I'm using Win Xp with Office 2003 ....
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I need to sort information in a column containing both numbers and words. In the "asending" & "desending" it only gives two options to choose from. (none) & PartNum.
I have some numbers in a column which due to other cells not yet being filled in are returning a supressed #div/0! error. This is fine, but when i go to sort the column it puts them in the wrong order. I would like to record a macro, and assign it to the column header in order to sort the column.
I am trying to sort a column so it'd be like a schedule the earliest time on top and latest on the bottom. I understand it is a form of custom listing but I am not quite sure what is the way to enter that list entry...attached is my excel spreadsheet I am currently working on.
The first column of each table is the same field, for example 'Country'. Lets say the first table has information on male population, the second table has information on female population. So i want to merge the tables into one, but here's the problem: table1 has 100 rows (countries), table2 has 96 rows (countries). I need excel to recognise the 4 missing rows of data in table2 and insert blank rows so all the data in table2 corresponds to the correct country in table1 (column1).
I currently have a macro that will sort a given column from A1:A5000. The data set can change from A5000 to A6000 or more. How do I edit VBE to accomodate this?
I am trying to sort a range of data by 1 column (column D), and get the following error:
"The sort reference is not valid. Make sure that its within the data you want to sort, and the first Sort By box isnt the same or blank."
Having googled and also checking some previous posts, I thought the issue may be around qualifying my key to ensure that I used the active sheet, but having done this, I still get the error.
