CODEWITHSUNDEEP
excelexcel-formula
gowthamnvv
gowthamnvv

Reputation: 307

In excel, how to get comma separated values from a column based on search criteria on another column

For example, see the table below with two columns.
Active User Ids

I need to get comma separated active user ids (where Active = 1) in a cell, as shown in the "Expected Output". There could be n rows in the users table.

Can anyone suggest an excel formula to achieve this (without using VBA code)?

Upvotes: 1

Views: 2105

Answers (2)

devuxer
devuxer

Reputation: 42354

Here's a way to do this purely with Excel formulas. I don't think it can be done purely with a single Excel formula, but you could at least hide the column that contains the working formulas you don't want users to see.

  • Enter Working into cell C1.
  • Enter =SUBSTITUTE(IF(B2=1,C1&","&A2,C1),C$1&", ","") into cell C2.
  • Drag cell C2 down as far as necessary to cover the longest list of data you could possibly have.
  • Type =INDEX(C:C,MAX(IF(C2:C10000="",0,ROW(C2:C10000)))) into cell D5 and press Ctrl+Shift+Enter to enter it as an array formula.
  • If desired, hide column C by right-clicking on the column header and selecting Hide. This will prevent users from seeing Column C. (You may wish to take other protective measures, such as locking your worksheet and only allowing users to edit Column A and Column B.)

Here's what your example looks like with the "Working" column:

UserId  Active  Working 
26001        0  Working 
26002        1  26002   
26003        0  26002                Expected Output:
26004        0  26002                26002, 26005, 26010
26005        1  26002, 26005    
26006        0  26002, 26005    
26007        0  26002, 26005    
26008        0  26002, 26005    
26009        0  26002, 26005    
26010        1  26002, 26005, 26010

And here's what your example looks like with formulas turned on:

UserId  Active  Working 
26001        0  =SUBSTITUTE(IF(B2=1,C1&", "&A2,C1),C$1&", ","") 
26002        1  =SUBSTITUTE(IF(B3=1,C2&", "&A3,C2),C$1&", ","") 
26003        0  =SUBSTITUTE(IF(B4=1,C3&", "&A4,C3),C$1&", ","")    Expected Output:
26004        0  =SUBSTITUTE(IF(B5=1,C4&", "&A5,C4),C$1&", ","")    =INDEX(C:C,MAX(IF(C2:C10000="",0,ROW(C2:C10000))))
26005        1  =SUBSTITUTE(IF(B6=1,C5&", "&A6,C5),C$1&", ","") 
26006        0  =SUBSTITUTE(IF(B7=1,C6&", "&A7,C6),C$1&", ","") 
26007        0  =SUBSTITUTE(IF(B8=1,C7&", "&A8,C7),C$1&", ","") 
26008        0  =SUBSTITUTE(IF(B9=1,C8&", "&A9,C8),C$1&", ","") 
26009        0  =SUBSTITUTE(IF(B10=1,C9&", "&A10,C9),C$1&", ","")   
26010        1  =SUBSTITUTE(IF(B11=1,C10&", "&A11,C10),C$1&", ","")

What do the formulas do?

=SUBSTITUTE(IF(B2=1,C1&","&A2,C1),C$1&", ","") checks to see if there is a 1 in column B. If yes, it takes what's above and adds a comma, space, and the UserId. If no, it simply takes what's above. To eliminate the word "Working" and additional separator characters, "Working, " is replaced with a blank.

=INDEX(C:C,MAX(IF(C2:C10000="",0,ROW(C2:C10000)))) searches for the first row in column C that contains a blank (which in this case is 11), then grabs that item from column C (which in this case is C11).

Upvotes: 0

yoyoyoyo123
yoyoyoyo123

Reputation: 2472

you could run some VBA for that sheet

Sub tester()
Dim last As Integer
Dim i As Integer
Dim myString As String
myString = ""

last = Range("A1").End(xlDown).Row
For i = 2 To last
    If Range("B" & i).Value = 1 And myString = "" Then
    myString = Str(Range("A" & i).Value)
    ElseIf Range("B" & i).Value = 1 Then
         myString = myString + "," + Str(Range("A" & i).Value)
    End If
Next i
    Range("C2").Value = myString
End Sub

Upvotes: 1

Related Questions