Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 12
There are 1 users currently browsing forums.
|
 |
12-12-2005, 01:10 PM
|
|
Registered User
|
|
Join Date: 12 Dec 2005
Posts: 7
|
|
|
compare column1 with column2
Please Register to Remove these Ads
Hi,
How do i compare column1 with column2 and output in column3.
ex.
column1 column2 output
test1 test4 test1
test2 test6 test2
test4 test1 test4
test6 test2 test6
Thanks for you help.
|
12-12-2005, 01:20 PM
|
|
|
|
Re: compare column1 with column2
You have not told use what criterion to use in the output column.
Do you want the largest of the two values?
either =MAX(A1:B1) or IF(A1>B1,A1,B1)
copy down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"myguess21" <myguess21.1zxvin_1134411004.1056@excelforum-nospam.com> wrote
in message news:myguess21.1zxvin_1134411004.1056@excelforum-nospam.com...
>
> Hi,
>
> How do i compare column1 with column2 and output in column3.
>
> ex.
>
> column1 column2 output
>
> test1 test4 test1
> test2 test6 test2
> test4 test1 test4
> test6 test2 test6
>
> Thanks for you help.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>
|
12-12-2005, 01:37 PM
|
|
Registered User
|
|
Join Date: 12 Dec 2005
Posts: 7
|
|
|
Thanks for quick response.
No this is string. Just want to search the value from column 2 in column1 if found out put in column 3.
|
12-12-2005, 02:10 PM
|
|
|
|
Re: compare column1 with column2
Some formulas to try:
=IF(A1=B1,A1,"")
=IF(FIND(B1,A1),A1,"")
=IF(SEARCH(B1,A1),A1,"")
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>
> Thanks for quick response.
>
> No this is string. Just want to search the value from column 2 in
> column1 if found out put in column 3.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>
|
12-12-2005, 02:10 PM
|
|
|
|
Re: compare column1 with column2
in C1
=if(match(B1,A:A,0),A1,"")
then drag fill down the column.
--
Regards,
Tom Ogilvy
"myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>
> Thanks for quick response.
>
> No this is string. Just want to search the value from column 2 in
> column1 if found out put in column 3.
>
>
> --
> myguess21
> ------------------------------------------------------------------------
> myguess21's Profile:
http://www.excelforum.com/member.php...o&userid=29578
> View this thread: http://www.excelforum.com/showthread...hreadid=492781
>
|
12-12-2005, 02:15 PM
|
|
|
|
Re: compare column1 with column2
Oh dear I mean to use MATCH not FIND!
Time to retire!!!
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:ejO2B70$FHA.1032@TK2MSFTNGP11.phx.gbl...
> Some formulas to try:
> =IF(A1=B1,A1,"")
> =IF(FIND(B1,A1),A1,"")
> =IF(SEARCH(B1,A1),A1,"")
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote
> in message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
>>
>> Thanks for quick response.
>>
>> No this is string. Just want to search the value from column 2 in
>> column1 if found out put in column 3.
>>
>>
>> --
>> myguess21
>> ------------------------------------------------------------------------
>> myguess21's Profile:
>> http://www.excelforum.com/member.php...o&userid=29578
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=492781
>>
>
>
|
12-12-2005, 02:35 PM
|
|
|
|
Re: compare column1 with column2
Is this is what you meant? (Search col A for each element in col B?)
=IF(MATCH(B1,A:A,0),B1,"")
red blue blue
green white white
blue yellow #N/A
black black black
white blue blue
but in both cases how do you get rid of "#N/A" when there is no match?
"Tom Ogilvy" wrote:
> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
>
|
12-12-2005, 02:45 PM
|
|
|
|
Re: compare column1 with column2
Should actually be:
=if(isnumber(match(B1,A:A,0)),A1,"")
--
Regards,
Tom Ogilvy
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23anPy60$FHA.2996@TK2MSFTNGP09.phx.gbl...
> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote
in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread:
http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
|
12-12-2005, 02:45 PM
|
|
|
|
Re: compare column1 with column2
Not if you look at the OP's example. It is as I wrote it and I posted a
correction to account for the N/A.
--
Regards,
Tom Ogilvy
"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:AEDA1FFF-7EB0-4BEF-B73A-610F60AB61DA@microsoft.com...
> Is this is what you meant? (Search col A for each element in col B?)
>
> =
> IF(MATCH(B1,A:A,0),B1,"")
> red blue blue
> green white white
> blue yellow #N/A
> black black black
> white blue blue
>
> but in both cases how do you get rid of "#N/A" when there is no match?
>
> "Tom Ogilvy" wrote:
>
> > in C1
> > =if(match(B1,A:A,0),A1,"")
> >
> > then drag fill down the column.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com>
wrote in
> > message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> > >
> > > Thanks for quick response.
> > >
> > > No this is string. Just want to search the value from column 2 in
> > > column1 if found out put in column 3.
> > >
> > >
> > > --
> > > myguess21
> >
> ------------------------------------------------------------------------
> > > myguess21's Profile:
> > http://www.excelforum.com/member.php...o&userid=29578
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=492781
> > >
> >
> >
> >
|
12-12-2005, 02:50 PM
|
|
|
|
Re: compare column1 with column2
The original example seems a little unusual. Maybe it's a VLookup problem:
=VLOOKUP(B1,A:B,2,FALSE)
test1 test4 test3
test2 test6 test5
test4 test3 test1
test6 test5 #N/A
test3 test1 test4
"Tom Ogilvy" wrote:
> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
>
|
12-12-2005, 02:53 PM
|
|
Registered User
|
|
Join Date: 12 Dec 2005
Posts: 7
|
|
Yes. this is what i need to do.
Thanks a lot.
Quote:
|
Originally Posted by Charlie
Is this is what you meant? (Search col A for each element in col B?)
=IF(MATCH(B1,A:A,0),B1,"")
red blue blue
green white white
blue yellow #N/A
black black black
white blue blue
but in both cases how do you get rid of "#N/A" when there is no match?
"Tom Ogilvy" wrote:
> in C1
> =if(match(B1,A:A,0),A1,"")
>
> then drag fill down the column.
>
> --
> Regards,
> Tom Ogilvy
>
> "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com> wrote in
> message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> >
> > Thanks for quick response.
> >
> > No this is string. Just want to search the value from column 2 in
> > column1 if found out put in column 3.
> >
> >
> > --
> > myguess21
> > ------------------------------------------------------------------------
> > myguess21's Profile:
> http://www.excelforum.com/member.php...o&userid=29578
> > View this thread: http://www.excelforum.com/showthread...hreadid=492781
> >
>
>
> |
|
12-12-2005, 02:55 PM
|
|
|
|
Re: compare column1 with column2
P.S. thanks for the #N/A correction
"Tom Ogilvy" wrote:
> Not if you look at the OP's example. It is as I wrote it and I posted a
> correction to account for the N/A.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> news:AEDA1FFF-7EB0-4BEF-B73A-610F60AB61DA@microsoft.com...
> > Is this is what you meant? (Search col A for each element in col B?)
> >
> > =
> > IF(MATCH(B1,A:A,0),B1,"")
> > red blue blue
> > green white white
> > blue yellow #N/A
> > black black black
> > white blue blue
> >
> > but in both cases how do you get rid of "#N/A" when there is no match?
> >
> > "Tom Ogilvy" wrote:
> >
> > > in C1
> > > =if(match(B1,A:A,0),A1,"")
> > >
> > > then drag fill down the column.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "myguess21" <myguess21.1zxwwm_1134412803.489@excelforum-nospam.com>
> wrote in
> > > message news:myguess21.1zxwwm_1134412803.489@excelforum-nospam.com...
> > > >
> > > > Thanks for quick response.
> > > >
> > > > No this is string. Just want to search the value from column 2 in
> > > > column1 if found out put in column 3.
> > > >
> > > >
> > > > --
> > > > myguess21
> > >
> > ------------------------------------------------------------------------
> > > > myguess21's Profile:
> > > http://www.excelforum.com/member.php...o&userid=29578
> > > > View this thread:
> http://www.excelforum.com/showthread...hreadid=492781
> > > >
> > >
> > >
> > >
>
>
>
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
