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 --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
"Freddo" wrote:
=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) Try this corrected formula (lightly tested here ok), array-entered as befo =INDEX('Delivery Notes'!F2:$F$500,MATCH(1,(F6='Delivery Notes'!$C$2:$C$500)*(O6='Delivery Notes'!$E$2:$E$500),0)) Think the culprit was the 2 missing apostrophes: ' just before and after the sheetname ref in the formula. It's good practice to include these apostrophes, irregardless whether there are spaces in the sheetname or not. This is the formula in my formula bar the curly brackets dont seem to have been pasted Yes, that's ok. The curly braces are inserted by Excel (not to be typed in) and won't be copied. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
Thanks that seems to work it was the words one cell I needed to get rid of
"Max" wrote: "Freddo" wrote: =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) Try this corrected formula (lightly tested here ok), array-entered as befo =INDEX('Delivery Notes'!F2:$F$500,MATCH(1,(F6='Delivery Notes'!$C$2:$C$500)*(O6='Delivery Notes'!$E$2:$E$500),0)) Think the culprit was the 2 missing apostrophes: ' just before and after the sheetname ref in the formula. It's good practice to include these apostrophes, irregardless whether there are spaces in the sheetname or not. This is the formula in my formula bar the curly brackets dont seem to have been pasted Yes, that's ok. The curly braces are inserted by Excel (not to be typed in) and won't be copied. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
"Freddo" wrote:
Thanks that seems to work it was the words one cell I needed to get rid of Glad to hear that worked out ok <bg ! Dave meant it: the "(one cell)" bit, as an explanation that the entire formula should be placed all in one cell -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
Thanks for the correction/amplification.
And one trick to getting those pesky apostrophes to work correctly is to change the sheet name to something that doesn't require them -- maybe just A (single character A). Then get the formula working and then rename the sheet. If excel wants the apostrophes, it'll add them. Max wrote: "Freddo" wrote: Thanks that seems to work it was the words one cell I needed to get rid of Glad to hear that worked out ok <bg ! Dave meant it: the "(one cell)" bit, as an explanation that the entire formula should be placed all in one cell -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two spreadsheets part 2
Thanks for the tip, Dave !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote: Thanks for the correction/amplification. And one trick to getting those pesky apostrophes to work correctly is to change the sheet name to something that doesn't require them -- maybe just A (single character A). Then get the formula working and then rename the sheet. If excel wants the apostrophes, it'll add them. |
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 |