A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old October 16th 08, 01:10 AM posted to microsoft.public.excel.misc
MAD101
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
Ads
  #2  
Old October 16th 08, 01:43 AM posted to microsoft.public.excel.misc
Max
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 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

  #3  
Old October 20th 08, 01:58 AM posted to microsoft.public.excel.misc
MAD101
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 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

  #4  
Old October 20th 08, 01:12 PM posted to microsoft.public.excel.misc
Max
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to combine 200 spreadsheets Phil Smith Excel Discussion (Misc queries) 5 January 22nd 08 08: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 04:42 PM
Combine two spreadsheets with Different column headings Angela Excel Discussion (Misc queries) 5 December 8th 05 10:25 PM


All times are GMT +1. The time now is 01:11 PM.


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