I have 2 tables (see attached short sample) and need to merge them. Both tables containing 1 common ID for location (S220) and one common number. I need to fill out Table A (column A-B) with corresponding numbers from Table B (from column E-J). The desired outcome showed in Table C (column O-P).
View 5 RepliesI have a couple of spreadsheets. all of them have a same or common column called "ID".
The order of the ID columns of the spreadsheets are not in the same order however.
I'd like to see if there is a way that I can merge the spreadsheets to one using the shared ID.
To explain more:
Spreadsheet1 columns are: "ID", "URL", "Location"
Spreadsheet2 columns are: "ID", "Picture"
Spreadsheet3 columns are: "ID", "Address"
As I said copy/past is not an option as the IDs are not in the same order. I need to create a new spreadsheeet and have all "ID", "URL", "Location", "Picture", "Address" in that spreadsheet.
I have multiple worksheets with multiple varying columns with varying rows. My one constant is the product_id. I want to merge all worksheets into 1 worksheet based on the product_id's. Here is my example:...............
It has to consolidate all of the column names from all of the worksheets into the final worksheet, then take all of the rows and put the product_id in the product_id column and put the other data under the appropriate columns. Some columns will end up blank where they may be a column in worksheet 2 but not worksheet 1.
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 am trying to merge two different databases in the next week. The problem I am running into is the name from 1 database isn't always the same on the other database.
I want to merge Database 1 with Database 2 and create a relationship with them both by using the database 2 internal #. As you can see in my table database 1 has similar names to database 2 but they are not exact. I want to match these names and have the formula on match tell me what the database 2 internal # is. I have also uploaded a sample excel doc with a note in B2.
database1
Herb Chambers Ford of Braintree
Herb Chambers Honda of Seekonk
Dimmitt St. Petersburg
Database 2 matched internal #
Database 2
Herb Chambers Ford of Braintree (Dave Dinger Ford)
Herb Chambers Honda Of Seekonk (7-FI)
Dew Cadillac (Dimmitt of St. Petersburg)
Database 2 internal #
15360
15301
25413
I have a table. I give this table to 3 different people. Each one fills information in their corresponding table.
How do I do it, so their information gets feeded into my table, without overwriting each other.
I have been working on a project for a few days now, it is almost finished but I am struggling with creating macros. I will send a file in the attachment and I will tell you what I need to accomplish :
1. I need to add to every sheet a button " add new record" - this button will add a new line and copy the formulas and dropdown menus + formatting from the last line , but not the actual data. The numbering will be 1. 2. 3. etc .. in the first column.
2. I need to create in the Price offer sheet the last line that will sum up the total weight and the total price of all the products ... this line will be always the last and the "add new record" lines will be added above the summary line.
3. I need to create a drop down list in Price offer sheet ( via validation data and Vlookup function) , but the problem is that the source for the dropdown list has to be merged from the data from sheets pipes and others ( name columns) ... the data should be ordered in the ascending order for better seach ( or even better would be to create a search function that will search a record based on input letters in every dropdown menu - I dont know if something like this is possible in excel )
I'm having an issue where I want to merge lots of tables held in different spreadsheets into one large table so that I can use it in a pivot table. The attached spreadsheet demonstrates what I mean:
So there are 3 different accounts held in the first 3 tabs. These all have the same columns and format. I want to merge these automatically into the table that is in tab 4 so that I can use the pivot table also demonstrated in this tab.
What I want to happen is when I add rows to the tables in the first 3 tabs, these are automatically populated onto the 'overall' table in the 4th tab. (I'm aware I could do this manually, but for what I'm using this for these will be dozens of tables with hundreds of rows updated monthly).
Is this possible? Basically it doesn't matter how it's done, as long as i can show the information held in the pivot table each month end.
I 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]....
I have data in two columns
Column A has values with names like -
John, Paul, Hena, Kumar, Paul, Hena, John in different rows
Colum B has day values - eg , sun, mon, tue, wed , thu, fri and sat
I want to insert a new column C with formula that returns
1. for every John, concatenate of all the day values adjacent to row of John eg "sunmonfri"
Similarly for all the values in Column A, Column C should reflect the concatenate values of Column B.
This is kind of an extension to a previously solved post. For a similar problem I used the attatched file (which someone from here so kindly came up with - but to be honest i dont fully understand),
but now im getting onto numbers from 1 to 100 so could get very confusing in the table!
RANDOM TABLE2.xlsx
Basically I want to produced a random number, (which I have managed) then another number, but it cant have any common factors as the first.
So 1st = 10, 2nd = 13 is acceptable
but 1st = 22, 2nd = 12 is not.
I have managed to do it with smaller values by just typing then out, but this is a very long winded technique, but is there a formula that can do this?
how can i extrac & merge number to set with three digit.
on my sample image if i has number of Ditgit#1,Digit#2 and Digit#3 on "Result" title.
How to make fomular with out VB to extrac each number and Merge number 3 digit/set on "Extrac and Merge number" title.
I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".
So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?
Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.
The purpose of this data is for reference to question #2, so that i can utilize some percentages.
Here’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
Is there a field setting in pivot tables to return the number of unique records?
Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).
Data Base
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-
I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-
TRIP#
1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005
I am looking at some combination of Index, Match and array functions in order to assign a value, in this case a currency, based upon an instance where another data item with the same trade ID already has that currency assigned to it.
Basically I have a list of data where there are a list of trade IDs for each data row/item.
In some cases there will be two rows of data that have a common trade ID.
For example there may be two results that share a trade ID eg 450222, one in EUR, one in another currency eg GBP.
What I need to do is create a formula in other column to the right of my data that recognises that if a trade ID is assigned to a non-Euro currency then to apply the foreign currency to both trades with the common ID.
ie change
Ref 450222 Ccy EUR
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy Eur
to
Ref 450222 Ccy GBP
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy USD
An offset function can't really be applied as the order of the data is random. My subsequent calculations involve a SUMIFS on GBP, USD etc.
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 a spreadsheet that lists two columns - impressions and clicks - by date. Sometimes the same date is listed in multiple rows. What I would like to do is add the columns so that I have a total number of impressions and a total number of clicks by each date.
For example my data could look like:
Date || Impressions || Clicks
01/01/09 || 5 || 6
01/01/09 || 3 || 4
01/01/09 || 4 || 7
and I would like it to look like this:
Date || Impressions || Clicks
01/01/09 || 12 || 17
A shortened spreadsheet is attached. The actual one is for the past year with over 1000 rows, which is why I'm hoping someone can point me in an easy direction to do the summing.
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.
See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?
doc list.xlsx‎
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?
I have 300 number groups and each group has 22 different numbers (from 1 to 80).
Can I find the most common 5 or more numbers in these groups by excel, macro or any program?
for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.
My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.
What functions will allow me to get the item number information I want from Report 2 into Report 1?
I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.
I have Column A with file paths from the same main directory.
C:UsersSJohn.AJAXDesktopTimeBills.docx
C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx
C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx
I would like to find the last common folder along the entire Column (Time),
remove all to the left, and insert E: in its place. Getting:
E:TimeBills.docx
E:TimeDChargers hick.docx
E:TimeDChargers raps.doc
E:TimeDChargersDodgersstadium.docx
E:TimeDChargersDodgersstats.docx
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.
I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?
View 10 Replies View RelatedThe file have 4 Tabs (see attached file). The tab called staff, project 1 and project 2 are manually entered. I need to combined some of the information contained in the 3 tabs into "assignment" tabs in order to show the staff workload against the projects/tasks assigned to the individual staff.
In "assignment" tab, I would like to get have pull-down function to obtained the Project 1 and Project 2 "Activities" in Cell Column B & C and automatically placed "start" & "finish" date Cell Column D & F onto the assignment tab Cell Column B, D, E & F
In "assignment" tab, the staff assigned to execute the tasks will also be able to obtain by pull-down function obtained from the "staff" tab, the position Column C, Name in Column F, Job in Column E, Start in column H and Finish in column I onto the "assignment" tab Column B, C, D, E & G
The timeline should show automatically the bar based on the start & finish date. The colour for Task and Staff to be different colour.
I have two lists, let's say:
A B
Eva 1982
Anna 1981
Lisa 1983
John 1980
Steve 1972
...
And
A B
Eva Female
John Male
Lisa Female
...
Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.
Everything is explained inside the attached WB.
View 4 Replies View Related