(Match & Sort) Sort Columns B And C So That A And B Match Up Numerically

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.


Match And Sort By Two Columns

Feb 4, 2010

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:
Jan Feb

I would like it to look like this:
Jan Feb

View 14 Replies View Related

Match And Sort Data In 6 Columns?

Jan 6, 2014

Column A has donor name, column B has corresponding donation data for 2011. Column C has donor name, and column D has donation data for 2012. Column E has donor name, and column F has corresponding donation data for 2013.

Although Columns A and C and E all have donor names, there are many changes
from year to year with the addition and deletion of certain donors so the rows do not match up exactly. Is there a quick way to have the rows match up according to donor names in column each of the three columns? Secondarily, I need to sort by donation amount from highest to lowest (it can either be the total over the three years or based on any single year.

Currently my list looks like this:
2011 2012 2013

I would like it to look like this:
2011 2012 2013
B - B
C C -
- D-
- E-
H - -
L - L
- - M

Basically, I want to see the year over year giving for each donor. I've attached a sample for refrence. (I can't post the original due to confidentiality requirements.)

View 2 Replies View Related

Sort And Match 2 Columns Of Data

Jul 19, 2012

I have 3 columns of data and 30,000+ rows. Column A & B are related and column C contains some of the same values as column B but not an exact match. I'm looking for a way to sort columns A & B so that columns B & C match up and the data from column A is moved as well. For any data that has no match, a blank space should be input. Please see below.


red dog cat
blue cat dog
green mouse pig
yellow horse kitten


blue cat cat
red dog dog
green mouse pig
yellow horse kitten

View 2 Replies View Related

How Do You Sort Columns So That They Match Up With Other Columns With Like Data

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

Sort Numerically By The Values

Oct 23, 2009

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).

View 6 Replies View Related

Sort Postcode List Alphabetically And Numerically

Aug 8, 2013

How can I sort the attached list both alphabetically and numerically. I have a list of UK postcode sectors which I am trying to sort alphabetically and numerically, the order should be eg AB1 0, AB10 1, G2 1, G20 1 but excel wont allow that sort order. There are three postcode formats

XX11 1
X1 1
X11 1

Where X an Y could be any letters in the alphabet and 1 could be any digit 0 to 9. Have attached a short list and formula in column B which would allow me to sort the list in column A which I can then apply to my full list.


View 2 Replies View Related

Sort And Match

Dec 3, 2008

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.


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

View 9 Replies View Related

Find, Match And Sort Macro

Jul 8, 2008

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

View 9 Replies View Related

Formula To Reorder Data - Match/Sort/lookup

Jan 4, 2007

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=

View 9 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

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

Sort Horizontally. Sort Across Columns

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

Sort Column To Match Pair In Other Column, Skip Blank Spaces

Mar 28, 2007

Here's what I have.. (on a much smaller scale)


(notice how the lines that match are now even.)

[Edited by admin~ *Link* to large images, don't display them]

View 9 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.


[Code] .........

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.


[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.


The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Match 1st And 2nd Columns In 2 Sheets And Return 3rd / 4th And 5th Column If Match

Dec 15, 2013


What im trying to do is match columns A & B from AUDIT Sheet to Columns A & B in MASTER sheet. If they match then pull columns C, D & E from MASTER into AUDIT.

View 9 Replies View Related

Sort Column B To Match Column A

Nov 14, 2006

column A has random numbers 1- 10. Column B has numbers 1-10 also but in a different order. How can i sort column B to match Column A?

View 9 Replies View Related

How To Match 2 Columns And Count Their Match

Jan 28, 2008

HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how

many are number matched in column N..


























View 9 Replies View Related

Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

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

View 5 Replies View Related

Sort Command - Custom Order Or Sort Ascending Or Descending

Oct 8, 2006

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

View 4 Replies View Related

Sort Worksheets Using Custom Sort To Choose Certain Word?

May 30, 2012

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.

View 1 Replies View Related

Sort / Filter Rows: Sort A Range Of Row

Nov 26, 2008

I 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 Related

Macro To Sort Colums And Protect After Sort

Nov 5, 2009

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,

View 3 Replies View Related

Match 2 Columns And Add Row Where There Is No Match?

Apr 22, 2014

I have a situation where I need to match 2 columns, Data in Column E & Data in Column R.

When there is not a match, there must be a set of cells/row inserted to the right or left depending on which data is missing and the data beneath must be shifted down.

There have been issues with doing this because once a cell is added, the formula for matching does not stay consistent.

I have attached an example where the tab "Final" is the end product needed and the First 2 Tabs are the Raw Data

The Steps are to take the data from the first 2 tabs and align them side beside only being divided by a column and another to match the 2.

View 14 Replies View Related

Sort Values In Column B To Match Values In Column A?

Jul 8, 2013

I have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:










So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.

View 5 Replies View Related

Sort Multiple Columns, With Blanks, And Move All Columns Into One

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
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

Sort - Defined Sort Criteria

Sep 20, 2006

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() ...

View 5 Replies View Related

Sort All Columns Except For Few

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:


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

Sort Columns A B C D And E

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

Sort Columns - First A / Then B / Then C

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").Sort Key1:=Range("A2:A3"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

[Code] .......

View 1 Replies View Related

VBA: Sort On Two Columns

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
C 15/10/2008 AAA BBBB CCC

I need this sort:
A 10/2/2008 FFFF GGGG HHH
C 15/10/2008 AAA BBBB CCC

View 9 Replies View Related

Sort A To G And S Columns

Jun 26, 2006

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
End If
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.

View 9 Replies View Related

Sort 4 Columns By Comparing 2

Aug 8, 2014

I have column A which contains a range of part numbers, and column B is an On Hand quantity of that part number.

Column C is just like column A, in that it contains the same part numbers but it has about 1,000 additional part numbers that are obsolete. Column D has the On Hand quantity for Column C.

Column A and B are old information as far as the On Hand quantities, where Column C and D are up to date On Hand. The problem is the old outdated part numbers in column C.

How do I make column A and column C match, but not lose their respective On Hand quantities? I know this sounds discombobulated, but basically I need column A's part numbers, with column D's on hand quantity.

View 2 Replies View Related

2 Columns Table Without Using SORT

Mar 1, 2009

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.

View 11 Replies View Related

Sort Like Numbers In 2 Different Columns

May 4, 2009

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)

View 3 Replies View Related

How To Sort Two LARGE Columns

Oct 11, 2013

I have a list of domains (about 200,000) that looks like this:

I have a list of urls (about 1 million) that looks like this:

I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):

