| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare against three columns and paste from a fourth
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare against three columns and paste from a fourth
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare against three columns and paste from a fourth
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" wrote: 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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Compare against three columns and paste from a fourth
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Compare and copy/paste | Excel Worksheet Functions | |||
| Add a Row Every Fourth Row | Excel Worksheet Functions | |||
| Compare col and match then copy and paste | Excel Discussion (Misc queries) | |||
| MARK EVERY FOURTH ROW | Excel Worksheet Functions | |||
| Taking data from 3 columns to make the fourth. | Excel Worksheet Functions | |||
Linear Mode
