Creating a list in Excel for a matching criteria

[zuber85]

Hi,

I hope someone can help me with this. Here is an example of m
spreadsheet:

A B C
D
1 01/07/2012 02/07/2012
03/07/2012
2 NAME
3 John H H
4 Gary H
5 Paul H
H


Now what I would want to do is to be able to create a list of names fo
a given date where there is a H in the date cell, for example if my dat
was 02/07/2012 then the list will give me John and Gary and if the dat
was 03/07/2012 then it would only give me Paul.

I hope this makes sense.

Thanks in advance for any help.

 

[James Ravenswood]

One way, either manually or via VBA, is to set AutoFilter for all the date columns. Pick a date and filter to hide blanks. Then copy the visible names in the first column and paste elsewhere.

 

[v]

Here is solution:

=IF(INDIRECT("R" & ROW() & "C" & MATCH($F$1,$A$1:$E$1,0),0)="H",A3,"")


Paste this formula in Cells F3 with following conditions:

- F1 should contain date from your data
- F2 empty row
- Column E is blank
- Columns A should contain ur data


Hereunder is sample

A B C D E F
01-07-2012 02-07-2012 03-07-2012 03-07-2012

John H H
Gary H
Paul H H Paul

For any detail ping me or visit (e-mail address removed)

 

[vikrmnv]

Here is solution:

=IF(INDIRECT("R" & ROW() & "C" & MATCH($F$1,$A$1:$E$1,0),0)="H",A3,"")


Paste this formula in Cells F3 with following conditions:

- F1 should contain date from your data
- F2 empty row
- Column E is blank
- Columns A should contain ur data


Hereunder is sample

A B C D E F
01-07-2012 02-07-2012 03-07-2012 03-07-2012

John H H
Gary H
Paul H H Paul

For any detail ping me or visit www.Excelolozy.com

 

[zuber85]

One way, either manually or via VBA, is to set AutoFilter for all th
date columns. Pick a date and filter to hide blanks. Then copy th
visible names in the first column and paste elsewhere.

Thanks, this might be a bit long winded for people