Consolidate & Combine Data from Separate Worksheets or Workbooks(Excel Files)

Hi!


I have three set of excel sheets from three different sources of data. Each sheet has unique ID number. I would like to consolidate the data as one. For instance, I would like to know how many times a unique ID number is on the different excel sheets.

Hi all
If anyone can help me. I got a problem on how to consolidate data from different excel files within one workbook.

Please help me the following

excell file 1:
sUNDAY MON TU THUR FRI SAT SUN TOTAL HOUR
Name of assignment
PLAYING GOL 4 4 4 12
MEETING CLIENT 4 4 4 12
SEMINAR 4 4 4 12

EXCEL FILE 2:

sUNDAY MON TU THUR FRI SAT SUN TOTAL HOUR
Name of assignment
EATING 4 4 4 12
SHOPPING 4 4 4 12
DINNER WITH CLIENT 4 4 4 12


The data is something like above, what i want is to merge or consolidate or whatever you may call all of those data into one workbook (summary), so that i can know, how many hours per week (month) that i have dinner, play golf, eating out, etc

Can anyone please help me.
thanks a lot

cheers
Harry

I have multiple worksheets with all the same headings.
Name:Title:Dept:ComSvrName:ComSvrMonth:ComSrvYr

Each worksheet can have different total number of rows.
W1
Jay:Director:Math:helping:8:2006
Alex:Cord:Sci:Dissecting:2:2003
Ray:Asst:Art:Drawing stuff:4:2005
Debra:Asst:Health:driving car:2:2002

W2
Jay:Director:Math:teaching students:7:2001
Alex:Cord:Sci:universal studies:5:2005
Ray:Asst:Art:dancing:1:2004

W3
Jay:Director:Math:times table:4:2002

How can I combine all the worksheets so we get a new worksheet with just 1 row for each person's information?
The cell names would look like
Name:Title:Dept:ComSvrName:ComSvrMonth:ComSrvYr:ComSvrName2:ComSvrMonth2:ComSrvYr2:ComSvrName3:ComSv rMonth3:ComSrvYr3

NOTE: Although there are a different number of total rows in each worksheet the person's name, title, dept do not change. One the comsrv info changes.

Any help with this would be super great!
Thanks,

I am attaching the files for your reference.

MASTER FILE

http://www.box.net/shared/dat9pgal4o


PAF FILE

http://www.box.net/shared/itz4dbg48h


PSI FILE

http://www.box.net/shared/cdj0szqhj3

My datas are in the path "C:\Temp"

I have three files named 0001.PAF, 0002.PSI & Master File.xls

The first two files (name of the file will be different each time) are basically CSV files but their extention changed from CSV to PAF & PSI respectively.

0001.PAF has the fields from column A to Column J.
The required fields in the context are Column B,E,F,G,H

0002.PAF has the fields from Column A to Column J (in which column E,F,G,H,I blank)
The required fields in the context are Column B & C


POINTS:

1. PAF Files contains the salary details of the employee Column G+H is the total salary of the employee
2. PSI file contains the name of the employee
3. There is 14 digit no in these two PAF & PSI files which is the employee id and is required to identify the correct employee and correct salary by using VLOOKUP function

My task is to import the data contained in two files that is 0001.PAF & 0002.PSI (basically CSV file only the extension different) into sheet1 and sheet2 of Master File.xls based on the follwoing conditions

