Conditional Formatting Rows, and Matching and Sorting Two Sets of Data

[Alan L 185]

Hi

I want to match and sort two groups of data as shown in the ‘Before’ and ‘After’ worksheets in the attachment.

Basically, there are twenty students in a class with related information (ID, Gender and Tutor).

At Checkpoint 1, tutors provide a Projected Mark based on the students’ performance to date. This data is sorted by the Projected Mark and colour coded. At Checkpoint 2 tutors provide a revised Projected Mark based on the students’ progress. This data is also sorted by Projected Mark and colour coded. See the ‘Before’ worksheet for details.

I want to be able to keep the Checkpoint 1 data as is and move the Checkpoint 2 data so that it is in line with the ID in Checkpoint 1. See the ‘After’ worksheet for details.

Can someone please provide me with:
1. A formulae for Conditional Formatting that colour codes rows according to the Projected Mark (Red is < 40, Orange is ≥ 40 and ≤ 79, Light Green is ≥ 80 and ≤ 119, Dark Green ≥ 120).
2. A formula that will enable me to move the Checkpoint 2 data so that it is in line with the ID in Checkpoint 1

Thanks in advance and looking forward to seeing your responses

Alan

[slasherdan]

it will take 3 rules to do the conditional formatting you are requesting first click the use a formula to determine which cells to format option

first rule =$E3<40 click format and pick your color
2nd rule =AND(($M3>39),($M3<80)) click format and pick your color
3rd rule =AND(($M3>79),($M3<120)) click format and pick your color

Below is a suggested way to keep the students grades together regardless of scores.

	A	B	C	D	E	F	G
2	ID	Student	Gender	Tutor	Proj Mark 1	Proj Mark 2	Proj Mark 3
3	114	Student 6	M	ARC	22	45	56

in this example those formulas would be placed on cells E3 down to E22 and F3 down to F22 etc
(format painter to copy and paste formulas so you do have to do a lot of typing)
by doing it this way you have several options freeze frame column D so you will scroll between the scores with cells a3 - D3 always being seen.

hope that helps

[Alan L 185]

Thanks Slasherdan

I can see how the formulae will work for the conditional formatting the cell. How can I get it to colour the row?

The data for Checkpoints 1 and 2 are entered at different times into a database, then downloaded into a spread. So I need a formula that will match the IDs then move the Checkpoint 2 data so that it is inline with Checkpoint 1 data.

[pwnyadav007]

I think u want these type of conditional formatting check this out.....
i did conditional formatting in before sheet....



Conditional Formatting Rows, and Matching and Sorting Two Sets of Data.xlsx

[Alan L 185]

pwnyadav007

Thanks for trying. Unfortunately, there's no formulae in the before sheet.

[pwnyadav007]

i had done conditional formatting in it, when u change to marks of student it will automatically change its color according to ur condition which u provide

1. A formulae for Conditional Formatting that colour codes rows according to the Projected Mark (Red is < 40, Orange is ≥ 40 and ≤ 79, Light Green is ≥ 80 and ≤ 119, Dark Green ≥ 120).

[Alan L 185]

Yes you have, thanks.

Now are you able to complete the second part of my challenge, which is to be able to produce a formula that will enable me to move the Checkpoint 2 data so that it is in line with the ID in Checkpoint 1?

[pwnyadav007]

then check this i hide ur Checkpoint 2 and create a new checkpoint using formula
hope this will help



Conditional Formatting Rows, and Matching and Sorting Two Sets of Data (1).xlsx

[Alan L 185]

Cool! I'll try it out on some other data tomorrow and let you know how it goes.

Thank you.

[pwnyadav007]

Take this file in this file just put the data in before sheet( both checkpoint 1 and 2) and see the result in After sheet I hv also protect the data in after sheet so no one manipulate that
passwrd is 123


Conditional Formatting Rows, and Matching and Sorting Two Sets of Data (1).xlsx

[Alan L 185]

pwnyadav007

I want to thank you very much for helping me. Both formulae have worked well on the actual data.

This provides me with a great tool to help my students.

All the very best

Alan L