(Match & Sort) Sort Columns B And C So That A And B Match Up Numerically
I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.
View Complete Thread with Replies
Related Forum Messages:
Match And Sort By Two Columns
Column A has customer name, columns B,C,D has corresponding sales data for
that month. Column E is blank. Column F has has customer name. Column
G,H,I has the same corresponding sales data for the next month.
Although Column A and F both have customer names, there are many changes
month over month with the addition and deletion of certain customers so the
rows do not match up exactly. Is there a quick way to have the rows match up
according to customer names in column A and F and then sort them
Currently my list looks like this:
I would like it to look like this:
How Do You Sort Columns So That They Match Up With Other Columns With Like Data
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.
Sort Numerically By The Values
I have 9 columns of data in C2-C10 that I can sort numerically by the values in C2. All the numeric values in C2 are also in C1 but C1 has additional numbers not in C2.
I want to sort C2-C10 such that each row in C2-C10 aligns with its respective value in C1.
e.g. If C1 contains 1,2,5,8,34,42,56 and C2 contains only 2,5,34,56 I want the row (of C2-C10) with C2=2 to align with the cell where C1=2. And where C2=34 I want the row (C2-C10) with C2=34 to align with the cell where C1=34.
Is this possible though i'm not sure my explanation of what I want to do is good enough!
Note that my dataset is going to be very large (500,000 rows).
Sort And Match
I consider myself an ok Excel user and use it within a window of comfort for lots of things successfully, however todays task has me stumped and extremely frustrated... cant see the wood for the trees...
I have a spreadsheet of data collected from two sources, columns A & B from one source and columns C to E from another.
There are usernames in columns B and C.
I need to sort the entire sheet so that a username in column B is aligned with the same name in column C (and keeps the rest of the data in the row together)
I have scoured the net for hours for what seems such a simple operation to no avail.
A B C D E
data username1 username1 data data
data username2 username9 data data
data username3 blank data data
data username4 username6 data data
data username5 username2 data data
Find, Match And Sort Macro
On my worksheet I have the 6 columns below starting in A1.
The Code is an ASX code and the PE is the price earnings for each code for the past 3 years
The 3 code columns A1, C1, E1 are letters and the 3 PE columns B1, D1, F1 are numbers.
I need to match and sort these so the codes with their PE's all match so I can compare them.
Using the codes in A1 to match the rest would be ideal as this is he most recent data.
Every year new stocks are added and some deleted so none of the rows match.
Its hard to post and example so if you need more info please ask.
I have very basic vb skills and may be able to adapt some code already posted if anyone knows of some.
Code08 PE 08 Code07 PE 07 Code06 PE 06
AAC 3.4 AAC 5.5 AAC 12
AAF 6 AAE 13 AAI 3
Formula To Reorder Data - Match/Sort/lookup
I have a data entry sheet for attendance that shows the individuals name, date and hours attended. Each individual attendance is addedd to a new row. I need to transpose this data and display it in a report table, with the names in column A and showing hours worked for each day of that week. The formula needs to accomodate the growing data entry sheet as records are added (I'm using a date picker to change the week in the report sheet).
I've tried match, lookup and index and have confused myself thoroughly! Below is a sample of the data entry sheet (on left) and report table (on right)
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1I1J1K1L1M1=
Sort Horizontally. Sort Across Columns
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
Sort Command - Custom Order Or Sort Ascending Or Descending
Is 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
Macro To Sort Colums And Protect After Sort
I 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,
Sort Multiple Columns, With Blanks, And Move All Columns Into One
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
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.
Sort All Columns Except For Few
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:
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?
Sort Columns A B C D And E
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.
Sort A To G And S Columns
r = Range("c65536").End(xlUp).Row
Range("a5:G" & r).Select
Selection.Sort Key1:=Range("G5"), Order1:=xlAscending, Key2:=Range("F5") _
, Order2:=xlAscending, Key3:=Range("C5"), Order3:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
Rows("4:" & r).Select
For i = 5 To r
If i Mod 2 = 0 Then
Selection.Interior.ColorIndex = xlNone
Selection.Interior.ColorIndex = 36
Application.Calculation = xlCalculationAutomat
this sorts range from A to G and set color to each second row how can I include S column in this range
I mean that cells in column S are moved with cells from columns A to G.
Sort Columns By A Button
I have a code with perfectly sorts Excel rows when you click a button:
Private Sub CommandButton1_Click()
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending
but if i like to sort the columns by Row 1 how i can do it?
Sort Two Columns Simultaneously
I'm trying to sort selected rows by two columns, but simultaneously instead of sorting by "Column A and THEN Column B". In my case, this is numerical data where there is ONLY data in one column or the other, never both.
Should sort to:
2 Columns Table Without Using SORT
I have some table with 3 columns: Name, Team, Players with the same name
I want to get to a 2 columns table without using "SORT" or something like that... using only formulas.
Maybe I wasn't so clear so I added a file with my problem.
VBA: Sort On Two Columns
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
Sort Like Numbers In 2 Different Columns
I have 3 columns; A:Name, B:Debit & C:Credit. There are multiple row (approx 15,000) where I have a customer name and then a debit at one date, and a credit at another date. There is a debit to match every credit.
I would like to some how sort the sheet so that I can make sure each credit matched the debit. That way I can find what debit's don't match the credits. This is an example of how this looks. http://i303.photobucket.com/albums/n...kejoe/sort.jpg. I would like to sort it like the example on the far right (see attached picture)
I am trying to create a VBA macro that autosorts a selection of columns by column A. I currently have the below already written which gives the desired effect. I was just wondering if there was a way i could have this script constantly running on the sheet so it is always sorted correctly without me having to click Run Macro everytime.
Selection.Sort Key1:= Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Sort - Defined Sort Criteria
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() ...
Sort Records That Are 3 Lines By 18 Columns
I have data records that are 3 rows long by 18 columns (A thru R) wide. People's 1st names only exists in the 1st row, 2nd column (B) of each record. The 1st record starts on row 5.
I need a VBA procedure that when run, it will sort the data alphabetically by the 1st row, 2nd column (B) of each data record. The 3 line data records looks like this starting on row 5:
1st row _18_|_John____|_730-6853_, etc
2nd row ____|_Doe____|_238-4835_, etc
3rd row_____|_Sales___|_328-4893_, etc
1st row _32_|_Jack____|_684-3812_, etc
2nd row ____|_Jones___|_232-4954_, etc
3rd row_____|_MFG____|_238-5355_, etc
1st row _18_|_Jimmy___|_485-2294_, etc
2nd row ____|_Smith___|_223-9110_, etc
3rd row_____|_Maint___|_368-6381_, etc
If only above 3 records exists, when sorted Jack's record starts on row 3, Jimmy's record starts on row 6, John's record starts on row 9, etc
We can have as many as 15 records (3 lines long) that goes thru row 49.
Sort Data Over Multiple Columns
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?
Automatically Sort Columns Simultaneously
there are two colums, one for product name (unique names) and other one is for their prices (may be same for more then one product). I want to arrange these columns by the their price vlaues in assesnding/desending order and to have their unique corresponding product names in respective columns. Provide me any solution, probably using any excel formulas. presently i have tried this using Large and Vlookup funstions...but it fails.. when there are same price is available for two product names, then it takes only first one. I WANT TO GET THIS DONE AUTOMATICALLY, AS THIS IS TO BE POPULATED ON SOME OTHER SHEET AS A PARETO. waiting for your vlauable responces ASAP.
Auto Sort All Columns Independently
I am looking for some help with a spreadsheet I am working on, the purpose of this sheet is to record case numbers that I am working on.
This spreadsheet will have new information added to it regularly and the cases may not be in a sequential order.
What I have found is a code that will sort a column of case numbers into an ascending order upon opening the file. What I would like to be able to do is apply the same rule to all columns on that sheet.
I have included the current spreadsheet to let you see how far I got.
VBA Code To Sort Data - Run-time Error When No Data To Sort
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Copy Columns Between Worksheets If The Columns Don't Match
I have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.
Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
Sort Columns With Multiple Values In Cells
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'
Possible To Sort Hoizontally With Multiple Columns Under One Header
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.
Sort Repetitive Data From Rows To Columns
I have a column of repetitive data:
CITY, STATE ZIP
I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?
Special Row Sort Of Multiple Columns
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.
Auto Sort Columns On Workbook Open
I have a worksheet with 10 columns, and an ever number of growing rows.
What I would like to do is to Sort Column 'B', along with all the
other respective data in the other columns, each time the spreadsheet
opens. I would prefer to use VBA or some other auto-launching event.
Sort Area Around Active Cell By Two Columns
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy.
Dave supplied me with a macro that will sort a range by the first column:
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
Copy Rows To Worksheet If Cross Match & Another If No Match
I have 2 worksheets named sheet1 and need_to_delete that are in the same format: 7 digit number, 5 digit alpha numeric, text, dollar amount. I need to copy every row into sheet2 where there are no matches in the column A of the two source worksheets, and copy every row that does have a match in column A into sheet 3. I also need to keep the rows in their current order.
Sum If W/ Index Match On Vertical And Horizontal Match
I am looking to sum the values found at the intersection of multiple values based upon a vertical and horizontal lookup. The formula I am trying is as follows (which results in #Value):
Search A Sheet For A Match And Then Copy All The Cells To The Right Of The Match
I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.