1. The data should be imported after using the VLOOKUP function, so as to ensure the accuracy of the data
2. Employee Name should be sorted in the alphabetical order
3. Salary amount should be the total salary ie G+H from PAF FILE
4. Total Salary should be splitted into currency denomination of 500,100,50,20,10,5,1
5. The data should have Header (The co name in the header is there is the last name in PSI file)
6. The data should have Footer (The data always: SALARY PAYOUT by: XYZ CO LTD
7. The alignment and format of the data should be as per the Master File


Kindly note my Output file Master File.xls for your refenece

Thank you all for going through my post. Awaiting for a solution. Thank you.

I have a rather large data set where I have 216 information processing trials for 30 people. Each trial contains 6 variables (duration, position picture appeared, type of picture, correct response, response time of subject, and subject's response). I need to split up the data into 3 main categories (3 different types of pictures). However, the pictures are randomized so no 2 subjects have the same order. I need a macro that I can say (for example):
If text = Neutral, then copy that cell + 2 cells above + 3 cells below into sheet 2.
If text = Blood, then copy that cell + 2 cells above + 3 cells below into sheet 3
If text = Fear, then copy that cell + 2 cells above + 3 cells below into sheet 4.


Is there someone who can help me?

Hello,

I have a sheet with random entries in the columns A:A to D:D

like this
col.A col.B col.C col.D

AK 39448 39457 10
OW 39449 39451 3
NB 39449 39451 3
OW 39532 39542 9
TL 39570 39570 1
BG 39573 39578 5
TL 39591 39591 1
etc.

If I have the value OW in cell E1 I would to see all entries made for this product.
Result=

col.F col.H col.I col.J

OW 39449 39451 3
OW 39532 39542 9

I know that VLOOLUP can do this but only if the matrix is sorted ascending and this is not possible, how can I else deal with this and geht the datas on for the product OW ??
Is there any way of doung this, I know I could also do it with a pivot, but is ther another way of doing this (cause some users here do not know hwo to use a pivot)

Thanks for you help


Uli

Hello,

I am stuck on a very strange situation. I have my data arranged like this:

Sheet 1

Part # Part Name Comp # Comp Name Quantity
ABC Cell Phone 123 Screen 1
ABC Cell Phone 456 Keys 1
ABC Cell Phone 789 Battery-1 1
XYZ MP3 Player 123 Screen 1
XYZ MP# Player 101 Battery -2 1

Sheet 2
Part # Part Name Comp # Comp Name Quantity
ABC Cell Phone 123 Screen 14
XYZ MP3 Player 123 Screen 14

When I consolidate data, I usually get multiple blank cells. I am not sure how I should merge the data from 2 sheets.

Hello all. I am working on a project that is two part. I am working with Excel 2007. I have a workbook that has 50 or so worksheets that I need to split and save as individual files. I found the following code online. It works fine, however, I need the "new" workbooks to be saved as Excel 97-2003 files. What do I need to add to this to make that happen?

Code:

Sub splitsheettoworkbook()

Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Worksheet 'Could be chart, worksheet, Excel 4.0 macro,etc.
Dim strSavePath As String

On Error GoTo ErrorHandler

Application.ScreenUpdating = False 'Don't show any screen movement

strSavePath = "C:\Excel Worksheets\" 'Change this to suit your needs

Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & sht.Name
wbDest.Close 'Remove this if you don't want each book closed after saving.
Next

Application.ScreenUpdating = True

Exit Sub

ErrorHandler: 'Just in case something hideous happens
MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."

End Sub

Part Two is this: I have another file that I'm needing to split as well. However, the information in this workbook is organized in a Pivot Table. I have utilized the 'Show Report Filter Pages' to have the information on separate worksheets so that I can split the file. Will the above code (with the modification to save in 97-2003 format) work with the Pivot Table?

I have some code that looks like it works, but never actually saves the files to my directory. What am I doing wrong

I am trying to use Acrobat Distiller to print each tab in my workbook to it's own PDF file:

Public Sub SaveWorksheetsaspdf()


Application.ActivePrinter = "acrobat distiller on Ne04:"
ActiveWorkbook.PrintOut , , , , "acrobat distiller on Ne04:", True, True, "\Test" & stTab & ".pdf"

End Sub

I have about 60 sheets with different names(not named sheet 1,2,3... but say aaa,bbb,ccc...) in a single workbook. I would like to save every 3 sheets as a separate workbook. The filename should be the name of the first of the three sheets.
For Eg: Sheets aaa,bbb,ccc as workbook aaa,sheets ddd,eee,fff as workbook ddd and so on(total of 30 workbooks) . I have put together a VBA code by combining sections of codes from different threads in this forum
VB:

Sub WorksheetLoop() 
    Dim WS_Count As Integer 
    Dim ws1 As Worksheet 
    Dim ws2 As Worksheet 
    Dim ws3 As Worksheet 
    Dim I As Integer 
     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count 
     ' Begin the loop.
    For I = 1 To WS_Count 
         
         
         ' store worksheets as variables
         
        wname = ActiveWorkbook.Worksheets(I).Name 
        Set ws1 = ActiveWorkbook.Worksheets(I) 
        I = I + 1 
        Set ws2 = ActiveWorkbook.Worksheets(I) 
        I = I + 1 
        Set ws3 = ActiveWorkbook.Worksheets(I) 
         
         ' copy worksheet objects ws1,ws2 & ws3 to a new book and save
        application.workbooks.copy (array(ws1,ws2,ws3)) 
        ActiveWorkbook.SaveAs Filename:=ws.Name & ".xlsx" 
        ActiveWorkbook.Close 
         
    Next I 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



A syntax error is thrown at the line "application.workbooks.copy (array(ws1,ws2,ws3))" .Please help debug this or create an alternate code for this.