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

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

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(She et1!$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
---
"MAD101" wrote:
> 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

Thank you, that solved my problem.

"Max" wrote:

> 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(She et1!$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
> ---
> "MAD101" wrote:
> > 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

Welcome, but pl take a moment to press the Yes button below, from where
you're reading/posting this.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"MAD101" wrote:
> Thank you, that solved my problem.