Distinguish "text" in =REPT(text, COUNTIFS(...))

[bruh]

Hi guys. I have this table that I'm using to plot 3 different three criteria (size, weight, and eaten or not) for the tables on the right.

What I want to do is take the "help" cells and specify which fruit met that criteria. For instance, in cell J3, I want to change "help" to the fruit symbol "W" from B3. Especially, for cases like H5 where there are 2 different fruits that meet the small & light criteria (Kiwi and Grape) and make cell H5 read "K, G" instead of Help help".

Basically, in cell I4, the Orange met all 3 criteria (medium, average, eaten = no). Therefore, I want cell I4 to read "O". That's basically it. For all cells in those two tables on the right.

What would be more convenient if I could reference column B for all the fruit symbols. I want the same to happen for all the other fruits.
I'm not even sure =REPT() is the right way to go about this... Thank you in advance for the help.

 

[bebo021999]

With Ex365 its easy to combine text string with formula
For your version Ex2016, VBA can do it. Does it accepted?

 

[bruh]

VBA? I'm not sure how to use that... I'm pretty novice with excel. Is there a simple equation or formula that I can use for this?

 

[bebo021999]

VBA? I'm not sure how to use that... I'm pretty novice with excel. Is there a simple equation or formula that I can use for this?

It requires text string combination, like "A,W,P,..." with items quantity vary. Ex2016 is not strong for text combination.
Use UDF (User Define Formula) is so easy. Just create a piece of code, named it as function
I will try it and come back later.

 

[bebo021999]

Since you are new VBA user, try following steps:
- Right click on sheet name/ choose view code
- Insert/Modules/Module1
- Copy below code, paste into right editing window
- To run code, hit play button (or F5) . Or you can create a button in worksheet ("RUN"), right click on that button then choose "assign Macros" then pick the macro name "test"

Option Explicit
Sub test()
Dim i&, rng, cell As Range
Dim S As String, W As String, E As String, com As String, st As String

'this array to combine text string in the 3 columns C,D,E. Adjust to actual range.
rng = Evaluate(Range("B2:B7").Address & "& ""|"" & " & Range("C2:C7").Address & _
        "& ""-"" & " & Range("D2:D7").Address & "& ""-"" & " & Range("E2:E7").Address)
      
'this is an union of the two results range. Adjust to actual range.
Union(Range("H3:J5"), Range("M3:O5")).ClearContents

For Each cell In Union(Range("H3:J5"), Range("M3:O5"))
    st = ""
    S = Cells(cell.Row, IIf(cell.Column < 13, 7, 12)).Value ' Column L=13, Column G = 7. Adjust to actual column indexes.
    W = Cells(6, cell.Column).Value ' 6 = row index of weight condition in row 6
    E = IIf(cell.Column < 13, "no", "yes")
    com = S & "-" & W & "-" & E
    For i = 1 To UBound(rng)
        If com = Split(rng(i, 1), "|")(1) Then st = st & IIf(st = "", "", ", ") & Split(rng(i, 1), "|")(0)
    Next
    cell.Value = st
Next
End Sub

I also attach sample file here

Test.xlsm

drive.google.com

 

[bruh]

Wow thank you so much for all this code. But I have to ask, is there no simpler way of doing this only using formulas from Excel 2016?

 

[bebo021999]

Wow thank you so much for all this code. But I have to ask, is there no simpler way of doing this only using formulas from Excel 2016?

It is possible, if there were just a few text to concatenate (6, in this sample).
To use formula, just like this:
=IF(B2=..., IF(C2=...,IF(D2=..., ...
IF(B3=,...
...
IF(B6=...
I believe that not a proper way to do it with formula in Ex2016

 

[bebo021999]

Another way is creating helper columns/rows/range. Not be encouraged.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, you will get better/faster help if you provide your sample data (& expected results) with XL2BB so I suggest that you investigate that.

I agree with @bebo021999 that Excel 2016 functions do not lend themselves to situations where there could be 1 or more of several text values to be joined.
So I am also offering a vba suggestion but by creating a special function (user-defined function or UDF) that you can use in your worksheet more like a standard function.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down in each result section.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Fruit(rData As Range, sSize As String, sWeight As String, sEaten As String) As String
  Dim d As Object
  Dim a As Variant
  Dim s As String
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = rData.Value
  For i = 1 To UBound(a)
    s = a(i, 3) & "|" & a(i, 4) & "|" & a(i, 5)
    d(s) = d(s) & ", " & a(i, 2)
  Next i
  Fruit = Mid(d(sSize & "|" & sWeight & "|" & sEaten), 3)
End Function

Notice that I have suggested a slight layout change at the top of each result section.

bruh.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1			Size	Weight	Eaten?				Eaten					Eaten
2	Apple	A	medium	average	yes				no					yes
3	Watermelon	W	large	heavy	no		large			W		large
4	Peach	P	medium	average	yes		medium		O			medium		A, P
5	Kiwi	K	small	light	no		small	K, G				small
6	Grape	G	small	light	no			light	average	heavy			light	average	heavy
7	Orange	O	medium	average	no
Lookup

Cell Formulas
Range	Formula
H3:J5	H3	=Fruit($A$2:$E$7,$G3,H$6,$I$2)
M3:O5	M3	=Fruit($A$2:$E$7,$L3,M$6,$N$2)

 

[cmowla]

@bruh ,
You could just select your table in the top left, press Ctrl T to convert it to a table (where you check my table has headers). And then use the filters (arrows) to filter out each of the criteria. Then see all of the (surviving) symbols in column B.