Criteria List

D

default_name

Board Regular
Joined
May 16, 2018
Messages
180
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hello all,

It was kind of tricky to come up with the best way to title this thread...my apologies for vagueness.

I have a pretty big spreadsheet with a lot of detailed items.
Rows A:CV are a bunch of details about an item.
Rows CV:DH are a list of categories...if the item meets certain Category characteristics, then it is listed as TRUE.
Basically, the table looks something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B to CV[/TD]
[TD]CW[/TD]
[TD]CX[/TD]
[TD]CY[/TD]
[TD]CZ[/TD]
[TD]DA[/TD]
[TD]DB[/TD]
[TD]DC[/TD]
[TD]DD[/TD]
[TD]DE[/TD]
[TD]DF[/TD]
[TD]DG[/TD]
[TD]DH[/TD]
[TD]DI[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Category A
[/TD]
[TD]Category B[/TD]
[TD]Category C[/TD]
[TD]Category D[/TD]
[TD]Category E[/TD]
[TD]Category F[/TD]
[TD]Category G[/TD]
[TD]Category H[/TD]
[TD]Category I[/TD]
[TD]Category J[/TD]
[TD]Category K
[/TD]
[TD]Category L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]a bunch of details about the item[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 2[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 3[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 4[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Item 5[/TD]
[TD]a bunch of details about the item[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7...
[/TD]
[TD]...thousands of items...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to figure out how to break down the results into a list on the same cell...something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B to CV[/TD]
[TD]CW[/TD]
[TD]CX[/TD]
[TD]CY[/TD]
[TD]CZ[/TD]
[TD]DA[/TD]
[TD]DB[/TD]
[TD]DC[/TD]
[TD]DD[/TD]
[TD]DE[/TD]
[TD]DF[/TD]
[TD]DG[/TD]
[TD]DH[/TD]
[TD]DI[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Category A
[/TD]
[TD]Category B
[/TD]
[TD]Category C
[/TD]
[TD]Category D
[/TD]
[TD]Category E
[/TD]
[TD]Category F
[/TD]
[TD]Category G
[/TD]
[TD]Category H
[/TD]
[TD]Category I
[/TD]
[TD]Category J
[/TD]
[TD]Category K
[/TD]
[TD]Category L
[/TD]
[TD]Possible Categories
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item 1[/TD]
[TD]a bunch of details about the item[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]Category A, Category D, Category H[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 2[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]Category B, Category C, Category G, Category H, Category I[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 3[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]Category E, Category F, Category J[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 4[/TD]
[TD]a bunch of details about the item[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]Category D, Category L[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Item 5[/TD]
[TD]a bunch of details about the item[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]Category A, Category E, Category F, Category I[/TD]
[/TR]
[TR]
[TD]7...[/TD]
[TD]...thousands of items...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Interpretation: Item 1 belongs Category A, Category D, and Category H.

Hope that makes sense.

Thanks in advance for your help!
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
If you have TextJoin, try


Book1
ABCDEFGHIJKLMNO
1Category ACategory BCategory CCategory DCategory ECategory FCategory GCategory HCategory ICategory JCategory KCategory L
2Item 1a bunch of details about the itemTRUEFALSEFALSETRUEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSECategory A Category D Category H
3Item 2a bunch of details about the itemFALSETRUETRUEFALSEFALSEFALSETRUETRUETRUEFALSEFALSETRUECategory B Category C Category G Category H Category I Category L
4Item 3a bunch of details about the itemFALSEFALSEFALSEFALSETRUETRUEFALSEFALSEFALSETRUEFALSEFALSECategory E Category F Category J
5Item 4a bunch of details about the itemFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSETRUECategory D Category L
6Item 5a bunch of details about the itemTRUEFALSEFALSEFALSETRUETRUEFALSEFALSETRUEFALSEFALSEFALSCategory A Category E Category F Category I
Sheet3
Cell Formulas
RangeFormula
O2{=TEXTJOIN(CHAR(10),TRUE,IF(C2:N2=TRUE,$C$1:$N$1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
If you don't have TextJoin, you can use
Code: 
Function DefaultName(Rng As Range, Hdr As Range) As String
   Dim i As Long
   For i = 1 To Rng.Count
      If Rng(1, i) = True Then DefaultName = DefaultName & Hdr(1, i) & Chr(10)
   Next i
   DefaultName = Left(DefaultName, Len(DefaultName) - 1)
End Function
and used like
=DefaultName(C2:N2,$C$1:$N$1)
 
Upvote 0
Fluff said:
If you don't have TextJoin, you can use
Code: 
Function DefaultName(Rng As Range, Hdr As Range) As String
   Dim i As Long
   For i = 1 To Rng.Count
      If Rng(1, i) = True Then DefaultName = DefaultName & Hdr(1, i) & Chr(10)
   Next i
   DefaultName = Left(DefaultName, Len(DefaultName) - 1)
End Function
and used like
=DefaultName(C2:N2,$C$1:$N$1)
Click to expand...

Wow! Thanks! I don't have TextJoin, but this code works great!
There's only one little tweak I am try to make to it...I want to separate each category with a comma.
Currently it's showing up like Category ACategory BCategory C

Where could I insert the comma into the code to fix this?
Thanks again! This is great!
 
Upvote 0
If you turn "Wrap text" on you will see that they are all on an individual line.
If you want commas instead, simply replace Chr(10) with ","
Alternatively the UDF can be modified to allow you to choose the delimiter.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Fluff said:
If you don't have TextJoin, you can use
Code: 
Function DefaultName(Rng As Range, Hdr As Range) As String
   Dim i As Long
   For i = 1 To Rng.Count
      If Rng(1, i) = True Then DefaultName = DefaultName & Hdr(1, i) & Chr(10)
   Next i
   DefaultName = Left(DefaultName, Len(DefaultName) - 1)
End Function
and used like
=DefaultName(C2:N2,$C$1:$N$1)
Click to expand...

I have just noticed that in the few instances where all the choices are FALSE, that it results in a #VALUE ! error.

Is there a way that, instead of a #VALUE ! error I could get a string of text that says something like "n/a"?
 
Upvote 0
How about
Code: 
Function DefaultName(Rng As Range, Hdr As Range) As String
   Dim i As Long
   For i = 1 To Rng.Count
      If Rng(1, i) = True Then DefaultName = DefaultName & Hdr(1, i) & [COLOR=#ff0000]Chr(10)[/COLOR]
   Next i
   If Len(DefaultName) = 0 Then
      DefaultName = "NA"
   Else
      DefaultName = Left(DefaultName, Len(DefaultName) - 1)
   End If
End Function
You may need to change the part in red to the delimiter you are using
 
Upvote 0
Fluff said:
How about
Code: 
Function DefaultName(Rng As Range, Hdr As Range) As String
   Dim i As Long
   For i = 1 To Rng.Count
      If Rng(1, i) = True Then DefaultName = DefaultName & Hdr(1, i) & [COLOR=#ff0000]Chr(10)[/COLOR]
   Next i
   If Len(DefaultName) = 0 Then
      DefaultName = "NA"
   Else
      DefaultName = Left(DefaultName, Len(DefaultName) - 1)
   End If
End Function
You may need to change the part in red to the delimiter you are using
Click to expand...

I'm sorry, but how would I know what my delimiter value is?
The delimiter is how the list is broken up, right? (in this case by categories)
In your code you use a value of 10....is this because the string "Category A" is ten characters long?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Dynamic Data Validation
Replies
2
Views
109
thbutterflycollector_
T
V
  • Solved
Creating a new filtered list from a dynamically created list based on previous numbers
Replies
6
Views
129
DanteAmor
DanteAmor
F
  • Solved
Changing criteria range using the same cell reference based on data validation list header change
Replies
3
Views
174
Peter_SSs
Peter_SSs
M
  • Question Question
VLOOKUP?
Replies
1
Views
213
MeaclH
M
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

Share this page

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