HOW DO I COMBINE two spreadsheets into one by names in column a

M

MAD101

I have twi spreadshees that havethe same employee names. One spreadsheet has
the employee names in column a and overtime hours in column b, and standard
hours in column c. The second spreadsheet has the employee names in column a
and track hours in column b. I want to combine these two spreadsheets to
where the employees with same name will have their hours all in one row.


Ex.
Name track_reg
ABRAMS, JAMES 11.50
ACKER, EDWARD 8
ADAMS, CLIFTON 12.50
ADAMS, JAMES 11.50
ADAMS, JENNIFER 11.50
ALBERSON, OWEN 11.50

Name OT ST
ABRAMS, JAMES 0 11.5
ACKER, EDWARD 0 11.5
ADAMS, CLIFTON 5 8
ADAMS, JAMES 0 11.5
ADAMS, JENNIFER 0 11.5
ALBERSON, OWEN 0 11.5


I would like the hours to all in one row for each employee.

Ex:
Name OT ST track_reg
ABRAMS, JAMES 0 11.5 11.50
 
M

Max

Assuming your 1st table is in Sheet1's cols A and B,
2nd table in Sheet2's cols A to D, data from row2 down

In Sheet2,
Put in E2: =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))
Copy E2 down as far as required. Suggest that you also do a one time prior
clean up of the possible extraneous white spaces in col A in both Sheet1/2
which might otherwise throw apparent good matches off. Just select col A,
click Data>Text To Columns>Check "Fixed Width">Finish.

And if you need an error trap to return zeros for any real unmatched cases,
use this in E2, copied down:
=IF(ISNA(MATCH(A2,Sheet1!A:A,0)),0,INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)))

If for some reason you can't do the quick clean up to remove the extraneous
white spaces, here's a standy alternative which uses TRIM to use in E2,
normal ENTER, copied down:
=INDEX(Sheet1!B$2:B$100,MATCH(TRUE,INDEX((TRIM(Sheet1!$A$2:$A$100)=TRIM(A2)),),0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
 
M

MAD101

Thank you, that solved my problem.

Max said:
Assuming your 1st table is in Sheet1's cols A and B,
2nd table in Sheet2's cols A to D, data from row2 down

In Sheet2,
Put in E2: =INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0))
Copy E2 down as far as required. Suggest that you also do a one time prior
clean up of the possible extraneous white spaces in col A in both Sheet1/2
which might otherwise throw apparent good matches off. Just select col A,
click Data>Text To Columns>Check "Fixed Width">Finish.

And if you need an error trap to return zeros for any real unmatched cases,
use this in E2, copied down:
=IF(ISNA(MATCH(A2,Sheet1!A:A,0)),0,INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)))

If for some reason you can't do the quick clean up to remove the extraneous
white spaces, here's a standy alternative which uses TRIM to use in E2,
normal ENTER, copied down:
=INDEX(Sheet1!B$2:B$100,MATCH(TRUE,INDEX((TRIM(Sheet1!$A$2:$A$100)=TRIM(A2)),),0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

combining two spreadsheets with a common field 1
HOW DO I COMBINE DATA FROM MULTIPLE ROW INTO ONE ROW AND ADD VALUE 3
Sumif from two criteria in one column 4
How do I create a small array from a larger one? 3
How do I split names from one column to two? 1
How do I combine two columns of text to 1 as in 1st name surname 2
How do I combine information from two spreadsheets into one? 1
how do I combine two columns to create a third column 2

Top