Grouping Data From Two Tables (different Structure) Based On Common Criteria?
Dec 1, 2013
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 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.
Essentially what I want, is to type a medication in, lets say, A2, and for a macro to produce a response in B2 based off of grouping criteria from another worksheet. My workbook better explains what I would like to see happen. A macro would be best for this, right?
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).
I have created a pivot table and grouped the dates by month and year. Unfortunately I did not realise I left the auto box clicked which has inserted an end date. Is there any way I can change this easily so that I can insert more data.
I need to modify a pivot table that has dates as one of it's headings. Instead of listing each individual date, I need to group them into months. I know it can be done, but I haven't been able to figure out how to do it.
I have a weekly spreadsheet that has downcodes for components and every week I create a pivot table to break the file of downtime by truck# and downcode. There are about 80codes and they are classed into10 classes but there are weeks when some of the codes in a class won't appear so the code shouldn't show on the report. What I do on a weekly basis is create groups for the codes captured by the pivot table......Example
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.
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.
I need a VBA macro code to get the output like in the excel file which I have attached with this thread. Which means, I need to group the data for every two hours. in the output I need all 4 cols namely A, B, C, D along with extra col namely, interval based on which I need these groupings to be done..
The code has to be really flexible so that it works for all dates and times in the files. Because like this I have to do for 2000 files.....
I would like to automatically create a filestructure in c:projects based on entries in sheet1.
Column A Row 1: Admin
Column B Row 1: Meetings Row 2: Div Row 3: Presentations
Column C Row 3: Master presentations Row 4: External presentations
I have a fixed structure used for all projects, but it differs what each project is using of the structure.
Question: 1. Is it possible to loop through this sheet and create the structure in a predefined location
2. Optional I would like to use a column after/before each folder in sheet 1 where I can use ' ' for not making the folder and 'x' to create the folder.
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
I have worked out (through reading and what not) how to link a combo box to a range in a worksheet. What I need to do is relate what is selected in that combo box, to another combo box which picks from another range. I have a data spreadsheet called "Offices". It's set up as follows
(Sorry, it's really frustrating trying to format it to a table type thing but you get the gist)
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
I have a spreadsheet that consists of 981 rows and 1152 columns (192 * 6). It is essentially 981 rows by 192 columns rectangles (if we think of it as a range) side by side. What I want to be able to do is cut 981 rows by 192 columns at a time and paste at the bottom of the first rectangle so forth so on.
Essentially currently data sets look like 1 2 3 4 5 6 - I want the cut paste process to move these rectangles to:
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
What I'm trying to do is concatenate all of the URLs into one cell so that each email address has only 1 single row with many URLs associated with it. I tried using VLOOKUP(B1, $A$1:$B$4000, 1, FALSE) but this didn't work. I thought it was a long shot anyway. I also tried writing a bash script do parse a csv with this data and create a csv file with the URLs concatenated with no luck. I'm trying to get my data to look like this:
EDIT: The format didn't turn out like I'd hoped. In the first example, each URL has an associated email address, each in its own row. In the second, it is just one row, with multiple urls in the first cell and an email address in the second cell.
I have two worksheets. One has columns A B C , the second has columns A B C D. Columns A in the two worksheets reflect the same data (serial number) , although one worksheet could have more rows than the other since it gets updated manually daily. I would like to use a macro that would:
1. use columns A's value in worksheet 1 to match a row in worksheet 2 then merge columns from worksheet 1 & 2 and insert into a new worksheet 2. repreat until all rows in worksheet 1 are read 3. save worksheet 3
I'm building a sales tracking spreadsheet and need to find a way to auto group certain rows based on their cell value. In my tracker I'd like to group by company name and be able to collapse it to look at the details.
Here's an example of my starting point and how I'd like the data to look like in the end.
Starting State: Company Name contact name sales stage Forcast amount ---------------------------------------------------------------------------------------------- Company A david brown contracts 10,000 Company A John Smith Won 20,000 Company A D'Arcy Davis Canceled 10,000 Company B Company B
Desired State: Starting State: Company Name contact name sales stage Forcast amount ---------------------------------------------------------------------------------------------- +Company A 30,000 +Company B
I have situation where my account (from trail balance) will present to different grouping based on reporting, example below. How I can formulaize or using VBA to sum to amount based on the format.
Example :
if i want the format b : if i sumif HCM - the total should be from account code a1001,a1002,a1003 and hrd will sum up based from account code a1004,a1005,a1006
Account Code format a Format b format c format d format e
I am working with a large data set containing information about certain items
The items are structured so that they all have seven main sections. Within each main section their is a variable number of parts, that the items are made of.
The items uses only one part from each main section. So all items consists of seven (identical) main sections and seven parts.
I am looking to group items togheter which are precisely identical. I mean the items that uses the same part in all the main sections.
Is there an Excel/math wizard out there? I have all the data, but I can't make Excel do this task for me..
Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
Capture.PNG
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on. - I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
I would like to sync cells together that contain common words for sorting purposes is this possible? For instance i have a [URL] ..... in column A row 1 and In column B row 2 i have the word bellmont i need to get the rows to sync so rows containing common words line up. I have 8,000 rows to sync?
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?
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.
A is Date B is Room Number C is Location D,E,F all have numbers results in them. G is the average of D,E,F for each person H is group average.
If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?
So For Example (Columns DEF left out for clarity)
Date Rm Location G Inidivual Average
[Code]....
So because room number changed it calculated as a different group. Is this possible?
i need to group the values based on A, B, C columns.
which means all "Chickpea_B-1_11area_3d.csv " will have same timestamp and mean_area_3d values, only D column vlaue changes.....
in the same way, all "Chickpea_B-1_12area_3d.csv" will have same timestamp and mean_area_3d values, only D column vlaue changes.....
now i need all Chickpea_B-1_11area_3d.csv values to be grouped into one. and chickpea_B-1_12area_3d.csv values to be grouped into one..
if you see the output sheet, you will understand clearly, which i did manually for your easy understanding...
i did manually and also attached the sheet for your kind reference and easy understanding... where i grouped all Chickpea_B-1_11_area_3daver_domain_area, fill_factor, leaf_angle_theta, plantHeight, projected_leaf_a into one.... where b and c cols are same for all the D E F G H I Values. in the same way for Chickpea_B-1_12_area_3d and all other below values to be grouped.
like this i have to do for 1000 files. please find the attachment...
I have a pivot table (vendor spend report) which has over 7000 rows of data. There are 38 vendors (some vendors appear multiple times i.e. Accenture Itay, Accenture Germany, etc.). Vendors names are in column B.
I need to have the VBA script search for each of the vendors (and related names) and group them together. Also, last one is for all the MISC. vendors which need to be group together (one's that dont belong to the 38 managed vendors).
Also, I need to rename the Group for each one; rather than Group1, I need to name it "Accenture All" etc...
