Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default HOW DO I COMBINE two spreadsheets into one by names in column a

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
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

  #4   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default HOW DO I COMBINE two spreadsheets into one by names in column

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to combine 200 spreadsheets Phil Smith Excel Discussion (Misc queries) 5 January 22nd 08 09:25 PM
How do I combine spreadsheets? Jennifer Excel Discussion (Misc queries) 2 August 31st 06 09:33 AM
Combine 2 spreadsheets w/1 common column of data, text and number Ginger Excel Worksheet Functions 0 March 26th 06 11:45 PM
how do i combine two separate spreadsheets? Merging Spreadsheets Excel Discussion (Misc queries) 1 March 2nd 06 05:42 PM
Combine two spreadsheets with Different column headings Angela Excel Discussion (Misc queries) 5 December 8th 05 11:25 PM


All times are GMT +1. The time now is 10:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"