• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find an item's group using a formula - no VBA, no Power Query

abaker77

New Member
Fluff, from MrExcel.com (see link) brilliantly presented a solution for the question: "How to find an item's group using a formula, without VBA or Power Query?"
I've attached a simple worksheet illustrating the example and solution. My question is: What if there are duplicate items and those items are found in multiple groups? The solution offered appears to return only the last group. (see attached.)
Thanks so much.
 

Attachments

bosco_yip

Excel Ninja
Try,

1] CSE formula in C4 :

=TEXTJOIN(", ",,IF(TableX=C3,TableX[#Headers],""))

Confirmed pressing with Ctrl+Shift+Enter entry

If you are using Office 365 CSE entry maybe not be required

2] Select B8:G14 >> Conditional formatting >> New rule and enter formula >>

Formula 1 : =B8=$C$3 , click Format >> Cell "Fill" -> Yellow

Then,

Formula 2 : =(COUNTIF($B$8:$G$14,B8)>1)*(B8=$C$3) , click Format >> Cell "Fill" -> Yellow, & Cell "Font" -> Red

>> Finish

76882
 

Attachments

abaker77

New Member
That is quite a wonderful solution. However, while this does take care of situation where the code may be duplicated across columns, there's still a problem if any code is duplicated within a single column. For example, if code160 were to appear twice, under GroupD, then the Textjoin function (which doesn't need CSE) would return multiple GroupDs.
 

bosco_yip

Excel Ninja
..... if any code is duplicated within a single column. For example, if code160 were to appear twice, under GroupD, then the Textjoin function would return ....GroupD.
Should duplicate code appeared in the column group, C4 formula changed to >>

1] For Office 365 :

=TEXTJOIN(", ",1,UNIQUE(IF(TableX=C3,TableX[#Headers],"")))

2] For Excel 2019 :

=TEXTJOIN(", ",,FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(TableX=C3,TableX[#Headers],""))&"</b></a>","//b[not(preceding::*=.)]"))

Confirmed pressing with Ctrl+Shift+Enter entry

76887
 
Last edited:

Peter Bartholomew

Well-Known Member
The purpose of this post is just to highlight the extent of the change that could result from the release of methods at present available within Excel 365 insider beta channel.
Code:
= LET(
  matches, TableX=code,
  ORλ,     LAMBDA(grp, OR(grp)),
  required, BYCOL(matches, ORλ),
  FILTER(TableX[#Headers], required))
First, every cell of the table is compared to the code in cell C3. A Lambda function ORλ is then defined to apply the built-in function OR by column, a task performed on the next line. Filter picks out the column headers with a matched code.
76912
 
Top