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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1998, 2005, 2014[/TD]
[TD][/TD]
[TD]2005[/TD]
[TD]John Doe[/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]2012, 2014, 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]George Spelvin[/TD]
[/TR]
[TR]
[TD]George Spelvin[/TD]
[TD]2005, 2012, 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2012[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]George Spelvin[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[palaeontology]

[TABLE="width: 1857"]
<colgroup><col><col><col><col span="20"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1998[/TD]
[TD]1999[/TD]
[TD]2000[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]1998, 2005, 2014[/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jane Doe[/TD]
[TD]2012, 2014, 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD]Jane Doe[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]George Spelvin[/TD]
[TD]2005, 2012, 2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]George Spelvin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]George Spelvin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]George Spelvin[/TD]
[/TR]
</tbody>[/TABLE]

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
John Doe	1998, 2005, 2014	John Doe	George Spelvin
Jane Doe	2012, 2014, 2017	Jane Doe	George Spelvin
George Spelvin	2005, 2012, 2017

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2005[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=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))),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
John Doe	1998, 2005, 2014
Jane Doe	2012, 2014, 2017
George Spelvin	2005, 2012, 2017

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Data​

Formula in A2 is copied across and down.

	A	B	C
John Doe	Jane Doe
George Spelvin	George Spelvin

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2005[/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=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))),""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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))),""))