Trout58
07-28-2017, 07:04 AM
Hi All
Spent ages on the web testing lots of different options for comparing two worksheets in the same workbook using formulas and conditional formatting but they either don’t work at all or don’t return the results how I would ideally like them to. I know exactly what I want but only have basic skills or knowledge so don’t know how to get it or, maybe it can’t be achieved.
I hope those with far more experience will let me know if I’m dreaming or not lol!
I have the capacity to run and export a real time Excel membership list form a parent body whenever I want to. These reports comprise 15 columns with one row per member and up to 25,000 rows (members).
1st data cell is B5 – end data cell is P25000
As a rule, there is always a variance between reports in respect to the number of members on each report for varying reasons, most of which are legitimate. However, with that volume of records it’s simply not practical to manually look for which members may have dropped off (or been added) to the membership list between runs so ‘ideally’ I would like a formula / macro that would do the following:
The workbook has three sheets: 1_Previous, 2_Current, 3_Comparison,
a. I would like a formula / macro that will compare 1 against 2 and enter the results in 3 in the following way;
b. So, if Mary Smith’s record appears in 1 but not in 2, then enter her entire record row in 3;
c. Enter all such records in 3 with the same background (conditional formatting) colour;
d. If, Bob Brown doesn’t appear in 1 but does appear in 2, then enter his entire record row in 3;
e. Enter all such records in 3 with a different background (conditional formatting) colour; and
f. All records to appear in 3 in alphabetical order by member Surname, which is column C.
I don’t want the comparison on the basis of rows only i.e. if Mary Smith's record appears in say row 18791 in 1 but drops of and doesn’t appear in 2; it is of no importance to me to know whose record now appears in row 18791 in 2. But, it is essential that Mary Smith's record appears in 3 with the allocated background colour that immediately tells me she was in 1 but is not in 2.
Have attached a sample workbook that shows exactly what i would like to happen in a perfect world - maybe it can't - appreciate any help on this
Cheers19914
Spent ages on the web testing lots of different options for comparing two worksheets in the same workbook using formulas and conditional formatting but they either don’t work at all or don’t return the results how I would ideally like them to. I know exactly what I want but only have basic skills or knowledge so don’t know how to get it or, maybe it can’t be achieved.
I hope those with far more experience will let me know if I’m dreaming or not lol!
I have the capacity to run and export a real time Excel membership list form a parent body whenever I want to. These reports comprise 15 columns with one row per member and up to 25,000 rows (members).
1st data cell is B5 – end data cell is P25000
As a rule, there is always a variance between reports in respect to the number of members on each report for varying reasons, most of which are legitimate. However, with that volume of records it’s simply not practical to manually look for which members may have dropped off (or been added) to the membership list between runs so ‘ideally’ I would like a formula / macro that would do the following:
The workbook has three sheets: 1_Previous, 2_Current, 3_Comparison,
a. I would like a formula / macro that will compare 1 against 2 and enter the results in 3 in the following way;
b. So, if Mary Smith’s record appears in 1 but not in 2, then enter her entire record row in 3;
c. Enter all such records in 3 with the same background (conditional formatting) colour;
d. If, Bob Brown doesn’t appear in 1 but does appear in 2, then enter his entire record row in 3;
e. Enter all such records in 3 with a different background (conditional formatting) colour; and
f. All records to appear in 3 in alphabetical order by member Surname, which is column C.
I don’t want the comparison on the basis of rows only i.e. if Mary Smith's record appears in say row 18791 in 1 but drops of and doesn’t appear in 2; it is of no importance to me to know whose record now appears in row 18791 in 2. But, it is essential that Mary Smith's record appears in 3 with the allocated background colour that immediately tells me she was in 1 but is not in 2.
Have attached a sample workbook that shows exactly what i would like to happen in a perfect world - maybe it can't - appreciate any help on this
Cheers19914