Compare against three columns and paste from a fourth

T

Toney

Hi all,

This looks to me to be a really complicated thing to do but maybe it's not.

I have two workbooks with essentially the same info EXCEPT for a couple items.
WB1
short file name, file size, date, time, file owner
WB2
full file name, file size, date, time

I tried sorting by date, time, size, name but unfortunately the lists are
not exactly the same.

So....
How complicated is it to have Excel compare the date, then the time, then
the file size between and if these match paste the full file name from WB2
into the WB1 in the row where the match occurs.

Sound easy? Here's some of the data...
WB1
WORLEST.WPD 8,060 5/3/1999 2:50p MTS
SMSPRO~1.CV5 126,220 5/4/1999 3:42p RJS
ASPHAL~1.CNV 134,134 5/7/1999 2:38p SMS
PLANMS.MCR 55,200 5/10/1999 10:34a MTS


WB2
2:50p 5/3/1999 8,060 WORLEST.WPD
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5
2:38p 5/7/1999 134,134 Asphalt trail.cnv
10:34a 5/10/1999 55,200 PLANMS.MCR

Result
2:50p 5/3/1999 8,060 WORLEST.WPD MTS
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5 RJS
2:38p 5/7/1999 134,134 Asphalt trail.cnv SMS
10:34a 5/10/1999 55,200 PLANMS.MCR MTS

Thanks in advance
Toney
 
B

Bernie Deitrick

Toney,

The general approach would be to use a formula in cell E2 of WB2, like

=index([WB1.xls]Sheet1!$E$1:$E$1000,sumproduct((A2=[WB1.xls]Sheet1!$A$1:$A$1000)*(B2=[WB1.xls]Sheet1!$B$1:$B$1000)*(C2=[WB1.xls]Sheet1!$C$1:$C$1000)*(D2=[WB1.xls]Sheet1!$D$1:$D$1000)*ROW($A$1:$A$1000)))

and then copy down...

HTH,
Bernie
MS Excel MVP
 
T

Toney

Bernie,

Your response did answer what I asked. Unfortunately I wasn't specific
enough. I don't have the exact same data in each file. The file listing I
used to create WB1 seems to have missing a couple directories and also seemed
not included some duplicate files.

So the function would have be able to compare the size, date and time and
only if there is a match grab the owner info.

Toney

Bernie Deitrick said:
Toney,

The general approach would be to use a formula in cell E2 of WB2, like

=index([WB1.xls]Sheet1!$E$1:$E$1000,sumproduct((A2=[WB1.xls]Sheet1!$A$1:$A$1000)*(B2=[WB1.xls]Sheet1!$B$1:$B$1000)*(C2=[WB1.xls]Sheet1!$C$1:$C$1000)*(D2=[WB1.xls]Sheet1!$D$1:$D$1000)*ROW($A$1:$A$1000)))

and then copy down...

HTH,
Bernie
MS Excel MVP

Toney said:
Hi all,

This looks to me to be a really complicated thing to do but maybe it's
not.

I have two workbooks with essentially the same info EXCEPT for a couple
items.
WB1
short file name, file size, date, time, file owner
WB2
full file name, file size, date, time

I tried sorting by date, time, size, name but unfortunately the lists are
not exactly the same.

So....
How complicated is it to have Excel compare the date, then the time, then
the file size between and if these match paste the full file name from WB2
into the WB1 in the row where the match occurs.

Sound easy? Here's some of the data...
WB1
WORLEST.WPD 8,060 5/3/1999 2:50p MTS
SMSPRO~1.CV5 126,220 5/4/1999 3:42p RJS
ASPHAL~1.CNV 134,134 5/7/1999 2:38p SMS
PLANMS.MCR 55,200 5/10/1999 10:34a MTS


WB2
2:50p 5/3/1999 8,060 WORLEST.WPD
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5
2:38p 5/7/1999 134,134 Asphalt trail.cnv
10:34a 5/10/1999 55,200 PLANMS.MCR

Result
2:50p 5/3/1999 8,060 WORLEST.WPD MTS
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5 RJS
2:38p 5/7/1999 134,134 Asphalt trail.cnv SMS
10:34a 5/10/1999 55,200 PLANMS.MCR MTS

Thanks in advance
Toney
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Toney,

Just use the comparisons for the data that you do have, so if column A is
not a match remove that part - (A2=[WB1.xls]Sheet1!$A$1:$A$1000)* and if
Column G is part of it, add in another section - just use * between each
used (Cell = Range)

HTH,
Bernie
MS Excel MVP


Toney said:
Bernie,

Your response did answer what I asked. Unfortunately I wasn't specific
enough. I don't have the exact same data in each file. The file listing I
used to create WB1 seems to have missing a couple directories and also
seemed
not included some duplicate files.

So the function would have be able to compare the size, date and time and
only if there is a match grab the owner info.

Toney

Bernie Deitrick said:
Toney,

The general approach would be to use a formula in cell E2 of WB2, like

=index([WB1.xls]Sheet1!$E$1:$E$1000,sumproduct((A2=[WB1.xls]Sheet1!$A$1:$A$1000)*(B2=[WB1.xls]Sheet1!$B$1:$B$1000)*(C2=[WB1.xls]Sheet1!$C$1:$C$1000)*(D2=[WB1.xls]Sheet1!$D$1:$D$1000)*ROW($A$1:$A$1000)))

and then copy down...

HTH,
Bernie
MS Excel MVP

Toney said:
Hi all,

This looks to me to be a really complicated thing to do but maybe it's
not.

I have two workbooks with essentially the same info EXCEPT for a couple
items.
WB1
short file name, file size, date, time, file owner
WB2
full file name, file size, date, time

I tried sorting by date, time, size, name but unfortunately the lists
are
not exactly the same.

So....
How complicated is it to have Excel compare the date, then the time,
then
the file size between and if these match paste the full file name from
WB2
into the WB1 in the row where the match occurs.

Sound easy? Here's some of the data...
WB1
WORLEST.WPD 8,060 5/3/1999 2:50p MTS
SMSPRO~1.CV5 126,220 5/4/1999 3:42p RJS
ASPHAL~1.CNV 134,134 5/7/1999 2:38p SMS
PLANMS.MCR 55,200 5/10/1999 10:34a MTS


WB2
2:50p 5/3/1999 8,060 WORLEST.WPD
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5
2:38p 5/7/1999 134,134 Asphalt trail.cnv
10:34a 5/10/1999 55,200 PLANMS.MCR

Result
2:50p 5/3/1999 8,060 WORLEST.WPD MTS
3:42p 5/4/1999 126,220 SMS Prov and Stew.cv5 RJS
2:38p 5/7/1999 134,134 Asphalt trail.cnv SMS
10:34a 5/10/1999 55,200 PLANMS.MCR MTS

Thanks in advance
Toney
Click to expand...
Click to expand...
Click to expand...
 
Top