Advancing lookups and comparisons

[ahern79]

its a bit more then I normally do. heres the set up.

I have a book with 3 pages. Each page is set the same way.
A1 - Property Number (Unique identifier)
A2 - Property Name (can be the same)
A3 through A15 - Dates of Renovation

The first page is the MAIN sheet.
Each Preceding page is from a different data set.

I'm trying to (on the Main Sheet) get the most recent date from each
preceding sheet of the Renovation date based on the Property Number.

some sort of Match and lookup formula, but I cant figure it out.

thank you in advance for your help.

 

[Bernie Deitrick]

Something along the lines of

=MAX(Sheet1!$A$3:$A$15)

You don't say what the sheets are named.

HTH,
Bernie
MS Excel MVP

 

[ahern79]

I have that as part of the formula, however, I need a way to have the formula
automatically look for the Property Number and match up all the same property
numbers and then look at the most recent date in all the sheets and input the
"MAX" number in the Row it finds the Property number in.

Also, if theres a way to verify that the main page has all the prorty
numbers in the preceding sheets that would be helpful as well. I know this
should be done in Access but the company wont allow out.

Thank you,
John

 

[Bernie Deitrick]

But, didn't you say:

Then I'm confused:

match up all the same property
numbers and then look at the most recent date in all the sheets

If the Property number is unique, why do you need to look across multiple sheets for it?

SO: If you have mulitple sheets with the same property number, then you really need to change the
structure of your workbook. MAKE it a database - you don't need to use Access to have a database.
Use one sheet with a column for all important items: Property number, date of renovation,
description of renovation. Every renovation should have ALL required information entered on the
same row. Then you can use data filters, pivot tables, etc. on the database, and find your
information without formulas, without duplication, without multiple sheets (which can be a bad idea,
despite the ability to use them).

HTH,
Bernie
MS Excel MVP

 

[ahern79]

Thank you Bernie for you help however, the way the bosses want this to happen
is:

I will eventually have MORE then three sheets. I am datamining for the most
recent information I can find on the properties. To complicate this further,
every week they send down an UPDATED spreadsheet with more current property
ID's. I have to look through and keep track of all the various datamining
sources and what dates I find pertaining to those Property IDs.

SO, I will have SEVERAL worksheets each for a different datamining source,
and I want ONE master sheet that compiles all the different sources (and
potential different property ID's depending on the time that I get the
updates; I'm not required to go back to the source once I finish the
list...just have to update the next source and go from there) and the most
recent dates from the different source sheets.

A small version for help sake would be:

the sheets all have the same headers: (but of course more then just the
three renovation dates; in total I have 33 dates to look for for each
propertyID)
A1 - Property ID
B1 - Construction year
C1 - Mechanical Renovation Date
D1 - Electrical Renovation Date
E1 - Plumbing Renovation Date

The "Import" sheet starting on Row "2" I need to be able to combine the data
from "Data1" and "Data2" in decending value by the Property ID, and with the
most recent dates from the Data# sheets.

Data1:
A B C D
E
Property ID Construction Date Mechanical Electrical Plumbing
2 14265 1956
3 14266 1980 1999
4 14267 1620 1990 1990
1990
5 14269 1890

Data2 is vertually identical but the dates are different:
A B C D
E
Property ID Construction Date Mechanical Electrical Plumbing
2 14265 1950
3 14266 1978 1999 1998
1997
4 14268 1990
5 14270 2000

I need to be able to combine these property ID's while keeping the most
recent years in the B:E columns.

Any idea how to do this?? is it a Macro button? is it a sheet filled with
formulas??

 

[Bernie Deitrick]

Well, you describe your layout entirely differently:

First, it was:
A1 - Property Number (Unique identifier)
A2 - Property Name (can be the same)
A3 through A15 - Dates of Renovation

Now it is:
A1 - Property ID
B1 - Construction year
C1 - Mechanical Renovation Date
D1 - Electrical Renovation Date
E1 - Plumbing Renovation Date

with multiple Property IDs on each sheet.

It's hard to come up with a coherent solution when the target is ill-defined and moving.

Still, I would copy the data (just data - no headers) from the new sheet and append it to the bottom
of my existing list. Then I would use data filters / pivot tables to look at specific properties,
dates, etc.

HTH,
Bernie
MS Excel MVP