Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / May 2006

Tip: Looking for answers? Try searching our database.

Text Columns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fak119 - 29 May 2006 11:59 GMT
I have to columns, alphabetically sorted, one with 1oo names, one with 8o-9o
names, which are identical to the ones in the column with 1oo.

Is there a way (formula or macro) that places the identical text (names)
next to each other in the same row, leaving blank the cell where there is no
match?

The columns should then look like this:

Miller                Miller
Smith
Kerry                 Kerry
Ferguson           Ferguson
Peters
Black                 Black

etc.

Thank you
Ardus Petus - 29 May 2006 12:36 GMT
Say 1st column is column A, 2nd column is column B.
Enter in C1:
=IF(ISNA(VLOOKUP(A1,B:B,1,0)),"",VLOOKUP(A1,B:B,1,0))
and drag down

HTH
--
AP

>I have to columns, alphabetically sorted, one with 1oo names, one with
>8o-9o
[quoted text clipped - 17 lines]
>
> Thank you
fak119 - 29 May 2006 13:32 GMT
That was great, thank you! However the problem is not yet completely solved...

Next to each text (name) are values, but they are different! This value
should go next to the name, and the final four columns would look like this:

> > Miller        25        Miller         40
> > Smith       35                                 (will later be ignored)
[quoted text clipped - 4 lines]
> >
> > etc.

> Say 1st column is column A, 2nd column is column B.
> Enter in C1:
[quoted text clipped - 26 lines]
> >
> > Thank you
Ardus Petus - 29 May 2006 13:47 GMT
See example: http://cjoint.com/?fDoVdlIkC3

HTH
--
AP

> That was great, thank you! However the problem is not yet completely
> solved...
[quoted text clipped - 48 lines]
>> >
>> > Thank you
fak119 - 29 May 2006 14:15 GMT
Brilliant!

Merci beaucoup !

> See example: http://cjoint.com/?fDoVdlIkC3
>
[quoted text clipped - 54 lines]
> >> >
> >> > Thank you
JLatham - 29 May 2006 13:58 GMT
Ok, revised for the new information presented.

Assumptions - your data is laid out in A, B, C and D as you indicated and
starts in row 1 and continues to row 100.
To get the matching names, use this in E1 and drag down:
=IF(ISNA(MATCH(A1,C$1:C$100,0)),"",INDEX(C$1:C$100,MATCH(A1,C$1:C$100,0),0))
then in at F1 to get the value associated with the matched names, use this
and drag down:
=IF(ISNA(MATCH(A1,C$1:C$100,0)),"",INDEX(D$1:D$100,MATCH(A1,C$1:C$100,0),0))

> That was great, thank you! However the problem is not yet completely solved...
>
[quoted text clipped - 40 lines]
> > >
> > > Thank you
JLatham - 29 May 2006 14:02 GMT
We should stress that the names in BOTH lists should be in alphabetical order
as you said they were in your original question, otherwise your results may
not be as desired.  Here's list of things as I set them up to test the
equations:
 A            B              C              D              E              F
(E & F from formulas)
Adria    15    Adria    7    Adria    7
Harvey    33    Harvey    12    Harvey    12
Jenna    7    Jerry    18       
Jerry    21    Lee    21    Jerry    18
Lee    404    Mary    33    Lee    21
Mary    18    Ralph    83    Mary    33
Morris    83    Scoda    404       
Ralph    501    Tom    501    Ralph    83
Scoda    12            Scoda    404
Tom    66            Tom    501

> That was great, thank you! However the problem is not yet completely solved...
>
[quoted text clipped - 40 lines]
> > >
> > > Thank you
Ragdyer - 29 May 2006 14:15 GMT
Say your first list is in A1 to B100,
And your second list is in D1 to E100.

Enter this formula in F1:

=IF(ISNA(MATCH($A1,$D$1:$D$100,0)),"",INDEX($D$1:$E$100,MATCH($A1,$D$1:$D$10
0,0),COLUMNS($A:A)))

Copy across to G1,
Then select *both* F1 and G1, and drag down to copy.

Really no need for anything to be sorted.
Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> That was great, thank you! However the problem is not yet completely solved...
>
[quoted text clipped - 40 lines]
> > >
> > > Thank you
JLatham - 29 May 2006 13:41 GMT
Another solution that does not depend on the two columns being next to one
another or even on the same worksheet, but the example shown does presume
that first data series is in column A (rows 1 to 100) and second series is in
B on the same sheet beginning in row 1 also.  Put this in cell C1 and drag
down:

=IF(ISNA(MATCH(A1,B$1:B$100,0)),"",INDEX(B$1:B$100,MATCH(A1,B$1:B$100,0),0))

> I have to columns, alphabetically sorted, one with 1oo names, one with 8o-9o
> names, which are identical to the ones in the column with 1oo.
[quoted text clipped - 15 lines]
>
> Thank you
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.