Help & me match these rows
Mrbanner
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
carlo
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
Mrbanner
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
> =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 -
carlo
> > 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 -
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
Mrbanner
How would i log which ones in B are not listed in A?
Remembering that something in A400 could match to something in B300
> > > =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND(".",A2)-1---)),sheet2!
carlo
=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
> > > > =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND(".",A2)-1----)),sheet2!
Mrbanner
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 <carlo.ramu...@gmail.com> 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,
> > > > > =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND(".",A2)-1-----)),sheet2!
Mrbanner
all
On Nov 16, 9:44 am, Mrbanner <mrban...@swiftdsl.com.au> 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 <carlo.ramu...@gmail.com> 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<mrban...@swiftdsl.com.au> 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 <carlo.ramu...@gmail.com> wrote:
>
> > > > On Nov 15, 11:28 am,Mrbanner<mrban...@swiftdsl.com.au> 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 <carlo.ramu...@gmail.com> wrote:
>
> > > > > > =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND(".",A2)-1------)),sheet2!
Mrbanner
On Nov 16, 9:47 am, Mrbanner <mrban...@swiftdsl.com.au> wrote:
> =ISNUMBER(MATCH(A1,B:B,0)) seemed to do the job thanks for your help
> all
>
> On Nov 16, 9:44 am, Mrbanner <mrban...@swiftdsl.com.au> 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 <carlo.ramu...@gmail.com> 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,
> > > > > > > =if(iserror(vlookup(LEFT(A2,IF(ISERROR(FIND(".",A2)),LEN(A2),FIND(".",A2)-1-------)),sheet2!
carlo
(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