I have two spreadsheets that have one set of data in each column in common.... simply put it is a number.
From the one spreadsheet, I want to pull the column that is to the right of the number in column and merge it with the other data.
I am trying to figure out a formula that will accomplish this... I have attached a file to show the two spreadsheets and my end result I would like to have.
I have two workbooks, Quotes and Invoices. They have a common field (column) between them called "QuoteID". Since not all quotes turn into invoices, there are more rows in the Quotes workbook than the Invoices one. Is it possible to match the "QuoteID" cell values in Invoices to just the ones in Quotes that match up, and then merge them into a new worksheet? If not, is there an add-on that will accomplish this?
View 1 Replies View RelatedI want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
Got an issue that I can only seem to do with things like the program Hotkey and making physical macros. I'm sure it is easy but I'm lost.
I have data in one book that looks like this (Call it Book1)
Date: Service
ProviderReason for callCRM
SR #DM
SR #WO #Device #Serial #Issue15/02/2010DATE: 03/02/10
TIME: 11:29
BUS: 1234AO
ISSUE DETAILS: Issue with Console
and data in another book that looks like this (Call it Book2)
DeviceSerial NumberNameTerminal PositionTerminal Group Location DescriptionTerminal IDDescription Service Provider 104012343030123456ConsoleDriver Console1234AO90401234Service Provider Name found Here
I need to find the BUS: 1234AO from the cell in Book1 then look that Bus up in Book2 and then from Book2 copy the Device, Serial Number, and Service Provider into the cells into Book1 and repeat this infintely often. The problem I have is the the bus can have 1 Console and 1-5 Readers and I need to find that data also. So for example I need to find BUS 1234AO and get the Console details and past that into one issue and the next issue will have the same bus number 1234AO but I need the details for Reader2 (that detail is in the Name field as shown above.) The issue details will always have the Console text and Reader# text in it.
The problem I have is that the Reason for call cell has ALT-ENTER characters in it and the details are not always in the same spot but the details are almost always in the format ####AO (four numbers and the letters AO)
I have attached the excel sheet here for reference.
I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.
In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.
How should I code up a VBA for that?
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
I am trying to write a VBA code to conditionally copy and paste a range of cells on sheet A into sheet B if the specified cell value is not found on sheet B. Here's an example
On sheet A, there are phone numbers in column M that I need to find in sheet B. The reference column in sheet B is already a named range ("Description") which I'm using to conditionally format several cells in sheet A to turn green when the phone number is not found on B. This is the formula I'm currently using to do this "=AND( COUNTIF(DESCRIPTION, $M2)=0, $R2>0)" ...Obviously I'm using the AND function to tell the format to only work if the cell value of R is greater than 0.
What I would like to do to something very similar to what I already have in place, only now I would like to have a macro search through column M and instead of highlighting the proper cells...I would like to copy the associated row data from columns B:N in sheet A and paste special>values into A:M on sheet 2. Also, I need to keep the "R is greater than 0" condition in place.
I have searched and there are some examples that are similar to what I'm looking for but I cannot seem to use those answers to make my problem work. The example from the forums that seems to match my problem the closest is [URL]....
Summary: I have an Excel file called "COST TEMPLATE.XLSX" that has product information (serial number, quantities, pricing), calculations and reports; then on a monthly basis I receive a file (VENDOR_EXPORT.CSV) with updated product quantities to be merged into the cost spreadsheet.
I would like to merge specific data fields from the VENDOR_EXPORT.CSV file into my cost template based on the serial number. I am attaching three dummy files that hopefully describes the issue:
1. COST TEMPLATE.XLSX
2. VENDOR_EXPORT.XLSX - which highlights the cells to be import and to which corresponding column
3. VENDOR_EXPORT.CSV - raw CSV file
My preference is to do this with functions, not macros. How to add the export into the existing workbook as a temporary worksheet, or just pull from the other workbook as CSV file.
Looking for some code to do a simple compare column A to Column B (row 1 contains headings) and highlight any differences.
View 3 Replies View RelatedI have a list of parts in Column A, often times with duplicates in the column.
I have a list of parts in Column B (from another source), again, with duplicates.
What I need is - leaving columns A and B alone;
Column C to be unique Column A parts
Column D to be unique Column B parts
Column E to be parts in common between Columns C and D.
I have a workbook with several sheets containing prices of products. One column has the product number and a second column has the price. We just received new prices. Can a macro read the product number from the new sheet, find the same product number on the old workbook and transfer the new price. and than change the price so I know it was updated.
View 2 Replies View RelatedI have a range of data that has been collected by date starting in H4 across several columns. This means that my entries start at different points in each row i.e. -
Row 4 first entry Column AJ
Row 5 first entry Column K
Row 6 first entry Column AB
Using vba is it possible to find the first entry in Row 4 and copy it and all the figures following it in that row to K4 on Sheet2, and then row 5 to K5 on Sheet2 and so on?
I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A
B
C
D
E
F
G
H
1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY
2
and
3
and
2
and
10
and
5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
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 Relatedi want to compare two excel sheets and extract common data in either sheet. For eg: If sheet 1 and sheet 2 contains some common data, i need to compare or lookup or whatever i do but i need to pull out that common data in sheet 2.
View 3 Replies View RelatedI have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
The attached file shows what I'm trying to do - I'd like to fill in the data in the top left corner table (blue-shaded) with the data combined from the two other tables. Think of the Greek letters as names that are grouped into the groups named {A,B,C,D,E} (the names have no significance, it's just an example).
I was able to do so using an array formula and an extra column (yellow shaded) as you can see in B2 - however, this solution doesn't suite my case because the actual tables will be coming from automated processes and cannot be easily manually altered.
I tried inserting the array formula into the sumproduct formula - see B3 - but for some reason this doesn't behave the same as when the array is outside the formula. I should also mention that I came across a user defined function solution - this too is not so suitable for me right now.
I'm compiling data to be transfered into a report program but the programe can't deal with any Excel Formulas so the data has to appear 'as is' so to speak. I have 2 columns of data (roughly 1200 rows) and I need to merge the two together but i can't have the formula in the merged cell......
View 4 Replies View RelatedI collect data from a number of tools for our server and switch info. I don't have a problem merging that data. My problem is trying to get that data into a working format.
-- I have a server that has dual network connections.
-- This data resides in different rows, and each has slightly different data that I need to preserve.
-- I want to merge these two rows into one pre-defined output layout on a separate sheet without loosing any of the data.
Some other things of note. Not all of the servers have duplicate entries, but still want that data on the output sheet as well.
I have attached an example of what I have to work with and what the output sheet that I'm trying to get to looks like.
I have an excel spreadsheet supplied by a client, let's call this the master sheet, with about 500 URLs and I've been asked to gather information from Google Analytics and place in a spreadsheet. This spreadsheet can't be changed as it goes into their CRM system.
On the master sheet, we have a list of URLS, what I need to do is go through each URL and place the number of visits to each URL on a weekly basis.
Google Analytics excel spreadsheet, will spit out a list of URLs and the page visit numbers.
My question is:
If I have both spreadsheets open, the master spreadsheet and the information from Google Analytics spreadsheet. Is there a way of having both sheets open and doing an exact match on the URLs between both spreadsheets, so we can copy and paste the information directly.
Or even a formula that says something like:
If the master sheet spread has the same URL in the Google Analytics spreadsheet, paste the relevant information into the correct cells?
As you can see, doing this manually will take a long time. I'm just looking for a productive method to save time.
I have a sheet with the registered members of our program (about 600 of them) with the date they registered for 2007, and I have another sheet with the same but for 2006, another sheet for 2005 etc.
How can I merge them into one sheet with columns for name, area, and 2006, 2007 etc? That way I can have one sheet giving up to date information for our members as they come and go over the years...
I need assistance with a formula two merge data of two cells together. For instance I have a column 'first name' and 'last name' and I want to create a column of just 'name' and concatinate the data eg: John Smith. I have roughly 175 cells to do this for.
View 5 Replies View RelatedI have 2 large (100k+ rows) Spreadsheets, where one has lots of information including each entries state and City and I also have another spreadsheet which has city and postcode information.
I need to get the postcodes into the main sheet that already has the cities but not the postcodes.
Is there a way to import this seeing as each sheet contains fields of similar data (ie. the city information)
Sheet 1 example fields:
Name, description, state, city
Sheet2 fields:
State, City, Postcode
finish up a macro. The first part of the macro that is already working copies and paste 4 cells content from each worksheet found in a closed workbook thus generating 4 rows of data for each worksheet.
How to merge those 4 rows of data into one row. I have attached an example with the original data and the results data I am looking for but here is an explanation:
The column that is a constant and that I want to match is column B (Worksheet name). The 4 rows generated per worksheet will be showing on the first row data in Column C and columns D,E,F will be blank; then the 2nd row of data will have column C blank, column D with data and column E,F, and so on for the next 2 rows. What I would like the macro to do is merge all 4 rows so that the data found in cell C,D,E,F can be found in one row.
example.xlsx
i have two excel sheets named APP1.xls and APP2.xls. I want to copy the data from both these files into one sheet.
I want to copy the Header row which is common for both files and i want to copy the row which has Total for that Application.
Have attached both the Input files and the outfile for your reference.
i have a workbook in which table is given on sheet 1 , and sheet 2 respectively , i want a macro which will copy sheet 2 data in sheet 1 below sheet 1 data .
View 4 Replies View RelatedI am trying to merge data from 2 tables into a master sheet. Column goes like this 1 = customer ID, 2 = allocated number, 3 = age
The first table has around 2000 entries and 2nd table has about 1000, and they both contain some overlapping entries. I wonder if there is a simple way to go around this rather than manually looking at each individual entries as I will be more tables to fit in later on.
How would I combine the data in 30 cells into one cell.
They all 12 digit numbers and I need them separated by a comma.
Hi i need to combine and merge difefrent data into groupings. EXAMPLE
1ax
1bx
1cx
2ax
2bx
2cx
It needs to end up as
1ax
2b
c
With a 2 or 3 empty lines until the next set of data
But if for example i have this:
1ax
1bx
1cx
2ax
2bx
2cz
3 c z......................