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

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

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

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

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

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