Hello everyone,
I was wondering if someone could give me a hand with two questions:
First question, I got a column, A, with individuals IDs and a column B, with results matching each one of the IDs. to Analyse the data I need to have a third column, C with contain some duplicates with column A but not all. Is there a way to if the ID matches on column A and C to automatically add the result from column B in front (as in, column D) of the respective duplicated on column C? Not all values in column C will have necessarily a match result. I've managed to do this manual and with the usual highlight duplicates, removing the not duplicated ones and by ordering them, but due to the amount of data was wondering if there was a more automatic way of doing it.
My second doubt is regarding a scoring system. I want to rank/score (for example score 0, 1, 2 and 3) certain values based on different variable, with some variables having more weight than others in the scoring. I've gotten as far as using the following formulas:
=SUM(I2:I4)
=SUMPRODUCT(I2:I4,$F$2:$F$4)
=I8/MAX($H$8:$DE$8)
Which seemed to me going in the right direction, but then for the last bit I can only find something like =RANK(I9,$H$9:$DE$9) which indeed ranks them, but not in the right way as Ideally I wanted it in a score of 0, 1, 2 and 3 fitting the values within this score. For example if final value is above 100 then score 3, if between 50 and 100 Score 2... Just an example not necessarily this values as that I could work it out later on.
Both this question didn't seem too hard in the beginning, and probaly aren't for someone that knows what is doing ahah but it's been hunting me the last few days and can't get my head around it.
Appreciate any help or hints, I've upload a test spreadsheet with both questions on different sheets and hopefully i've managed to explain the situation decently, do let me know if you need any more information.
1drv.ms
I was wondering if someone could give me a hand with two questions:
First question, I got a column, A, with individuals IDs and a column B, with results matching each one of the IDs. to Analyse the data I need to have a third column, C with contain some duplicates with column A but not all. Is there a way to if the ID matches on column A and C to automatically add the result from column B in front (as in, column D) of the respective duplicated on column C? Not all values in column C will have necessarily a match result. I've managed to do this manual and with the usual highlight duplicates, removing the not duplicated ones and by ordering them, but due to the amount of data was wondering if there was a more automatic way of doing it.
1528 | J0 | 4932 |
2039 | J0 | 4936 |
2316 | J0 | 5501 |
2371 | J0 | 4912 |
2410 | J0 | 4978 |
2466 | J0 | 2557 |
2520 | J0 | 2566 |
2548 | J0 | 2605 |
2557 | J0 | 2702 |
My second doubt is regarding a scoring system. I want to rank/score (for example score 0, 1, 2 and 3) certain values based on different variable, with some variables having more weight than others in the scoring. I've gotten as far as using the following formulas:
=SUM(I2:I4)
=SUMPRODUCT(I2:I4,$F$2:$F$4)
=I8/MAX($H$8:$DE$8)
Which seemed to me going in the right direction, but then for the last bit I can only find something like =RANK(I9,$H$9:$DE$9) which indeed ranks them, but not in the right way as Ideally I wanted it in a score of 0, 1, 2 and 3 fitting the values within this score. For example if final value is above 100 then score 3, if between 50 and 100 Score 2... Just an example not necessarily this values as that I could work it out later on.
| Weight | Ear Tag | 1979 | 2308 | 2127 | 2222 | 2367 |
5 | Lumps | 7 | 6 | 5 | 5 | 3 |
5 | Lumps + | 17 | 17 | 11 | 10 | 20 |
100 | IR | 1 | 0 | 1 | 0 | 0 |
| Total | 25 | 23 | 17 | 15 | 23 | |
| weighted | 220 | 115 | 180 | 75 | 115 | |
1 | 0.522727 | 0.818182 | 0.340909 | 0.522727 | ||
1 | 29 | 4 | 31 | 29 |
Both this question didn't seem too hard in the beginning, and probaly aren't for someone that knows what is doing ahah but it's been hunting me the last few days and can't get my head around it.
Appreciate any help or hints, I've upload a test spreadsheet with both questions on different sheets and hopefully i've managed to explain the situation decently, do let me know if you need any more information.
