questionss
New Member
- Joined
- Jul 15, 2014
- Messages
- 2
Hello.
I have two tables with similar information. File A is the raw, and File B with selected items. Is it possible to return all code under title “DEF” from File A in the way ”A112, A144, A134, A136, A452, A584, A714, A848, A980, A988” to file B cell B3 with title name(A3) and date range (G3:H3)?
I tried this:
=INDEX('[FILE A.xls]Sheet 1'!$B$1:$B$48002,MATCH($H3,IF('[FILE A.xls]Sheet 1'!$A$1:$A$48002=$A3,'[FILE A.xls]Sheet 1'!$D$1:$D$48002),0))
But it can only return the first code that match the starting date. I am using excel 2010, please give me some advice.
Thanks so much.
File A
<tbody>
</tbody>
File B
<tbody>
</tbody>
I have two tables with similar information. File A is the raw, and File B with selected items. Is it possible to return all code under title “DEF” from File A in the way ”A112, A144, A134, A136, A452, A584, A714, A848, A980, A988” to file B cell B3 with title name(A3) and date range (G3:H3)?
I tried this:
=INDEX('[FILE A.xls]Sheet 1'!$B$1:$B$48002,MATCH($H3,IF('[FILE A.xls]Sheet 1'!$A$1:$A$48002=$A3,'[FILE A.xls]Sheet 1'!$D$1:$D$48002),0))
But it can only return the first code that match the starting date. I am using excel 2010, please give me some advice.
Thanks so much.
File A
TITLE | CODE | DURATION | DATE |
ABC | C123 | 0:39:00 | 1/4/2010 |
DEF | A112 | 1:15:00 | 10/1/1999 |
DEF | A144 | 1:15:00 | 10/1/2000 |
DEF | A134 | 1:15:00 | 10/1/2001 |
DEF | A136 | 1:15:00 | 10/1/2002 |
DEF | A452 | 1:15:00 | 10/1/2003 |
DEF | A584 | 1:15:00 | 10/1/2004 |
DEF | A714 | 1:15:00 | 10/1/2005 |
DEF | A848 | 1:15:00 | 10/1/2006 |
DEF | A980 | 1:15:00 | 10/1/2007 |
DEF | A988 | 1:15:00 | 10/1/2008 |
GHI | B124 | 1:00:00 | 3/3/1980 |
GHI | B346 | 0:58:30 | 3/4/1980 |
GHI | B547 | 1:02:00 | 3/5/1980 |
<tbody>
</tbody>
File B
Title | Code | Type | Format | Quantity | Duration | Staring Date | End Date |
ABC | A | A | 1 | 0:39:00 | 1/4/2010 | 1/4/2010 | |
DEF | B | A | 10 | 12:30:00 | 10/1/1999 | 9/10/2008 | |
GHI | C | A | 3 | 3:00:30 | 3/3/1980 | 3/5/1980 |
<tbody>
</tbody>