Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Sub CopyToA()
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
ActiveCell.Offset(0, 1).Select
Loop
End Sub
However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.
Need the formula that I will need in my macro to move to the next column (up one letter) for the next time I run the macro witout me having to manually change column the letter?
I have a look up table on sheet 1, that looks like this table to the right. On sheet 2, I need it to find the table contents in the column CC, then look at a column AA and find all that match it and move all that match this cell in column AA and copy the row it to sheet 3.
Its 300 lines of data, a couple of time of week.
It is a sort of data with a twist.
AABBCCDDEETreeDogCathousecardavemanboyjobbaseTableboatsamlaurasamjimboydaveweedwalkwalkruncatfunlaughboydivefloatTreeDogCathousecardavemanboyjobbasedaveweedwalkwalkrunfunlaughboydivefloat
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
View 9 Replies View RelatedI have this module that is supposed to look in column A and if it sees duplicates it looks in column B (with the date) and erases the entire row where the date is older in column B. I am attaching a sample to better illustrate it.
The highlighted in yellow rows are were I have duplicates and I want the row where the date is in red border to remain.
The module I have so far erases the newer records instead of the older.
This is the file:
Sample sorting.xls
Here is the
I am having to write a vba code for a survey data captured code online.
I am attaching the code and the vba module that is suupose to do
following things
2) dELETES THE LAST 3 COLUMNS
3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)
4)Inserts new row at top from Q1 TO Q11
5) Sorts data on Q6 (Faculty/Staff)
Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....
I am trying to sort a long range of text that is placed horizontally in a spreadsheet. I can do it vertically with the sort function in Excel but it does not seem to work for text that is placed horizontally. Example is as below:
Inventory Accounts Human Resources
When I press SORT A-Z (through a column filter) on my spreadsheet, I want something like this to happen. Is it possible?
EXCEL.jpg
I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies View RelatedSo, I have two columns on a sheet that all contain drop down boxes for various information. We'll refer to them as column 1 and column 2.
In column 3, I want to have the highest instances of those words sorted with the highest on top, and downward from there, along with the number of times it appears in either column 1 or 2. (And I want the sorting to be dynamic, i.e. when I change what's in column 1 or 2, it will re-rank them according to highest number)
i.e.
Column1 Column2 Column3 (Column4-#)
Bob Tom Bob 5
Joe Bob Tom 2
Bob Joe 1
Tom Bob
Bob
I have the drop downs and what not for the 1st 2 columns, I just need to know how to make 3 & 4 happen.
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp‎
I am a buyer of hundreds of different types of items. I get thousands of different price quotes each day for hundreds of different items, 3 of which are balls, gloves and bats. I don't have the time to go line by line through thousands of quotes for hundreds of items each day to find the best deals. I want Excel to point the best deals out to me automatically, perhaps by making a cell.
Basically I need excel to sort my data by item type.
Then, calculate the average price for each item.
Then, point out any price quotes that are 10% below the average price, perhaps by making that quote in a red box. Attached is a spreadsheet with an example.
I have a list of grades they are 3a, 3b, 3c, 4a, 4b, 4c, etc up to 7a. An a is one grade than a b, and a b is one grade higher than a c. In one test a student may score a 4b and in another a 4a. I need a way to calculate the difference in "grades" e.g if a student scores a 4a in test 1 and a 5c in test 2 the difference is 1.
Is there a way I can do this using custom formatting or will I have to assign values to the grades and insert extra columns to do the calculations. I need the simpliest way as I have to disseminate to my staff in my department.
I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.
View 3 Replies View RelatedI have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
I have a simple projectlist on sheet1 formatted like this:
Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
On sheet2 I have the same columnheaders.
What I would like to do, is that if Status changes to "Done",
* the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
* the now empty row on sheet1 should be deleted (so there're no empty rows)
* the data on sheet2, should be sorted with D as first key and C as second.
Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.
My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?
I only know small bits of VBA but my idea is this: ...
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.
On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.
I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
I would like to find a value w/i a column after sorting and insert a row right above it. After that I would like to sort again from that point downward.
View 2 Replies View RelatedIs it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
I am trying to accomplish three things with the attached workbook. The first thing is the insert a blank row between the different part numbers, column "A" to make the sheet easier to read. The second thing is to highlight the data in light gray (A:E) leaving the blank row that was created empty. The third is to then sort the worksheet by data in column "A" to group like numbers together.
View 12 Replies View RelatedI would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
View 3 Replies View RelatedI am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I have multiple columns with many rows of unique text in each. Here is an example of what the 1st few rows of column a and b might look like:
A___________B
hboc______dds
jk________optg
hbv_______pl
FIRST:
I would like to be able to sort the rows by the character length in a column. So, it would look like this (if sorted by 1st column):
A___________B
jk_________optg
hbv________pl
hboc_______dds
Second.........
I have an Overview sheet within my workbook that contains info from all other sheets.
What I want to do is when you click on the Sort button in the General sheet it will activate the Overview Sheet and
- first off sort all sheets starting with AJ together, then all sheets starting with CJ together and then all sheets starting with PJ
- then the next thing i want it to do is sort all Ajs by their start dates, sort all CJs by their start dates and the same for all PJs.
Now up until now I was keeping all sheets that began with AJs together in the workbook and so on so I was able to use the following sort function
Sub Overview_sorting() ...
I am using a formula but when it sorts with the data in the adjacent cells it does not update the reference cells properly.
For example here is the =SUMPRODUCT(($F$12:$WWX$12>=C1)*1,($F$12:$WWX$12<=E1)*1,ABS($F$13:$ZZ$13))
This code is located on row 13, when I do a sort function with all the data, this code is then moved to row 30, that is fine but the code changes in the following way:
=SUMPRODUCT(($F$12:$WWX$12>=C18)*1,($F$12:$WWX$12<=E18)*1,ABS($F$13:$ZZ$13))
C1 & E1 should not have changed at all, F13 & ZZ13 should have changed to F30 & ZZ30.
I am not sure if there is a way to fix the formula so it updates correctly, or these cells can stay in the same place as long as everything else sorts.
Is is possible to sort all columns except a few?
I am trying to get columns A:E on Sheet1 converted into columns A:H on Sheet2. I attached the workbook with the macro so you can see what I am talking about. I posted the same macro in the workbook below. It comes close to what I am trying to do but it only sorts based on Column E. I would like to include Columns A, B, C, and D in the sort instead of just Column E so the display will look like Sheet2.
View 2 Replies View RelatedI'm having a bit of trouble creating a macro that will sort columns in order - I've created a form that allows people to select a broad category ie) Schools, then a sub category ie) Primary or Secondary, and type in a third ie) Projects or Teachers
I'm trying to write a macro so that once they enter the form, their choices will be automatically sorted alphabetically, first by column A, then B, then C - to look like:
Schools - Primary - Projects - A
Schools - Primary - Projects - B
Schools - Primary - Projects - C
Schools - Primary - Teachers - B
Schools - Secondary - Projects - C
Schools - Secondary - Teachers - A
Schools - Secondary - Teachers - B
Schools - Secondary - Teachers - C
However I'm having the issue where if column B or C are left blank - the columns are not sorted properly, and the categories in column A get separated out..?
My code at the moment looks like:
Range("A1").Select
ActiveSheet.Unprotect
Range("A1").Sort Key1:=Range("A2:A3"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
[Code] .......