Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
I have two spreadsheets, I want to see if the same information is on both
spreadsheets, but I have to compare two columns at the same time so I cant use VLOOK up in the convetionational way unless it can look up two cells. eg I have one spread sheet which we shall call sheet1 with information in Columns A,B,C,and D and I have another spreadsheet called sheet2 within information in coulmns A,B,C, and D. I want to know if a row in sheet1 has the same infomation in coulmn A and B as a same row in column A and B in sheet2. If it has please show column C in sheet1 in column E in sheet 2, please note the infomation must be in the same row.I have had a reply, but I typed in the following it did not seem to work.=INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) When you click ctrl shift and enter the curly brackets start in front of the first = and close at the very end. This is the reply I got. =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Please could somebody check it and tell what I am doing wrong |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
.. I have had a reply, but I
typed in the following it did not seem to work. =INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0)) Some thoughts as to what's wrong with your implementation .. This part: INDEX(Delivery Notes!$F$1:$F$500 should be amended to: INDEX(Delivery Notes!$F$2:$F$500 to be consistent in range size to those in cols C and E (Or, perhaps it should be amended the other way round ..) And as per the steps given by the responder (Dave P, I presume <g), remember to enter the array formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Freddo" wrote: I have two spreadsheets, I want to see if the same information is on both spreadsheets, but I have to compare two columns at the same time so I cant use VLOOK up in the convetionational way unless it can look up two cells. eg I have one spread sheet which we shall call sheet1 with information in Columns A,B,C,and D and I have another spreadsheet called sheet2 within information in coulmns A,B,C, and D. I want to know if a row in sheet1 has the same infomation in coulmn A and B as a same row in column A and B in sheet2. If it has please show column C in sheet1 in column E in sheet 2, please note the infomation must be in the same row.I have had a reply, but I typed in the following it did not seem to work.=INDEX(Delivery Notes!$F$1:$F$500,MATCH(1,(F6=Delivery Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) When you click ctrl shift and enter the curly brackets start in front of the first = and close at the very end. This is the reply I got. =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Please could somebody check it and tell what I am doing wrong |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
(Or, perhaps it should be amended the other way round ..)
The above means perhaps change the 2 ranges within the MATCH part to: Delivery Notes!$C$1:$C$500 Delivery Notes!$E$1:$E$500 (to be consistent with .. INDEX(Delivery Notes!$F$1:$F$500,..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
Thanks for your help I have tried to make the range the same but it stills
come up with #NAME? "Max" wrote: (Or, perhaps it should be amended the other way round ..) The above means perhaps change the 2 ranges within the MATCH part to: Delivery Notes!$C$1:$C$500 Delivery Notes!$E$1:$E$500 (to be consistent with .. INDEX(Delivery Notes!$F$1:$F$500,..) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
"Freddo" wrote:
Thanks for your help I have tried to make the range the same but it stills come up with #NAME? Could you copy the *actual* formula (copy it from the formula bar) and paste in your response here ? Check also that the sheetname: Delivery Notes in the formula is correctly spelled and match exactly* what's on the tab (look out for any extra "invisible" white spaces in either which could throw the matching off) *except for case -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
=INDEX(Delivery Notes!$F$2:$F$500,MATCH(1,(F6=Delivery
Notes!$C$2:$C$500)*(O6=Delivery Notes!$E$2:$E$500),0))(one cell) This is the formula in my formula bar the curly brackets dont seem to have been pasted "Max" wrote: "Freddo" wrote: Thanks for your help I have tried to make the range the same but it stills come up with #NAME? Could you copy the *actual* formula (copy it from the formula bar) and paste in your response here ? Check also that the sheetname: Delivery Notes in the formula is correctly spelled and match exactly* what's on the tab (look out for any extra "invisible" white spaces in either which could throw the matching off) *except for case -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two spreadsheets | Excel Worksheet Functions | |||
Comparing two spreadsheets | Excel Worksheet Functions | |||
comparing 2 spreadsheets | Excel Discussion (Misc queries) | |||
Comparing Spreadsheets | Excel Worksheet Functions | |||
Please Help (Comparing Spreadsheets) | Excel Worksheet Functions |