Retrieving data from another sheet

double0g

New Member
Joined
May 8, 2003
Messages
31
Hi,

This is my list of data lets say on sheet 1. On Sheet 2 I want to see all the names, then the corresponding oldest date from sheet 1. (Im looking for the oldest item by person)

On Sheet 2 the column headings should be:

Name, Created, Last (modified), Next (modified), Item #
Book1
ABCDE
1CreatedLastNextNumber
2fred1/1/20041/5/20041/15/20041
3Jim1/5/20041/5/20041/12/20042
4Bill1/6/20041/14/20041/30/20043
5tom1/1/20041/18/20041/31/20044
6paul2/1/20042/5/20042/7/20045
7fred1/15/20041/17/20041/21/20046
8Jim1/16/20041/18/20041/22/20047
9Bill1/12/20041/12/20041/13/20048
10tom1/13/20041/14/20041/15/20049
11paul1/3/20041/5/20041/6/200410
12tom1/4/20041/12/20041/17/200411
13tom1/16/20041/17/20041/19/200412
14paul1/8/20041/13/20041/18/200413
15fred1/9/20041/12/20041/21/200414
Sheet1
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Would you mind posting the expected results with respect to the data you already posted?
 

double0g

New Member
Joined
May 8, 2003
Messages
31
Hi,

here is what the results should look like:
Book1
ABCDE
21NameCreatedLastNextNumber
22Bill1/6/20041/14/20041/30/20043
23Fred1/1/20041/5/20041/15/20041
24Jim1/5/20041/5/20041/12/20042
25Paul1/3/20041/5/20041/6/200410
26Tom1/13/20041/14/20041/15/20049
Sheet1
 

double0g

New Member
Joined
May 8, 2003
Messages
31

ADVERTISEMENT

Typo...you are right for Tom it should be Created 1/1/04...
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
double0g said:
Typo...you are right for Tom it should be Created 1/1/04...

Is it correct to state that you want to retrieve the Last date value, directly associated with the Created value (same reasoning regarding the Next date value and Item#) ?
 

double0g

New Member
Joined
May 8, 2003
Messages
31

ADVERTISEMENT

Correct. The last date value is in relation to the created value.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
double0g said:
Correct. The last date value is in relation to the created value.

The formula-based approach that follows will be expensive, that is, slow if data ranges in source are huge...

Sheet1(source)
Book5
ABCDEF
116
2NameCreatedLastNextNumber0
3fred1/1/20041/5/20041/15/200411
4Jim1/5/20041/5/20041/12/200422
5Bill1/6/20041/14/20041/30/200433
6tom1/1/20041/18/20041/31/200444
7paul2/1/20042/5/20042/7/200455
8fred1/15/20041/17/20041/21/20046 
9Jim1/16/20041/18/20041/22/20047 
10Bill1/12/20041/12/20041/13/20048 
11tom1/13/20041/14/20041/15/20049 
12paul1/3/20041/5/20041/6/200410 
13tom1/4/20041/12/20041/17/200411 
14tom1/16/20041/17/20041/19/200412 
15paul1/8/20041/13/20041/18/200413 
16fred1/9/20041/12/20041/21/200414 
17
Sheet1


Formulas...

A1:

=MATCH(REPT("z",255),A:A)

F2 must house a 0.

F3:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),LOOKUP(9.99999999999999E+307,$F$2:F2)+1,"")

See the next post for Sheet2(destination).
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Aladin Akyurek said:
...
See the next post for Sheet2(destination).

Sheet2(destination)

Formulas...

A1:

=LOOKUP(9.99999999999999E+307,Sheet1!F:F)

A3:

=IF(ROW()-ROW($B$3)+1<=$A$1,INDEX(Sheet1!A:A,MATCH(ROW()-ROW($A$3)+1,Sheet1!F:F)),"")

Note that the unique name list is not alphabetized. Although a different method is available for sorting this list, that method would cause futher slow down.

B3:

=IF($A3<>"",MIN(IF(Sheet1!$A$3:INDEX(Sheet1!$A:$A,Sheet1!$A$1)=$A3,Sheet1!$B$3:INDEX(Sheet1!B:B,Sheet1!$A$1),"")),"")

This formula must be confirmed with control+shift+enter instead of just with enter.

C3:

=IF($A3<>"",MIN(IF((Sheet1!$A$3:INDEX(Sheet1!$A:$A,Sheet1!$A$1)=$A3)*(Sheet1!$B$3:INDEX(Sheet1!$B:$B,Sheet1!$A$1)=$B3),Sheet1!C$3:INDEX(Sheet1!C:C,Sheet1!$A$1),"")),"")

This formula also must be confirmed with control+shift+enter.

Copy C3 first across to E3.

Now select A3:E3 and copy down.
 

double0g

New Member
Joined
May 8, 2003
Messages
31
How can this formula be altered so the names do not change order based on the oldest. As in, if I wanted the name list alphabetized then populated with the corresponding data for oldest create date?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,453
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top