I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.

View 3 Replies View Related

Sort / Shuffle Certain Columns For Given Row

Mar 2, 2014

I feel like this should be somewhat simple, but none of the normal methods I know seem to work. I want to set up a formula (not a basic sort, since I want it to automatically correct every time I change information) so that for each row, several columns re-sort by date.

For instance, I have the following table:

Item Type Dates

A DV 5/2/14 6/12/14 7/11/15
B DS 3/11/13 6/19/14 1/2/15
C LG 11/12/14 8/1/15 1/12/16

where each of the dates is in a new column, but the "Dates" title is merged between the cells

I want to be able to resort just the date columns(/cells) for each row if a new date is written. I want the dates to be in ascending order. For instance, if I go back into the worksheet and replace Item A's date 5/2/14 with 1/1/16, I want the date columns JUST FOR Item A to reshuffle/resort to be:

A DV 6/12/14 7/11/15 1/1/16

without it resorting any other rows (Items) columns.

View 5 Replies View Related

Macro / VBA To Use 2 Columns In 1 Sort

May 13, 2013

I am trying to write a Macro to basically do a lot of redundant formatting for me and I am stuck on one part of this code. Everything else works just dandy, but I am having the darndest time getting this Macro to sort by 2 columns instead of just 1.

I did the basic first step and recorded myself sorting the datasheet by both columns in 1 sort and got the code, but the problem is that the number of rows will change day-to-day, week-to-week, and so on, so I need it to not specify a cell number as the end of the range.

While searching around, I saw a very handy little piece of VBA code that will search for a header name, which is really nice because that would make it much easier to share this Macro with my coworkers who have different reports that all have columns in different locations.

So, I have a multi-part question. I will start with the most important one:

How do I alter this to not specify A9590 and D9590 as the last cells in those ranges?

ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Add Key:=Range( _
"A6:A9590"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Add Key:=Range( _
"D6:D9590"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _


Is there a way to have the Macro find where the header row is? The header rows in our reports can appear anywhere from Row 1 to Row 8 depending on how the report is set up (usually we have some information about the data, date range, etc at the top).

Dim c As Integer
With ActiveWorkbook.Worksheets("Date XREF").Range("A5").CurrentRegion
c = .Find(What:="Assignment ID", After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
.Sort Key1:=.Cells(1, c), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

View 2 Replies View Related

Macro To Sort Three Columns Into One

Jun 25, 2013

I have a sheet that looks like this...

Start #
End #


[Code] ........

And I want to sort it to look like the following, in order to track each number to a date and to enter into an external database...





I need to list the entire thing instead of having starting and ending part #s. Any macro to do this? The actual spreadsheet has 250 dates and over 30k part #'s so doing it out is not really an option.

View 9 Replies View Related

Sort Columns By A Button

Apr 6, 2007

I have a code with perfectly sorts Excel rows when you click a button:

Private Sub CommandButton1_Click()
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending
End Sub
but if i like to sort the columns by Row 1 how i can do it?

View 9 Replies View Related

To Sort The Objects In Columns A, B, & C

Apr 23, 2007

Here is the Sheet so far: ...

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved