View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
MAD101 MAD101 is offline
external usenet poster
 
Posts: 7
Default HOW DO I COMBINE two spreadsheets into one by names in column

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 DataText To ColumnsCheck "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