CODEWITHSUNDEEP
excelsearchexcel-formula
Sparsh
Sparsh

Reputation: 37

Multi criteria search and returning multiple values from a table using array function

Blue region is the raw data, green region the data manipulation zone.

I am working on transportation of some employees whose route detail is given in blue region. Login means towards office and logout towards home. Same route ID means that those employees are traveling together in same cab. No of employees suggest how many employees are traveling in each trip.

I want to make a summary report as shown in green region. I want to know with how many employees each employee traveled with - i.e. the yellow highlighted region. The cell shows me that employee d for Login 05:00 shift has either traveled alone or with only one other employee. I want these values for the whole Clubbing column.

To achieve this, I have used the formula shown in formula bar, in the highlighted cell. On pressing F9, I am getting the result like the one in yellow highlighted cell.

But my formula seems a little clumsy. Also, to get comprehensible results, I have to go to each cell in Clubbing column and hit F9 which is unwieldy (this table is small, I have hundreds of thousands of entries).

Is there a more efficient and lucid way of getting this result? Please share. I am also open to VBA solutions.

Upvotes: 0

Views: 93

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use TEXTJOIN:

 =TEXTJOIN(",",TRUE,UNIQUE(FILTER($L$5:$L$21,($I$5:$I$21=O11)*($J$5:$J$21=P11),"")))

Upvotes: 1

Related Questions