I have two spreadsheets. Sheet 1 has names, birth date, application date, account number, etc. Sheet 2, which is pulled from a different source, has names, birth date, attendance date, account balance, testing date, etc. Sheet 1 may have names that are not on Sheet 2 and vice versa. I want a Sheet 3 which has all of the columns. It should also combine clients...for instance, John Smith is on sheet 1 with his application date and account number and he is also on sheet 2 which shows his attendance date and account balance.. Instead of getting two John Smiths on sheet 3, I would like it to combine the information into one row which shows his application date, account number, attendance date, and account balance.

Doing it manually is not an option since there are so many, but I'm not sure how else to do it. Do you have any suggestions? I really appreciate any help you can provide. Thank you so much!!

hi,

if there is no duplicate entries (name and account number) on sheet1

looking for names and account number (i suppose it is columns A and D)

put this formula on sheet2,

=INDEX(Sheet1!C:C,MATCH(Sheet1!A1&Sheet1!D1,Sheet1 !A:A&Sheet1!D:D,0))

array formula, validate with ctrl + shift + enter

then change this part C:C to get info for different columns

note:
when you change a part of formula, dont forget to validate with ctrl +
shift + enter

when the data are collected and verified,
i suggest that you make a copy / paste special / values,
array formulas are using lots of memory

isabelle

Le 2013-08-13 13:16, a écrit :
I have two spreadsheets. Sheet 1 has names, birth date, application date, account number, etc. Sheet 2,

which is pulled from a different source, has names, birth date,
attendance date, account balance, testing date, etc.

Sheet 1 may have names that are not on Sheet 2 and vice versa. I want a
Sheet 3 which has all of the columns.

It should also combine clients...for instance, John Smith is on sheet
1 with his application date and account number

and he is also on sheet 2 which shows his attendance date and account
balance. Instead of getting two John Smiths on sheet 3,

I would like it to combine the information into one row which shows
his application date, account number, attendance date, and account balance.

Doing it manually is not an option since there are so many, but I'm not sure how else to do it. Do you have any suggestions?

I really appreciate any help you can provide. Thank you so much!!

if there is no duplicate entries account number on sheet1 and sheet2

you can use this formula (it is not an array formula)

=INDEX(Sheet1!C:C,MATCH(Sheet1!D1,Sheet1!D:D,0))

isabelle


Le 2013-08-14 22:11, isabelle a écrit :
hi,

if there is no duplicate entries (name and account number) on sheet1

looking for names and account number (i suppose it is columns A and D)

put this formula on sheet2,

=INDEX(Sheet1!C:C,MATCH(Sheet1!A1&Sheet1!D1,Sheet1 !A:A&Sheet1!D:D,0))

array formula, validate with ctrl + shift + enter

then change this part C:C to get info for different columns

note:
when you change a part of formula, dont forget to validate with ctrl +
shift + enter

when the data are collected and verified,
i suggest that you make a copy / paste special / values,
array formulas are using lots of memory

isabelle

Le 2013-08-13 13:16, a écrit :
I have two spreadsheets. Sheet 1 has names, birth date, application
date, account number, etc. Sheet 2,

which is pulled from a different source, has names, birth date,
attendance date, account balance, testing date, etc.

Sheet 1 may have names that are not on Sheet 2 and vice versa. I want a
Sheet 3 which has all of the columns.

It should also combine clients...for instance, John Smith is on sheet
1 with his application date and account number

and he is also on sheet 2 which shows his attendance date and account
balance. Instead of getting two John Smiths on sheet 3,

I would like it to combine the information into one row which shows
his application date, account number, attendance date, and account balance.

Doing it manually is not an option since there are so many, but I'm
not sure how else to do it. Do you have any suggestions?

I really appreciate any help you can provide. Thank you so much!!

correction:

=INDEX(Sheet1!C:C,MATCH(Sheet2!D1,Sheet1!D:D,0))

or the array formula

=INDEX(Sheet1!C:C,MATCH(Sheet2!A1&Sheet2!D1,Sheet1 !A:A&Sheet1!D:D,0))

isabelle

Le 2013-08-14 22:18, isabelle a écrit :
if there is no duplicate entries account number on sheet1 and sheet2

you can use this formula (it is not an array formula)

=INDEX(Sheet1!C:C,MATCH(Sheet1!D1,Sheet1!D:D,0))

isabelle


Le 2013-08-14 22:11, isabelle a écrit :
hi,

if there is no duplicate entries (name and account number) on sheet1

looking for names and account number (i suppose it is columns A and D)

put this formula on sheet2,

=INDEX(Sheet1!C:C,MATCH(Sheet1!A1&Sheet1!D1,Sheet1 !A:A&Sheet1!D:D,0))

array formula, validate with ctrl + shift + enter

then change this part C:C to get info for different columns

note:
when you change a part of formula, dont forget to validate with ctrl +
shift + enter

when the data are collected and verified,
i suggest that you make a copy / paste special / values,
array formulas are using lots of memory

isabelle

Le 2013-08-13 13:16, a écrit :
I have two spreadsheets. Sheet 1 has names, birth date, application
date, account number, etc. Sheet 2,

which is pulled from a different source, has names, birth date,
attendance date, account balance, testing date, etc.

Sheet 1 may have names that are not on Sheet 2 and vice versa. I want a
Sheet 3 which has all of the columns.

It should also combine clients...for instance, John Smith is on sheet
1 with his application date and account number

and he is also on sheet 2 which shows his attendance date and account
balance. Instead of getting two John Smiths on sheet 3,

I would like it to combine the information into one row which shows
his application date, account number, attendance date, and account
balance.

Doing it manually is not an option since there are so many, but I'm
not sure how else to do it. Do you have any suggestions?

I really appreciate any help you can provide. Thank you so much!!