Excel 2003 :: Merging Specific Data From Two Spreadsheets Into One

May 2, 2014

I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).

I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.

Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii

Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.

Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.

View 1 Replies


ADVERTISEMENT

Sales Data - Merging Two Spreadsheets For Consolidated View

Jul 31, 2014

I have two years of sales data for same and different product codes in different sheets for each year.

I want to merge both the sheets so that i have a consolidated view of both years data in one sheet.

The sheets have columns that look like the one described as under:

Product code Month 1 m2 m3 m4 m5 m6 m7 m9 m10 m11 m12

View 2 Replies View Related

Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)

Referral
A
B
C

[Code]....

way to do the calculation using Excel 2003

View 9 Replies View Related

Excel 2003 :: Finding Average With Multiple Spreadsheets?

Dec 24, 2012

I am currently using Excel 2003. I have a worksheet with two tabs.

First tab has a list of bank Names. Second list has Bank Names and balances.

I need to find out the the average from a Bank in the first tab, to the same bank on the second tab that reflects the balances.

View 6 Replies View Related

Merging Content From Two Spreadsheets

Oct 24, 2008

What I am trying to do is take the data (all columns) in Spreadsheet 2 and put that data in Spreadsheet 1 with all the companies matching up, how do I do this, is this possible?

I tried using VLOOKUP but I don't know if I was doing it right and I don't even know if it's the right function to use.

In sheet 1, column A I have a list of about 30,000 companies
In sheet 2, column A I have a list of about 10,000 (pulled from sheet 1), I have other info associated with these companies in columns B and C.

Basically I want to take column A, B and C from sheet 2 and match them up with the same companies in sheet 1. So there will be 10,000 matches and 20,000 N/A's. This is how I set up the VLOOKUP but nothing happened.

View 8 Replies View Related

Merging Template Changes Into Spreadsheets

Jul 14, 2013

I have a template and several spreadsheets based on this template, and when changes are made to the template they need to be manually merged into each spreadsheet, a very painstaking, time consuming, and error prone process. Changes are mostly added rows, data tables, and macro (coding) changes. Surely there must be a better way of doing this. There must be some way of automating at least part of this process, or so I would think. I know that changes to a workbook can be tracked, but I don't shows changes to code, and I don't know if it is true for templates as well (but I suspect that it is); at least this would show me what changes to the template had been made, but maybe not changes to the code, but this is only a small part of the problem.

View 1 Replies View Related

Merging Two Spreadsheets Not Consolidating

Jul 18, 2012

I have two spreadsheets of part numbers, one with a full list of parts approx 3500 lines and another with the same part numbers (but only approx 1000 lines) but this file also contains sales history. columns shown below

PART NUMBER, PART DESCRIPTION, RETAIL, COST, UNIT OF ISSUE, CURRENT STOCK, YTD SALES, MTD SALES

What i want to do is merge the two files together so the 1000 parts i have sales history for correspond with the same 3500 lines on the other master spreadsheet, so i can then sort them in which ever order i need at the time.

View 5 Replies View Related

Merging Two Identical Spreadsheets

Aug 11, 2006

I have three large txt files. All three are the same number of columns but are of a different number of rows. (Each month the number of columns will increase but the 3 source files will still contain an equal number).

Therefore I need to open the first text file, select all data and paste it into my master file.

I then want to open the second txt file and paste all the data at the first empty row of my master file.

I will then open the third txt file and paste all the data at the first empty row of the master file.

Finally I will close the txt files.

I will then have several other amendments to make to the master data file.

So to my problem....

Opening the txt files is fine.

However I want to select all the data in the first txt file but excel gives me an error message. (On the line highlighted in my code). I remember there being a function in VBA that selects all data around a cell, can anyone remember how to do this?

My code so far is:

Workbooks.OpenText Filename:= _
"C:CUSTOMERPass 1.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array _
(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8 _
, 1), Array(9, 1))
Cells.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Sheets("Data").Select
ActiveSheet.Paste

View 5 Replies View Related

Merging Contact Info From Different Spreadsheets To One Row?

Jan 23, 2014

I have a spreadsheet with names and phone numbers. I have another spreadsheet with similar- but not the same names and not in the same order- with email addresses.

I have pasted all info into one spreadsheet and alphabetised it- it looks a little like this now.... (joe bloggs being an example name)

joe bloggs telephone no
joe bloggs email

Is there a way i can easily merge these two rows keeping the name the same? Otherwise it will take FOREVER!!

View 5 Replies View Related

Merging Spreadsheets And Keeping Duplicate Rows?

Aug 15, 2014

