Duplicates question and scoring system

Newbie73

Board Regular
Joined
Feb 4, 2024
Messages
109
Office Version
  1. 365
Platform
  1. Windows
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.

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.

WeightEar Tag19792308212722222367
5​
Lumps76553
5​
Lumps +1717111020
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.

 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
1st problem:
Excel Formula:
=if(isnumber(xmatch(A1,C:C)),concat(B1,A1),"")
Copy down column D

2nd problem:
Excel Formula:
=XMATCH(I8,SORT($I$8:$DE$8,,-1,1))
 
Upvote 0
1st problem:
Excel Formula:
=if(isnumber(xmatch(A1,C:C)),concat(B1,A1),"")
Copy down column D

2nd problem:
Excel Formula:
=XMATCH(I8,SORT($I$8:$DE$8,,-1,1))

Hello MountainFog and thanks for your help! First formula looks promising but two problems. Ideally I wanted just the result of column B and not the ID written again, as in only J0,J1,J2. Another problem, or perhaps I didn't explained correctly in the first post, is that Column B as results for the IDs on Column A, and column C have certain IDs that are also present in Column A, those ones I would like the pull the respective result of Column B to Column D, so that Column C and B are matching with the ID and respective results. For example if I look at D42 from the test spreadsheet you've helped, D42 should be from number 4002 (C42) where the test result is on B61 (A61=4002 so is unique value is B=61 which is J0 and not J2 as currently shown in D42 as C42=4002

2nd problem

Sorry might be my fault again, but don't think is quite working for what I need, it just seems to have the same values as =RANK(H9,$H$9:$DE$9) which is not what I intend. I would like to have a small scoring table from 0 to 3 and then counting how many would match on that scoring with the different weight variable that are on the spreadsheet (possibly adding more in the future or changing their weight). Looking at H8 and others on that row, would like a score that if its 100 or greater then H1 counts as score 3. If H8 is between 50 and 100, then H1 would be score 2. If h8 between 25 and 50 then H1 score 1 and bellow 25 score 0. Could the the whole with the scoring

I could do manually an example if you think it would help. Thanks!
 
Upvote 0
Hello MountainFog and thanks for your help! First formula looks promising but two problems. Ideally I wanted just the result of column B and not the ID written again, as in only J0,J1,J2. Another problem, or perhaps I didn't explained correctly in the first post, is that Column B as results for the IDs on Column A, and column C have certain IDs that are also present in Column A, those ones I would like the pull the respective result of Column B to Column D, so that Column C and B are matching with the ID and respective results. For example if I look at D42 from the test spreadsheet you've helped, D42 should be from number 4002 (C42) where the test result is on B61 (A61=4002 so is unique value is B=61 which is J0 and not J2 as currently shown in D42 as C42=4002

2nd problem

Sorry might be my fault again, but don't think is quite working for what I need, it just seems to have the same values as =RANK(H9,$H$9:$DE$9) which is not what I intend. I would like to have a small scoring table from 0 to 3 and then counting how many would match on that scoring with the different weight variable that are on the spreadsheet (possibly adding more in the future or changing their weight). Looking at H8 and others on that row, would like a score that if its 100 or greater then H1 counts as score 3. If H8 is between 50 and 100, then H1 would be score 2. If h8 between 25 and 50 then H1 score 1 and bellow 25 score 0. Could the the whole with the scoring

I could do manually an example if you think it would help. Thanks!
Example for Second Question:

WeightEar Tag
1979​
2308​
2127​
2222​
2367​
2309​
5​
Lumps
7​
6​
5​
5​
3​
3​
5​
Lumps +
17​
17​
11​
10​
20​
14​
100​
IR
1​
0​
1​
0​
0​
1​
Total
25​
23​
17​
15​
23​
18​
weighted
220​
115​
180​
75​
115​
185​
Score
3​
3​
3​
2​
3​
3​


Example for first question. Something like this: C1 matches A4 so D1=B4, same with C2, the others in this example have no match with A no have no values in D


2466​
J0
2557​
J0
2520​
J0
2566​
J0
2548​
J0
2605​
2557​
J0
2702​
2563​
J0
3864​
2566​
J0
4384​
2590​
J0
4387​
2593​
J2
4717​
 
Upvote 0
Do let me know if anyone needs more information about it. Thanks in advance
 
Upvote 0
I've managed to find the solution to question number one! =INDEX(B:B,MATCH(C1,A:A,0)) Did the trick.

Still trying to figure the scoring/ranking system question
 
Upvote 0
Excel Formula:
=XLOOKUP(XMATCH(H$8,SORT($H$8:$DE$8,,-1,1)),$D$13:$D$16,$E$13:$E$16,,-1)

Xlookup table below
0​
0​
25​
1​
50​
2​
100​
3​


Solution is also in your test file
 
Upvote 0
Solution

Forum statistics

Threads
1,224,465
Messages
6,178,820
Members
452,881
Latest member
motivationgyan

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
Back
Top