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