I have a spreadsheet with only 80 rows in it and I need data from another spreadsheet with over 200 rows. One of these rows in each spreadsheet contains a matching field (Job #). I only want the 80 matching rows data from the over 200 row sheet.

Is there an easy way to combine these two. Even adding the 80 to the 200+ will be fine as I can just remove the blanks. I can't figure out a way to combine them and match them up.

View 2 Replies View Related

Collating Data From Multiple Spreadsheets In A Specific Folder

Apr 13, 2007

I have built a timsheet template which I intend to issue to c.140 personnel in my company to fill in for the month of May. On receipt of these timesheets, I will save them all in one, specified folder. I would then like to consolidate the total number of hours worked by adding cell N53 from each timesheet received. Is there any macro/ VB script which can add the contents of a particular cell (N53 in this case) from all the spreadsheets contained within a particular folder?

View 2 Replies View Related

Excel 2003 :: Get Number Of Items Against Specific Department?

Sep 7, 2012

I'm trying to get the number of items against a specific department which are contained in a sheet called "data", the sheet is contained in the same workbook.

There are named ranges for
Department (text)
Date_From & Date_To (in the format dd/mm/yyyy)
Band (Numeric 1 to 3)
Items (Numeric)

Using the following formula I get an #NUM error (using Excel2003)

=SUMPRODUCT(--(Department=A7),--(Date_From>=$B$2),--(Date_To

View 9 Replies View Related

Excel 2003 :: Convert Specific Date Text To Number?

Jun 20, 2014

I want to use a formula, in another cell, to convert ‎"Friday, ‎30 ‎May ‎2014, ‏‎3:47:16 PM" to a value. I am using Excel 2003.

View 2 Replies View Related

Excel 2010 :: Comparing / Merging Child Spreadsheet (purified Data) Into A Parent One Raw Data)

May 17, 2014

I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.

View 1 Replies View Related

Excel 2007 :: Merging Data Within Two Columns Into One

Dec 5, 2013

I have two columns

Req | Expiry Date
yes 11/12/2010
yes 08/06/2012
yes 02/07/2017

how do i merge both columns into one.. not losing any data hence

Req Expiry Date
yes 11/12/2010
yes 08/06/2017

I am using excel 2007 ITS FOR WORK!

View 9 Replies View Related

Excel 2007 :: Macro For Merging Data Of Alternate Rows?

Jul 7, 2014

Currently I am using Excel 2007. I want to merge data of alternate row of a particular column in Excel.Also i want to remove merged row. E.g.

Name City Pincode
John Mumbai
John 400009
Carol Delhi
Carol 110001
Carol Pune
Carol 411079

I want output to be ----->

Name City
John Mumbai-400009
Carol Delhi-110001
Carol Pune-411079

macro/formula for the above requirement..?

View 11 Replies View Related

Excel 2010 :: Merging Multiple Worksheets Into One All Data Worksheet?

May 29, 2013

I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.

How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.

How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.

View 2 Replies View Related

Excel 2010 :: Import Data From Other Spreadsheets Into Multiple Tables?

Sep 13, 2013

I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010

Here is a copy of what I have

Code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _

[code].....

Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.

View 1 Replies View Related

Excel 2010 :: Merging Data From Multiple Workbooks To Single Master Book?

Feb 16, 2012

We have a master worksheet, and I cant let staff see it, but I want them to update 'their bits' themselves, rather than email the admin staff and let them update the master sheet..

every job is on its own row and contains a unique ID.

is there a way i can merge their data in to the correct row and column(s) on the master sheet?

i've been playing with datasources etc

Excel 2010

View 2 Replies View Related

Excel 2003 :: Take Specific Text From Cell And Copy It In New One (Text And / Or Words Position Varies)

Jan 17, 2013

I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.

Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)

Useful information are included !!!

I'll give an example of a record...

Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "

Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...

Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:

Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function

Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...

View 9 Replies View Related

Excel 2003 :: Matching Row Data

Nov 28, 2011

I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.

Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1

View 3 Replies View Related

Excel 2003 :: Autopopulate Data From 1 Database To Another?

Jul 1, 2014

I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......

I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....

Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...

1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').

2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..

3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed

4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.

5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)

6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....

To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red

View 14 Replies View Related

Excel 2003 :: Web Query - No Data Has Been Found

Sep 20, 2013

I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.

This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.

[URL] ...

View 1 Replies View Related

Excel 2003 :: Data Extraction From Cell

Apr 11, 2013

Data in cell A1 - 1) A Q
Data in cell A2 - 12) V
Data in cell A3 - 131) B H L

How do I throw away the #) and the following space in cells A1, A2, and A3 and save the rest?

Excel 2003. ie. no VBA.

View 4 Replies View Related

Excel 2003 :: How To Use Filter On Multiple Data

Nov 24, 2013

I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.

Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?

Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?

View 1 Replies View Related

Excel 2003 :: Formatting Chart Data

Jan 18, 2014

I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.

View 9 Replies View Related

Google Spreadsheets - Counting Entries On Specific Date

Jan 13, 2014

How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);

Calculate the sum of the numbers in column D that occur on the 10th January 2014?

And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.

View 1 Replies View Related

2003 - Merging Four Adjacent Cells With Text And/or Blank Cells

Jun 14, 2008

I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).

I would like a comma or full stop and then a space between each item merged (cell A5).

The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.

Only where all 4 cells are blank, will I need cell 'A5' to indicate this.

The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.

View 9 Replies View Related

Excel 2003 :: Formula To Pull Data From One Sheet To Another?

Jan 16, 2014

I have Excel 2003

My spreadsheet has multiple lines of data for multiple items (have a specific item #) and I need to be able to enter an item # on a different sheet so that the requested information can be pulled from the data source. The items have information on multiple rows. I need to have all of the rows pulled into my output file when I specify the specific item. #.

I have attached a spreadsheet showing the type of data is contained and what my desired output would look like.

View 1 Replies View Related

Excel 2003 :: Pull Data From Multiple Sheets?

Mar 20, 2014

So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.

I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.

I don't have Excel 2007, only 2003 I have.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved