Special Row Sort Of Multiple Columns
Oct 29, 2008
I have a Name in Column A
I have a Number total(using a formula) in Column B
Each week I am copying from another source Names and Numbers, let's say in Column C and D.
I want to match the names in A with the Names in C so they are in the same row. The number in Column D needs to go with name from Column C.
Right now I am having to highlight the name and number in Column C & D and then drag it down to the row with the exact name in Column A. If there is a new name it adds it at the bottom of Column A and C.
View 9 Replies
ADVERTISEMENT
Feb 15, 2010
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.
View 2 Replies
View Related
Nov 6, 2009
I'm trying to figure out a way ( excel 2000) how to sort data over a range of columns.
Attached is a sheet.
So what I am looking for is this:
Bottom 10 for target 1, target 2, target 3.
I can sort them indervidually, but is there a way to sort the all?
Or would I need a agent column for each target to sort?
Or maybe there is a way to sort the data so it would work out that if they are in the bottom 10 of lets say 2 of the targets but not all 3 they would still show in the bottom 10?
View 7 Replies
View Related
May 19, 2014
Why my vba sort re-arranges columns instead of just sorting them.
I have four columns of data: T = department, U = director, V = client, W = product.
The number of rows varies. The data needs to be sorted by T, U, V and then W. I tried to create a macro that would perform the sort in two steps:
Sub test()
With Sheet19
.Activate
.Range("T2:W" & Cells(Sheet19.Rows.Count, 20).End(xlUp).Row).Sort _
Key1:=Range("W2"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlSortRows
[Code] ........
The macro sorts the data in the correct order, but it also puts column W before columns T, U and V.
So I end up with T = product, U = department, V = director, W = client.
View 1 Replies
View Related
Oct 23, 2009
My columns are labeled A B C D etc... If the product I am creating the table for falls into one of those categories I place a 1 in the cell. For instance, ProductA falls into the A and B categories, so column A will get a 1 and column B will get a 1. This is so I can sort all of category A's Products etc...
I am looking for a formula to put in a column that would Say what categories ProductA falls into. ie Since ProductA falls into the A and B catergories but not the C and D catergories, This final column would say something like AB, or A,B or something.
Alternatively, I could make a single column entiltled 'categories' and put something like 'A B' in ProductA's column. In this case I would need a way to sort all A's or sort all B's and not just sort all 'A B'
View 9 Replies
View Related
May 14, 2012
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155
View 2 Replies
View Related
Aug 6, 2008
In row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.
View 9 Replies
View Related
Jul 9, 2013
I have the following setup on a worksheet :
5427 Data
5427 Data
5427 Data
6565 Data
6565 Data
7765 Data
all I want to do is have the above table look like this :
5427 Data
6565 Data
7765 Data
The concept being that I can double click that row to see the additional information(i.e. expand to show extra information)
Real life use would be of such, the first column contains the invoice number, the 2nd 3rd etc contain item associated with such invoice.In grouping the column invoice number, you only have to look at one invoice, instead of it duplicating for each item contained within the invoice.
This will need to be done in an efficient manner(over 4000 rows need grouped)
I realize I could just create a list that has one copy of the duplicates, but I want to extract the original information as well. For example, it would look like the following :
Original :
1212 Data
1212 Data 2
1212 Data 3
2323 Data 1
2323 Data 2
Edited :
1212
2323
double click 1212 for :
1212
+Data 1
+Data 2
+Data 3
2323
Note : This grouping will also have to span across the whole row, sample rows :
Invoice# Price Color Weight Item
1212 Column1Data Column2Data Column3Data Column4Data etc.
1212 Column1Data Column2Data Column3Data Item2Column4Data etc.
View 4 Replies
View Related
Feb 14, 2014
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
View 14 Replies
View Related
Nov 14, 2009
I have a row of sub-headings at row 12 that require Sort Filters. My problem is that I need the filters on selected columns only. It seems that with the standard filter button I get all or nothing.
View 8 Replies
View Related
Jan 3, 2012
I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).
delete
EFGHIJKLMNOPQ8BallFrqPct
BallFrqPct
[Code]....
View 2 Replies
View Related
Sep 27, 2008
I have a spreadsheet with multiple columns. The first column defines a "route", and the next two list "start" and "end" cities for that route. The fourth column lists the length of each route. There are only a limited number of cities, so the same entries appear in both "start" and "end" several times. I would like to use Autofilter to sort the list for every appearance of a given entry in either "start" or "end". Is there a way to make Autofilter sort mutiple columns simultaneously?
I could achieve the desired end result with Advanced Filter, but I want something with the ease-of-use and immediate update/response of Autofilter. Advanced Filter requires explanation (as well as lots of clicking and typing) whereas Autofilter is self-evident. I also want to avoid VBA Macros as they are not well-understood by the users who will use this spreadsheet (and any VBA Macro will require very specific input to work properly.) Is it possible to do what I want? Or is Advanced Filter / VBA the only way to do it?
View 2 Replies
View Related
Feb 22, 2007
1. Alphabetize the worksheets that have a red tab color. The number of worksheets with a red tab color will vary from time to time.
2. All worksheets with a red tab color are formatted the same. I would like to copy the same range of cells (AP5:BP9) on all n number of worksheets with a red tab color to a worksheet labeled "Posting". Also, since AP5:BP9 are cells with formulas and formatting, the ranges need to be copied with formats and value and number formats. The first copied range needs to be on line 8 on the Posting worksheet, the second range to start on line 15, etc.
3. Last, but not really last, I would like to start the code with deleting whatever is currently on the Posting worksheet starting at line 8.
I have attached a small sample. The sample does not reference the same range as above on the worksheets with a red tab color, nor does it contain the formulas and formatting.
View 3 Replies
View Related
Jan 13, 2014
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.
View 2 Replies
View Related
Jul 10, 2012
I need a formula to transpose rows to columns of a large table. The transpose array formula is not working for me. I know there is a column and row function formula to do this, but I can't remember it.
View 3 Replies
View Related
Dec 30, 2006
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
View 2 Replies
View Related
Apr 9, 2014
This formula I want to apply it in another workbook. It split in different columns the content of a cell.
The formula is below:
[Code] .....
In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|
If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.
I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.
View 5 Replies
View Related
Jan 15, 2008
Working on a macro to replace a list of about 20 specific "Special" Characters in excel, and have ran into 6 that will not work.. Following are the characters: ā, č, ć, ř, ş, ż
ActiveSheet.Select
Selection.Replace What:="ä", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="á", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
View 5 Replies
View Related
Mar 18, 2009
I have a file with multiple sheets. Most of the sheets are named for states. I will import a worksheet monthly that has sales data that needs to be copied one line at a time and pasted to the correct company on the sheet that corresponds to the appropriate state. I want to do this by macro.
Here is what my sheets look like...
Arkansas
A B
Comp1 $1000
Comp2 $2000
Louisiana
A B
Comp3 $500
Comp4 $1000
Sheet3 - Import
A B C
Comp1 AR $500
Comp3 LA $500
Comp2 AR $500
What I want is a macro that will read each line in Sheet3 and depending on the state in column B, copy/paste/special/add the value in column C to the amount in column B for the corresponding company on the sheet for that state. In other words I would like to see..
View 9 Replies
View Related
Mar 18, 2014
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name
2. Select Move or Copy,
3. Select Create a Copy
4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
View 3 Replies
View Related
Sep 3, 2013
This is my text:
Test Name1
Test Name2
Test Name3
Test Name4
Test Name5
Required result is:
Test Name1|Test Name2|Test Name3|Test Name4|Test Name5
View 8 Replies
View Related
Mar 8, 2007
I am trying to create a macro to copy multiple sheets to a single named worksheet, all within the same workbook. The code below works, except I want to copy only the data (no formulas). Can I add code to paste values, or do I need to start over?
Public Sub CopyandPaste()
Dim ws As Worksheet
Worksheets("Summary").UsedRange.Delete
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Range("a2"). CurrentRegion.Copy _
Destination:=Worksheets("Summary").Range("A65536").End(xlUp)
End If
Next ws
End Sub
View 4 Replies
View Related
Jun 10, 2014
Have a spreadsheet with 15 columns. In one of the columns is the name of the company and that column is not in alphabetical order. The city, state, zip code, business type and all the other pertinent data about that company is in the same row as the name of the company. My intent would be to put the company names in alphabetical order and keep all the company information in the same row as the company name.
View 1 Replies
View Related
Jul 2, 2008
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.
View 9 Replies
View Related
Feb 26, 2009
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 Related
Jan 10, 2012
I have dates values in 3 columns.
A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
View 1 Replies
View Related
May 3, 2009
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?
View 6 Replies
View Related
Jul 8, 2009
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 Related
Oct 29, 2012
I'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] .......
View 1 Replies
View Related
Oct 22, 2008
I have the necessity to execute a macro for sorting a list of data on two columns.
More exactly, let's suppose to have this situation:
A 10/2/2008 FFFF GGGG HHH
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
B 08/1/2008 HHHH SSSS IIIII
C 15/10/2008 AAA BBBB CCC
I need this sort:
A 10/2/2008 FFFF GGGG HHH
B 08/1/2008 HHHH SSSS IIIII
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
C 15/10/2008 AAA BBBB CCC
View 9 Replies
View Related
