Text Columns

[fak119]

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]

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

"fak119" <[email protected]> a écrit dans le message de news:
[email protected]...
>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
>

[fak119]

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)
> > Kerry 21 Kerry 119
> > Ferguson 12 Ferguson 15
> > Peters 100 (will later be ignored)
> > Black 13 Black 13 (same value is possible and ok)
> >
> > etc.


"Ardus Petus" wrote:

> 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
>
> "fak119" <[email protected]> a écrit dans le message de news:
> [email protected]...
> >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
> >
>
>
>

[JLatham]

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))

"fak119" wrote:

> 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]

See example: http://cjoint.com/?fDoVdlIkC3

HTH
--
AP

"fak119" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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)
>> > Kerry 21 Kerry 119
>> > Ferguson 12 Ferguson 15
>> > Peters 100 (will later be ignored)
>> > Black 13 Black 13 (same value is possible and
>> > ok)
>> >
>> > etc.
>
>
> "Ardus Petus" wrote:
>
>> 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
>>
>> "fak119" <[email protected]> a écrit dans le message de
>> news:
>> [email protected]...
>> >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
>> >
>>
>>
>>

[JLatham]

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))

"fak119" wrote:

> 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)
> > > Kerry 21 Kerry 119
> > > Ferguson 12 Ferguson 15
> > > Peters 100 (will later be ignored)
> > > Black 13 Black 13 (same value is possible and ok)
> > >
> > > etc.
>
>
> "Ardus Petus" wrote:
>
> > 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
> >
> > "fak119" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> > >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
> > >
> >
> >
> >

[JLatham]

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


"fak119" wrote:

> 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)
> > > Kerry 21 Kerry 119
> > > Ferguson 12 Ferguson 15
> > > Peters 100 (will later be ignored)
> > > Black 13 Black 13 (same value is possible and ok)
> > >
> > > etc.
>
>
> "Ardus Petus" wrote:
>
> > 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
> >
> > "fak119" <[email protected]> a écrit dans le message de news:
> > [email protected]...
> > >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
> > >
> >
> >
> >

[fak119]

Brilliant!

Merci beaucoup !

"Ardus Petus" wrote:

> See example: http://cjoint.com/?fDoVdlIkC3
>
> HTH
> --
> AP
>
> "fak119" <[email protected]> a écrit dans le message de news:
> [email protected]...
> > 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)
> >> > Kerry 21 Kerry 119
> >> > Ferguson 12 Ferguson 15
> >> > Peters 100 (will later be ignored)
> >> > Black 13 Black 13 (same value is possible and
> >> > ok)
> >> >
> >> > etc.
> >
> >
> > "Ardus Petus" wrote:
> >
> >> 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
> >>
> >> "fak119" <[email protected]> a écrit dans le message de
> >> news:
> >> [email protected]...
> >> >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
> >> >
> >>
> >>
> >>
>
>
>

[Ragdyer]

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.
--
HTH,

RD

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

"fak119" <[email protected]> wrote in message
news:[email protected]...
> 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)
> > > Kerry 21 Kerry 119
> > > Ferguson 12 Ferguson 15
> > > Peters 100 (will later be ignored)
> > > Black 13 Black 13 (same value is possible
and ok)
> > >
> > > etc.
>
>
> "Ardus Petus" wrote:
>
> > 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
> >
> > "fak119" <[email protected]> a écrit dans le message de
news:
> > [email protected]...
> > >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
> > >
> >
> >
> >