Hello Friends. Welcome to Work Tips by Data Accumulation. Whatever field you may be from, we all faced this one common issue. That it, figuring out the right set(s) of rows and /or columns to get a desired number.
For example, Table-A1 consist of different set of fruits and their relevant cost. If A spends 30, the right combination that he would have spend is Apple & Strawberry, Orange or Mango & Banana. It easier to figure out in the given list. But how about a list consists of thousands of data. Can you figure it out on one-on-one basis. Let us see an easier method.
Skill required - Textjoin, Conditional formatting and remove duplicates.
For easy reference, I will restrict the list to 25. But, the method remains the same.
1. Copy paste the amount in a separate sheet ( hereafter referred as WD).
2. WD. Select the values and remove duplicate. (Hint: Alt+A and M or you can use the option under data tab.)
3. Use Textjoin function with If formula. Don't forgot to use ctrl+shift+enter.
4. Now remove the extra spacing by using replace function first "space & ," and again with " , & space".
Hint:( Leave replace with blank)
5. Insert a column twice in the middle. Fill up the first column with the desired number divided by 2 ( For the example, I consider 50,000 as my desired number, so I will get 25,000). In the second column, minus the original value and previous column. Do the same for remaining rows ( Hint: Use Ctrl+d or copy paste the formula).
6. Now copy paste the values. Remove minus. Hint:( Leave replace with blank). And sort column 3 in the table. And use conditional formatting the figure out the set. Pair-3 is a direct identification, not a pair.
This method help you the find the best of the two pairs. Kindly visit the others areas of your interest.
Thank you.
0 Comments