Need sorting help in Excel

syzygyproject

New Member
Joined
Jun 19, 2012
Messages
4
Hi,

I have a project that I need help with. I have 2 sets of data to sort, line up, and compare. The 2 data sets are comprised of 3 columns each. The first column in each have 5 total values that change only every couple hundred rows or so. The second column of each changes every 2-3 rows. The third row will only be used to compare the data sets once I can get the sorting correct. The data sets are very similar....the first is smaller than the second. What I need initially to do (and if I can only get help with this one thing it would save me many hours, if not days, of work) is line up the first and second columns of each data set by comparing the first two columns on a row by row basis and moving the first data set down to the corresponding row in the second data set. Here is a sample of what I am doing....

aaAaaBaaaCaaaDaEaaaF
1 70 100 1200 70 98 1100
2 70 100 1200 70 98 1100
3 70 100 1200 70 98 1100
4 70 101 1300 70 100 1201
5 71 101 1300 70 100 1201
6 71 101 1300 70 100 1201
.
.
.


The data sets in this example are A, B, and C as the first data set, and D, E, and F as the second data set. Columns C and F will only be used later in a comparison of their values, but the first thing I need to be able to do is get (in the example above) cells A1, B1 (and C1) moved down to where cells A1 and B1 will match cells D4, E4 as per below...

aaAaaBaaCaaaaDaaEaaaF
1aaaaaaaaaaaa70 98 1100
2aaaaaaaaaaaa70 98 1100
3aaaaaaaaaaaa70 98 1100
4 70 100 1200 70 100 1201
5 70 100 1200 70 100 1201
6 70 100 1200 70 100 1201
.
.
.
The columns are about 3000 lines long, and when cells A1, B1 and C1 are moved to match those in columns D, E, and F, I also of course need the remaining cells in columns A, B, and C that were moved to match the desired D and E cells, to move down as well. The second data set in columns D, E (the value in column F won't necessarily match that of column C but will need to move down with the corresponding A and B cells) contains all of the first data set and more. Thus the need to move the first data set to match row by row of the second data set. The values in columns A and B will generally be in identical 3 cell clumps, as will those in columns D and E. In the example above, note then that in rows 4, 5 and 6 that cells A4 and B4 match cells D4 and E4; cells A5 and B5 match D5 and E5; and cells A6 and B6 match cells D6 and E6.

What I have been doing, which is insane, is to highlight say cells A1, B1, and C1, then also highlight all the cells below those to the end of the spreadsheet (again, maybe about 3000 rows), then go back up to where I started highlighting cells, and dragging the whole highlighted area down to where columns A and B match the values in columns D and E. Once I do that, I have to then re-highlight starting at the next point where the column A and B values don't match those in columns D and E and keep doing this. There must be a better way?

Once these are all sorted and matched, is there also a way to have Excel highlight the third column values that don't match between the two data sets? In the example above, once the cells in the first data set are moved down to match the cells in the second data set, you can see that the third column in each doesn't match the other. For example, even though cells A1-B3 (and C1-C3) were moved down to match D4 - E 6, you can see that cells C4-C6 don't match cells F4-F6. Can F4-F6 be highlighted if they aren't the same as C4-C6?

I hope I explained this well enough. Please let me know if I need to provide more info for you to help. I am not much of a macro guy, so you might need to explain in excruciating detail how to implement a macro etc.

Thanks for any and all help!!
 
Last edited:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Use the following formula in the 1st row of a helper column in the 1st data set and copy down to the end of data:
=CONCATENATE(A1,"-",B1,"-",C1)
and then do the same in the 2nd data set (adjust cell references as appropriate).
Also keep each dataset on a separate worksheet within the same workbook.

Now you can compare the 2 data sets using VLOOKUP formulas with the helper columns as the reference point.
If you need help with the VLOOKUP formula, let me know the names of your 2 worksheets, columns containing the data in each, the helper column in each and range names assigned (if any).
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
Hi,

What I have been doing, which is insane, is to highlight say cells A1, B1, and C1, then also highlight all the cells below those to the end of the spreadsheet (again, maybe about 3000 rows), then go back up to where I started highlighting cells, and dragging the whole highlighted area down to where columns A and B match the values in columns D and E. Once I do that, I have to then re-highlight starting at the next point where the column A and B values don't match those in columns D and E and keep doing this. There must be a better way?

There sure is. eg:
Select A1:C3.
Right Click on selection.
Click "Insert" on the context menu.
In the box which appears, select "Shift cells down"
Press "OK"

Once these are all sorted and matched, is there also a way to have Excel highlight the third column values that don't match between the two data sets? In the example above, once the cells in the first data set are moved down to match the cells in the second data set, you can see that the third column in each doesn't match the other. For example, even though cells A1-B3 (and C1-C3) were moved down to match D4 - E 6, you can see that cells C4-C6 don't match cells F4-F6. Can F4-F6 be highlighted if they aren't the same as C4-C6?

Conditional formatting is one way to highlight differences.
Select the whole column F with F1 as active cell.
Select "Conditional Formatting..." from "Format" menu (Excel 2003)
Condition: "Cell value is not equal to" =C1
Set Format: eg red font
 

syzygyproject

New Member
Joined
Jun 19, 2012
Messages
4
Hi RonB1111.....thanks for the reply. I don't know much about VLOOKUP, used it years ago but have lost knowledge of it. If you could help that would be great. The two worksheets are named SAM and ZITE. The three columns of data are A, B, and C in each worksheet. The helper column is column D in each worksheet. I'm not sure what you mean by range names....didn't name anything so I don't think that would apply?

Thanks.

Dan
 

syzygyproject

New Member
Joined
Jun 19, 2012
Messages
4

ADVERTISEMENT

Thank you drsarao. Will this work with Excel 2010?
 

RonB1111

Well-known Member
Joined
Nov 28, 2011
Messages
2,277
Hi RonB1111.....thanks for the reply. I don't know much about VLOOKUP, used it years ago but have lost knowledge of it. If you could help that would be great. The two worksheets are named SAM and ZITE. The three columns of data are A, B, and C in each worksheet. The helper column is column D in each worksheet. I'm not sure what you mean by range names....didn't name anything so I don't think that would apply?

Thanks.

Dan

Assume your data starts in row 2:

Enter this formula in D2 and copy down to the end of data in each worksheet:
=CONCATENATE(A2,"-",B2,"-",C2)

Enter this formula in E2 of the worksheet named SAM and copy down to the end of data:
=VLOOKUP(D2,ZITE!D:D,1,FALSE)

Enter this formula in E2 of the worksheet named ZITE and copy down to the end of data:
=VLOOKUP(D2,SAM!D:D,1,FALSE)

The way you interpret the results in Col E of both worksheets is:
a) Any cell in Col E containing an answer, such as 70-100-1200, means there is an exact match in the other worksheet of all 3 columns
b) Any cell in Col E containing #N/A means there was no exact match in the other worksheet
 

Watch MrExcel Video

Forum statistics

Threads
1,123,053
Messages
5,599,524
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top