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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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#) ?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top