Generate list from column A for all B cells containing number C

[jnelson522]

I have a sheet with a list of names (A), followed by a cell (B) which contains one or more years separated by commas.

I want to generate a list of all names from A when B contains the number in D:

A	B	C	D	E
John Doe	1998, 2005, 2014		2005	John Doe
Jane Doe	2012, 2014, 2017			George Spelvin
George Spelvin	2005, 2012, 2017
			2012	Jane Doe
				George Spelvin

<tbody>
</tbody>

I suspect this is possible using INDEX and MATCH, or INDEX and COUNT(FIND). Much obliged for any suggestions.

 

[palaeontology]

			1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017
John Doe	1998, 2005, 2014		John Doe							John Doe									John Doe
Jane Doe	2012, 2014, 2017																Jane Doe		Jane Doe			Jane Doe
George Spelvin	2005, 2012, 2017									George Spelvin							George Spelvin					George Spelvin

<colgroup><col><col><col><col span="20"></colgroup><tbody>
</tbody>

You wanted your results in columns D and E, but would you settle for having the years across row 1, and the corresponding names listed under the years ... as you see above.

If so, then, if your supplied example of names and years present was in the range A2:B4, then in D1 enter 1998, D2 enter ... =D1+1 ... and drag across to W1

Then, in D2, enter .. =IF(ISERROR(IF(FIND(D$1,$B2,1)>0,$A2,"")),"",IF(FIND(D$1,$B2,1)>0,$A2,"")) .. and drag across to W2.

Then drag that row (D2:W2) down to D4:W4

You should now see names appear under specific years, but there will be blank cells.

If you can live with that, great ... if not, you'll need to come up with a way to hide the empty cells ... normally achievable with F5, and 'special', but the cells have formulae in them, so they aren't really 'blank', so aren't easily recognised.

Anyway, a clunky solution to your request.

Kind regards,

Chris

 

[Peter_SSs]

Welcome to the MrExcel board!

Would this suit you? Formula in E1 is copied across and down.

Book1
	A	B	C	D	E	F	G
1	John Doe	1998, 2005, 2014		2005	John Doe	George Spelvin
2	Jane Doe	2012, 2014, 2017		2012	Jane Doe	George Spelvin
3	George Spelvin	2005, 2012, 2017
4
List

Cell Formulas
Range	Formula
E1	=IF($D1="","",IFERROR(INDEX($A$1:$A$3,AGGREGATE(15,6,(ROW($B$1:$B$3)-ROW(B$1)+1)/ISNUMBER(FIND($D1,$B$1:$B$3)),COLUMNS($E1:E1))),""))

 

[jnelson522]

Welcome to the MrExcel board!

Would this suit you? Formula in E1 is copied across and down.

	A	B	C	D	E	F	G
1	John Doe	1998, 2005, 2014		2005	John Doe	George Spelvin
2	Jane Doe	2012, 2014, 2017		2012	Jane Doe	George Spelvin
3	George Spelvin	2005, 2012, 2017
4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

List​

Worksheet Formulas Cell Formula E1 =IF($D1="","",IFERROR(INDEX($A$1:$A$3,AGGREGATE(15,6,(ROW($B$1:$B$3)-ROW(B$1)+1)/ISNUMBER(FIND($D1,$B$1:$B$3)),COLUMNS($E1:E1))),"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Peter, thank you. This is getting close to what I'm trying to produce.

Can you suggest how I might take your formula and produce a vertical list (rather than horizontal, as in your example) for a given year on a new sheet?

Much obliged!

 

[Peter_SSs]

Can you suggest how I might take your formula and produce a vertical list (rather than horizontal, as in your example) for a given year on a new sheet?

Sure..

Book1
	A	B
1	John Doe	1998, 2005, 2014
2	Jane Doe	2012, 2014, 2017
3	George Spelvin	2005, 2012, 2017
Data

Formula in A2 is copied across and down.

Book1
	A	B	C
1	2005	2012
2	John Doe	Jane Doe
3	George Spelvin	George Spelvin
4
List

Cell Formulas
Range	Formula
A2	=IF(A$1="","",IFERROR(INDEX(Data!$A$1:$A$3,AGGREGATE(15,6,(ROW(Data!$B$1:$B$3)-ROW(Data!B$1)+1)/ISNUMBER(FIND(A$1,Data!$B$1:$B$3)),ROWS(A$2:A2))),""))

 

[jnelson522]

Sure..

	A	B
1	John Doe	1998, 2005, 2014
2	Jane Doe	2012, 2014, 2017
3	George Spelvin	2005, 2012, 2017

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Formula in A2 is copied across and down.

	A	B	C
1	2005	2012
2	John Doe	Jane Doe
3	George Spelvin	George Spelvin
4

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

List​

Worksheet Formulas Cell Formula A2 =IF(A$1="","",IFERROR(INDEX(Data!$A$1:$A$3,AGGREGATE(15,6,(ROW(Data!$B$1:$B$3)-ROW(Data!B$1)+1)/ISNUMBER(FIND(A$1,Data!$B$1:$B$3)),ROWS(A$2:A2))),"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Peter, would you be willing to review the spreadsheet where I've attempted to use the formula? Not yet working for me. https://www.odrive.com/s/6af04bc7-1bda-468d-89be-8fa892afee0a-59f8e43a

Much obliged,
John

 

[jnelson522]

Chris, thanks for your reply. I'll keep looking for a way to produce a list without spaces, but do appreciate your useful example of IF-FIND.

Best regards,
John

 

[Peter_SSs]

Peter, would you be willing to review the spreadsheet where I've attempted to use the formula? Not yet working for me.

That file is in Excel 2003 format. Some of the functions that I used in my suggestion were not available in that version. If you really are using Excel 2003 then I may not be able to help as my memory of just what was available 14 years ago is not too good.
If you are actually using a newer Excel version, what version is is & could you save the file in a newer format?

 

[NARAYANK991]

Hi ,

See if the following works :

=INDEX(Data!$A$3:$A$20,SMALL(IF(ISNUMBER(FIND($A$1,INDEX(Data!$B$3:$M$20,,MATCH(A$2, Data!$B$2:$M$2,0)))),ROW(Data!$B$3:$B$20)-ROW(Data!B$3)+1),ROWS(A$3:A3)))

This is an array formula , to be entered using CTRL SHIFT ENTER.

It is Peter's formula , made compatible with Excel 2003.

This will display error values , which you can render invisible using Conditional Formatting.

I am not able to use the ISERROR function , because Excel gives an error about too many levels of nesting.

 

[jnelson522]

Thanks for your reply and suggested formula. It did work (after changing the second INDEX range from "$B$3:$M$20" to "$B$3:$B$20"). I had also received another reply that suggested the following array formula, which also worked:

=ArrayFormula(IFERROR(INDEX(Data!$A$1:$A$999,SMALL(IF(ISNUMBER(SEARCH($A$1,Data!B$1:B$999)),ROW(Data!$A$1:$A$999)-(ROW(Data!$A$1)-1)),ROWS($A$1:$A1))),""))