Help Pls

[SamGB]

Really have no ideas where to start so ill just explain what i am trying to do.
I have 1 worksheet listing Peoples names, DOB, age etc. in rows from 1 - 50.
with cell A always being their name.

I then have a second work sheet listing the SAME peoples names followed by thier Home and Mobile numbers in rows from 1 - 50. HOWEVER when 1 person has a Home AND Mobile number thier name is duplicated, for example:
Cell A Cell B Cell C
P.Jones HOME 01322 1234124
L.Smith HOME 01454 1231234
L.Smith MOBILE 07841 777777

Now what i need is to merge the two sets of data. Was thinking a macro that looks for the names listed in the first workbook and then finds the same names in the second workbook, and depending on what referance Cell B has in it, it puts the data in cell C into the corosponding cell in the first workbook. im probally making it more complicated than what it is but any ideas or suggestions would be a great help. Also if the macro idea works, what would the code be??

Many thanks
Sam

[Miguel Zapico]

You can this with MATCH, INDEX and array formulas. In your example, let's
suppose that the phone numbers are in Sheet2, and you want them in columns F
and G of sheet one. On F1 the header is HOME, on G1 the header is MOBILE.
The names are in Column A of Sheet1, and thay are the exactly the same as
column A of Sheet2.
With all this, you can enter this array formula in F2 of Sheet1:
=INDEX(Sheet2!$C$1:$C$50,MATCH(1,--(Sheet2!$A$1:$A$50=$A2)*--(Sheet2!$B$1:$B$50=F$1),0))
Enter it with CTRL+SHIFT+ENTER. Later, you can copy and paste the formula
across the 50 rows on the F and G columns. It will raise and error in the
ones that haven't got a match, you can either delete that ones, hide with
conditional formating, or modify the formula to capture the error before
giving the result.

Hope this helps,
Miguel.

"SamGB" wrote:

>
> Really have no ideas where to start so ill just explain what i am trying
> to do.
> I have 1 worksheet listing Peoples names, DOB, age etc. in rows from 1
> - 50.
> with cell A always being their name.
>
> I then have a second work sheet listing the SAME peoples names followed
> by thier Home and Mobile numbers in rows from 1 - 50. HOWEVER when 1
> person has a Home AND Mobile number thier name is duplicated, for
> example:
> Cell A Cell B Cell C
> P.Jones HOME 01322 1234124
> L.Smith HOME 01454 1231234
> L.Smith MOBILE 07841 777777
>
> Now what i need is to merge the two sets of data. Was thinking a macro
> that looks for the names listed in the first workbook and then finds
> the same names in the second workbook, and depending on what referance
> Cell B has in it, it puts the data in cell C into the corosponding cell
> in the first workbook. im probally making it more complicated than what
> it is but any ideas or suggestions would be a great help. Also if the
> macro idea works, what would the code be??
>
> Many thanks
> Sam
>
>
> --
> SamGB
> ------------------------------------------------------------------------
> SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
> View this thread: http://www.excelforum.com/showthread...hreadid=545609
>
>

[SamGB]

Thanks
that looks like it will work although im having trouble modifying it to work with my sheet. can you just tell me a breakdown of what parts of the formula do what. it would be a great help

[SamGB]

I think i have modified it to suit although i get a #NUM return.
Below is how i have modified it:

=INDEX(CrewPhone!C:C,MATCH(1,--(CrewPhone!A:A=$C3)*--(CrewPhone!B:B=H$2),0))

Where CrewPhone! is the sheet containing the name and numbers
Column C:C contains the numbers
Column A:A contains the names
Column B:B contains the HOME or MOBILE referance
Cell C3 contains the persons name
Cell H2 contains the HOME header

can you see any probs??

[Miguel Zapico]

Sure, let's start from the inside:
--(Sheet2!$A$1:$A$50=$A2)*--(Sheet2!$B$1:$B$50=F$1)
This is the array part. This compares the values of the column A on sheet 2
with the A2 cell on the current sheet (the name), and the column B on sheet 2
with the cell F1 of the current sheet (the type of phone). It returns an
array of mostly 0s, with a 1 in the place where both conditions are fulfilled
at the same time.
The following part:
MATCH(1,--(Sheet2!$A$1:$A$50=$A2)*--(Sheet2!$B$1:$B$50=F$1),0))
This looks for that 1 in the array, and returns its position in the array.
That value will be used in the INDEX formula to retrieve the value from the
column C of sheet 2:
=INDEX(Sheet2!$C$1:$C$50,MATCH(1,--(Sheet2!$A$1:$A$50=$A2)*--(Sheet2!$B$1:$B$50=F$1),0))
I hope that with this you know what to modify to adjust to your cell. It is
also important to check that the formula has been entered as array formula,
it should show curly brackets on the formula bar (don't try to enter them,
just check that they are there)

Hope this helps,
Miguel.

"SamGB" wrote:

>
> Thanks
> that looks like it will work although im having trouble modifying it to
> work with my sheet. can you just tell me a breakdown of what parts of
> the formula do what. it would be a great help
>
>
> --
> SamGB
> ------------------------------------------------------------------------
> SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
> View this thread: http://www.excelforum.com/showthread...hreadid=545609
>
>

[Miguel Zapico]

The problem may be in using full columns as the ranges to match. Try using a
fixed range, even if it is big, or a dynamic named range (see
http://www.ozgrid.com/Excel/DynamicRanges.htm) instead.

Miguel.

"SamGB" wrote:

>
> I think i have modified it to suit although i get a #NUM return.
> Below is how i have modified it:
>
> =INDEX(CrewPhone!C:C,MATCH(1,--(CrewPhone!A:A=$C3)*--(CrewPhone!B:B=H$2),0))
>
> Where CrewPhone! is the sheet containing the name and numbers
> Column C:C contains the numbers
> Column A:A contains the names
> Column B:B contains the HOME or MOBILE referance
> Cell C3 contains the persons name
> Cell H2 contains the HOME header
>
> can you see any probs??
>
>
> --
> SamGB
> ------------------------------------------------------------------------
> SamGB's Profile: http://www.excelforum.com/member.php...o&userid=31263
> View this thread: http://www.excelforum.com/showthread...hreadid=545609
>
>