CODEWITHSUNDEEP
excelexcel-formula
Andy P
Andy P

Reputation: 11

Concatenate values based on criteria

I have a two column list of data in Excel. The first column being a question number from a test and the second column being a number referencing what is being tested on that question. Some elements are tested on more than one question. What I want to be able to do is to list the question numbers that each element is tested on. For example:

   A            B      Should return:   C            D

1  Q            Ref                     Q            Ref
2  1            N1                      1,3,5        N1
3  2            N4                      2            N4
4  3            N1                      4            N3
5  4            N3
6  5            N1

I want this to be returned using a formula.

Problems I have are returning then concatenating an unspecified number of values from one column that reference to a particular criterion for another column that is further to the right.

EDIT: Looking for a formula answer, not VBA if possible

EDIT: Thanks all for your comments so far. I will have a look at each of the possible solutions given so far and let you know what I go with. The 1,2,3 etc will need to be in the same cell.

Upvotes: 1

Views: 14599

Answers (3)

Fouad Amhaz
Fouad Amhaz

Reputation: 1

please use the below formula to find out all cells of specific value

=Textjoin(",",TRUE, Filter([Name Column], [Contract Column] = [Contract Cell]))

this formula will filter all cells in the target column with respect of another column and merge them with a comma symbol between them. and no need for sort.

for the above example: if you put in cell D2 the below =TEXTJOIN(",",TRUE,FILTER(A:A,B:B=E2)) and drag down.

FORMULA

Upvotes: 0

user4039065
user4039065

Reputation:

By nature, Excel discourages this in worksheet formulas. I guess they figure that if you do this in a User Defined Function (aka UDF) and it hoops a workbook, it is your own fault and so be it. To that end, I've never seen a standard or array formula using only native worksheet functions that accomplishes this on a 'ragged-edge' array of cells and it's been tried a few times. Consider it #REF! by design.

You can run successive IF functions (up to 64 by xl2007+ standards) to accomplish the string stitching (see this) but you will also be limited to the total length of a formula (see this). We also used 'helper' cells to run off the first 7 IFs in <=xl2003 then reference that cell in the first IF of another 7 nested IFs (rinse and repeat).

TLDR; In short, VBA is your most viable solution (see this). Conditional string concatenation is fraught with problems by itself let alone in an array loop.

CONCATENATE function

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Just to put my comment in an answer, so it make more sense.

First sort columns A and B on Column B.

In C2 put the formula:

=IF(B2=B3,A2&","&C3,A2)

Then copy down.

Then in Column E place your unique reference list. And in D2 put:

=VLOOKUP(E2,$B$2:$C$6,2,FALSE)

And copy down.

enter image description here

You can then hide column C.

It does require that it be sorted correctly and a helper column but it does stay to the formulas only rule.

Upvotes: 5

Related Questions