Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
Problem i have
1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
On Nov 15, 9:48 am, Mrbanner wrote:
Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
Sorry for no reply i did send one but it didnt post
so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 am, Mrbanner wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1-)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
On Nov 15, 11:28 am, Mrbanner wrote:
Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 am, Mrbanner wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1--)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
i understand if i was to remove the extenstion
How would i log which ones in B are not listed in A? Remembering that something in A400 could match to something in B300 On Nov 15, 2:53 pm, carlo wrote: On Nov 15, 11:28 am, Mrbanner wrote: Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 am, Mrbanner wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1---)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
Ok, you enter following formula in to C2 and then copy it down:
=LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and then you enter follwing formula to D2 and copy it down: =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1-)),C:C, 1,false),"No Match", "Match") Then you can put an autofilter and select all "No Match" Tell me if that works or not. Carlo On Nov 15, 1:22 pm, Mrbanner wrote: i understand if i was to remove the extenstion How would i log which ones in B are not listed in A? Remembering that something in A400 could match to something in B300 On Nov 15, 2:53 pm, carlo wrote: On Nov 15, 11:28 am, Mrbanner wrote: Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 am, Mrbanner wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1----)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
Thanks for help dude
When i am typing in formula i am gettign a error =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1--)),C:C, 1,false),"No Match", "Match") Seem to be getting error may contain errors i have done just asj you put above (".",B2)-1-)) is where the error seems to lay On Nov 15, 5:45 pm, carlo wrote: Ok, you enter following formula in to C2 and then copy it down: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and then you enter follwing formula to D2 and copy it down: =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1--)),C:C, 1,false),"No Match", "Match") Then you can put an autofilter and select all "No Match" Tell me if that works or not. Carlo On Nov 15, 1:22 pm, Mrbanner wrote: i understand if i was to remove the extenstion How would i log which ones in B are not listed in A? Remembering that something in A400 could match to something in B300 On Nov 15, 2:53 pm, carlo wrote: On Nov 15, 11:28 am, Mrbanner wrote: Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 am, Mrbanner wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1-----)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
=ISNUMBER(MATCH(A1,B:B,0)) seemed to do the job thanks for your help
all On Nov 16, 9:44 am, Mrbanner wrote: Thanks for help dude When i am typing in formula i am gettign a error =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1---)),C:C, 1,false),"No Match", "Match") Seem to be getting error may contain errors i have done just asj you put above (".",B2)-1-)) is where the error seems to lay On Nov 15, 5:45 pm, carlo wrote: Ok, you enter following formula in to C2 and then copy it down: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and then you enter follwing formula to D2 and copy it down: =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1---)),C:C, 1,false),"No Match", "Match") Then you can put an autofilter and select all "No Match" Tell me if that works or not. Carlo On Nov 15, 1:22 wrote: i understand if i was to remove the extenstion How would i log which ones in B are not listed in A? Remembering that something in A400 could match to something in B300 On Nov 15, 2:53 pm, carlo wrote: On Nov 15, 11:28 wrote: Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1------)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
No i lie didnt seem to match them corrctly
On Nov 16, 9:47 am, Mrbanner wrote: =ISNUMBER(MATCH(A1,B:B,0)) seemed to do the job thanks for your help all On Nov 16, 9:44 am, Mrbanner wrote: Thanks for help dude When i am typing in formula i am gettign a error =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1----)),C:C, 1,false),"No Match", "Match") Seem to be getting error may contain errors i have done just asj you put above (".",B2)-1-)) is where the error seems to lay On Nov 15, 5:45 pm, carlo wrote: Ok, you enter following formula in to C2 and then copy it down: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and then you enter follwing formula to D2 and copy it down: =if(iserror(vlookup(LEFT(B2,IF(ISERROR(FIND(".",B2 )),LEN(B2),FIND(".",B2)-1----)),C:C, 1,false),"No Match", "Match") Then you can put an autofilter and select all "No Match" Tell me if that works or not. Carlo On Nov 15, 1:22 wrote: i understand if i was to remove the extenstion How would i log which ones in B are not listed in A? Remembering that something in A400 could match to something in B300 On Nov 15, 2:53 pm, carlo wrote: On Nov 15, 11:28 wrote: Sorry for no reply i did send one but it didnt post so i created new topic sorry Her eit is again Easier this time around OK Same data put it into 1 sheet a b 1 hi.htm hi.htm 2 hello.htm hellow.htm 3 blue.htm bbloe.htm 4 bbloe blue.htm 5 happy.htm jeusus.htm 6 happy1.htm Ok A has 1905 lines b has 2010 lines what i need to do is if Anywhere in b1:1905 matches anywhere in a1:2010 In C1:2010 it shows True or false. that way i can see what records are missign from the colum B and then fix them up quickly On Nov 15, 12:05 pm, carlo wrote: On Nov 15, 9:48 wrote: Problem i have 1 Execel file 2 sheets SHeet 1 has 1900 lines of data Url File Names going down in Row A hello.htm hell2.htm 444.htm Sheet 2 has 2100 lines of data Row A Same but this time only part of the data and sometimes full hello.h hell2.htm 444.ht Which means people missed putting files into the excel file What i need to do is do some type of if or match function so that if A1:A1905 = Matches or part matches Sheet 2 A1:A2100 then it displaces the word match or no match in the sheet 2 file some where Any help would be great is the filename unique? or is it possible, that you have hello.htm hello.html hello.php hello.php4 If it is unique, a Vlookup should be able to do what you need. If it is not unique, then it will be a little bit more difficult, to be honest i don't have any idea how i would solve the problem. But if you want to try the vlookup: Make a new column in your sheet2 with the formula: =LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND("." ,A2)-1)) and copy it down then in a new column in sheet1: =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2 )),LEN(A2),FIND(".",A2)-1-------)),sheet2! B:B,1,false),"No Match", "Match") I started with A2 in case you use Titles change shee2!B:B to your new column in sheet2 hth Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I still need to know, if you have multiple filenames (excl. the extension). What I would do is look for the filename without extension and compare them (as i described in my first post), but that only works if you have unique filenames. Carlo- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help & me match these rows
Sorry, i just saw, that I had some problems with the braces:
(because of the word wrap i split up the formula, make sure to put it back together in excel) =IF(ISERROR(VLOOKUP(LEFT(B2,IF( ISERROR(FIND(".",B2)),LEN(B2),FIND(".",B2)-1)), C:C,1,FALSE)),"No Match", "Match") hth Carlo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
index match 2 rows 1 col | Excel Worksheet Functions | |||
Match & combine rows from 2 worksheets | Excel Worksheet Functions | |||
MATCH against 65534 rows | Excel Discussion (Misc queries) | |||
match week, count rows, sum rows? | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions |