Excel 2016 How to conditional concatenate

[Jfawcett]

Hello,

I'm working in Excel 2016, unfortunately this can't be updated due to work.

In column A I'm trying to get the cells to read off the question numbers of the first 6 incorrect questions and column B the corresponding MW clips that go with each of those first 6 incorrect questions.

Any help would be greatly appreciated.

Thanks,

Jack

 

[Jfawcett]

Picture of file attached

 

[Fluff]

Cross posted Excel 2016 Conditional Concatenate

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[Fluff]

Are you happy using a UDF rather than a function?

 

[Jfawcett]

Hi Thanks for the reply

A UDF would be fine.

 

[Fluff]

Ok, how about

Function Questions(Rng As Range) As String
   Dim Cl As Range
   Dim i As Long
   For Each Cl In Rng
      If Cl.Value < Cells(4, Cl.Column) Then
         Questions = Questions & ", " & Cells(1, Cl.Column)
         i = i + 1
         If i = 6 Then Exit For
      End If
   Next Cl
   Questions = Mid(Questions, 3)
End Function
Function MwClips(Rng As Range) As String
   Dim Cl As Range
   Dim i As Long
   For Each Cl In Rng
      If Cl.Value < Cells(4, Cl.Column) Then
         MwClips = MwClips & ", " & Cells(2, Cl.Column)
         i = i + 1
         If i = 6 Then Exit For
      End If
   Next Cl
   MwClips = Mid(MwClips, 3)
End Function

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		Question numbers	1	2	3	4	5a	5b	6	7	8a	8b	8c	9a	9b	10a	10b	11a	11b	11c	12	13
2		MW Clips	13	135a	68a	72	17/18	66	8	22a	64	64	64	28	28	22a	22a	10	10	10	87	72
3	First 6 Incorrect Questions	Suggested MW Clips	Types of angles	Solve simple	Negative numbers	Calculation to find half	Decimals add and subtract	decimals multiply	Coordinates	Money problem	Mode from vertical line chart	Probability from a graph	Probability from a graph	Factors	LCM	Division problem	Money problem	Properties of 2D shapes			Finding a % without a calculator	Fraction of amount
4			1	1	1	1	2	1	2	2	1	1	2	2	2	3	3	1	1	1	3	1
5	4, 8a, 8b, 8c, 10a, 11c	72, 64, 64, 64, 22a, 10	1	1	1	0	2	1	2	2	0	0	0	2	2	1	3	1	1	0	3	1
6	3, 6, 8a, 8b, 8c, 10b	68a, 8, 64, 64, 64, 22a	1	1	0	1	2	1	0	2	0	0	0	2	2	3	1	1	1	0	3	1
Sheet1 (2)

Cell Formulas
Range	Formula
A5:A6	A5	=questions(C5:AU5)
B5:B6	B5	=MwClips(C5:AU5)