Select the cells for the optimal sum value in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2704301%22%20slang%3D%22en-US%22%3ESelect%20the%20cells%20for%20the%20optimal%20sum%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2704301%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20my%20first%20time%20to%20post%20a%20question%20in%20this%20community.%20I%20am%20looking%20forward%20to%20hearing%20any%20advices.%20Thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20select%20the%20sample%20IDs%20with%20the%20values%2C%20which%20are%20added%20up%20to%20be%20(closer%20to)%20a%20certain%20number%2C%20for%20instance%2C%2090%2C%20in%20the%20below%20table.%3C%2FP%3E%3CP%3EThe%20result%20would%20be%20the%20highlighted%20ones.%20Are%20there%20any%20functions%20or%20formulas%20to%20do%20so%3F%3C%2FP%3E%3CP%3EThanks%20very%20much.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22352%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CSTRONG%3ESample%20ID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CSTRONG%3E%23%20of%20sample%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E21-070301-0001%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E21-072163-0001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E25%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E21-072165-0001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E15%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E21-072167-0001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E20%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E21-072169-0001%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E10%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E21-072445-0001%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E21-072552-0002%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22176%22%3E%3CFONT%20color%3D%22%23FF0000%22%3E20%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2704301%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2704405%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20the%20cells%20for%20the%20optimal%20sum%20value%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2704405%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142736%22%20target%3D%22_blank%22%3E%40liziwei0504%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20explain%20in%20bit%20more%20details%2C%20better%20on%20sample%20file%2C%20why%20ID%3Bs%20in%20red%20are%20close%20to%2090%20and%20other%20are%20not.%20And%20would%20you%20like%20to%20select%20or%20to%20highlight%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone,

 

It's my first time to post a question in this community. I am looking forward to hearing any advices. Thanks in advance.

 

My goal is to select the sample IDs with the values, which are added up to be (closer to) a certain number, for instance, 90, in the below table.

The result would be the highlighted ones. Are there any functions or formulas to do so?

Thanks very much. 

 

Sample ID# of sample
21-070301-000118
21-072163-000125
21-072165-000115
21-072167-000120
21-072169-000110
21-072445-000115
21-072552-000220
3 Replies

@liziwei0504 

Could you please explain in bit more details, better on sample file, why ID;s in red are close to 90 and other are not. And would you like to select or to highlight?

@liziwei0504 

See the attached workbook. You'll have to allow macros.

It's slow and could be improved.

Hello Sergei,
Thanks for your reply. I am sorry there might be confusion about the sample ID. Actually, it should be the case ID. For instance, there are 18 samples in case 21-070301-0001. My goal is to select the cases out with the sum of their total number of samples closest to 90 since we are templating the samples into a 96-well microplate (exclude 6 controls).
I would like to create a new list with the selected ones if possible.
Hope it'll make the request clearer.
Thanks.