I am working on a spreadsheet that has three columns.
A - Product (stays the same no changes)
B - Amount Ordered (varies)
C - Purchased By (varies)
My goal is to set up a formula that shows a summary of how much a person has ordered. Sounds straight forward, but the problem I am running into is when 2 or more people are making a combined purchase. In those cases the cost will be divided equally between the buyers. Is there a way to achieve this goal?
I included a sample of the spreadsheet. Typically this is about a 300-400 line report.
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria: First Letter of the Unique ID in column "E" - O, M, or L Program Type in Column "F" - U or R 1. O-U = U 2. O-R = RU 3. M-U = U2 4. M-R = R2U 5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB: Option Explicit Sub test() Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long Set dic = CreateObject("Scripting.Dictionary")
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
I’ve got a folder on drive C named “Customer Sheets”. In this folder at any time there could be over 500 individual customer sheets. i also have a "Summary Workbook" on drive C
What I need is a macro in my "Summary Workbook" that copies cells (“B3, B6, B12, B13, G5, G6”) form every customer sheet in my “Customers Sheets” folder and paste it into my “Summary Workbook” onto sheet2, every customer in a new row.
The “Summary Workbook” should run this macro every time it is opened or on my command so that the database is continually refreshed when a new customer sheet is made.
I need to match cells e3:h3 whenver the dates change in in cells c5:c9 and then put there result of cells b5:b9 into the corresponding cells of e5:h9.
Basically what I'm saying is that I want to keep a track of all previous pay amounts from each pay period. So when the next period changes the date it also copies the new pay amount to the corresponding date of the previous pay section.
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
At work I have a spread sheet that I used to track material shortages by part number. So in column A of the spread sheet there is a list of part numbers that have shortages, column E contains a list of all sales orders that are affected by the shortage separated by a comma. I am trying to setup a query sheet where I input a sales order and get back a list of parts that are short for that sales order(basically reversing the original list to be by sales order instead of part number). The number of values in column E varies, sometimes a cell will have 1 value, sometimes 20+ and anywhere in between.
Example Sheet: A B C D E
123
012
234
789, 567
465
789
890
012
I'm already got a INDEX/MATCH that would show both shortages for sales order 012. But I can not figure out how to get the shortages for 789 or 567.
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
I have a table in which you can select values from a pre-defined list. However on some days I need to be able to select multiple values from the list and not just one value. Is it possible to do this or if not is it possible that on the days I need to select multiple values I can get excel to auotmatically insert a line to enable me to select the second, third etc value without having to physically insert the extra line?
I am having trouble writing a code for something I need to do. There is a workbook with a worksheet for each month. At the start of every month a new worksheet is added. The opening data in this new worksheet is coming from the closing data of previous month, and some of this data is modified as the month passes.
So, in a way, some of the data for each month depends on the data of previous months. If there was an error in one of the cells, for say, January, and it was corrected manually, it will affect the cell values for feb, march, and so on. Currently, its all a manual process - from copying previous month's data to a new worksheet, and manually correcting errors in each worksheet, which is error -prone and a time consuming process. Is there any way to link each month's data and create an "update values" button clicking on which will update the values for cells in every month's worksheet following the one which had errors in it and was manually updated. It has to one directional.
I have a spreadsheet with data from a survey. There are 375 respondents (rows) and 26 questions (columns). I use COUNTIF in order to figure out the frequency of responses and SUMPRODUCT to do some finer analysis. Here's my problem, some of the questions have multiple valid responses. In those cases the data was entered into the same cell. So I have a column that looks like this:
1,4 2 1 3,12 1,11 8, 3,14,9
I can't figure out how to count the number of "1"s. It would be much easier if the responses could only be 1-9, but unfortunately they can be 1-20. So far, I've come up with:
I have a userform with a variety of checkboxes, textboxes etc which populate their data to cells on the worksheet.I know how to do that and it works fine, but now I need to populate values from multiple items to 1 cell.
For example, if the user selects 3 checkboxes then their respective values are copied across as 3 values in 1 cell rather than one writing over the others.
I'm working on a project for use by our sales teams to for them to easily find out which campaigns are currently being offered in their regions. The data looks like this:
Participating Regions
Campaign
IN, OH, MI, IL, NE, AZ
Campaign A
IN, OH, MI, IL, NE, AZ
Campaign B
IL, IN
Campaign C
IL, IN
Campaign D
All
Campaign E
IL, WI, MN
Campaign F
This is going to be embedded internally and needs to be as simple to use as possible. I would like for column A to be filterable by state, either with a drop down menu or a selector. My problem is that many states could be in one cell, or individually in a different cell, and the default autofilters recognize these as two different values. I would like the sales rep to be able to filter "IN" for example and get 5 results. One solution that I am aware of is for me to duplicate each campaign on a different row and list one state per row for that campaign. The issue with that is that this is going to be updated frequently and that is a tedious manual process. I am also aware that you can use a text filter for "contains" specific words, but even that is more intricate than what I am aiming for.
Say i wanted to track the changes in a cell, i.e. if a number changes in a cell it records the new number in a new column. The following macro achieves this for one value; say type a value in a15, it records the number changes in column D;
Private Sub Worksheet_Change(ByVal Target As Range) Dim DestCell As Range With Target If .Cells.Count > 1 Then Exit Sub If Intersect(.Cells, Me.Range("a15")) Is Nothing Then Exit Sub If IsEmpty(.Value) Then Exit Sub If IsError(.Value) Then Exit Sub
[code]...
But what if need to track the value of 4 cell changes in different columns, and record these values into different colums of their own? So for example, if i had a value in a1, and wanted to paste the changes into D; if i had a value in a2, and wanted to paste the changes into E; if i had a value in b7, and wanted to paste/record the changes into F etc etc.
I thought about running the macro for each cell but it wont work, for example i tried;
Private Sub Worksheet_Change(ByVal Target As Range) Dim DestCell As Range With Target If .Cells.Count > 1 Then Exit Sub If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub If IsEmpty(.Value) Then Exit Sub If IsError(.Value) Then Exit Sub
I would like to be able to add a checkbox linked to each sum cell that adds the values of each of those cells.
Ex: A5=4, B5=6, C5=1, D5=7. With a checkbox linked to each cell, if i check the boxes in A5 and C5 then in another cell i would get the sum of those two cells= 5.
I've tried this but every time i link a checkbox to the sum cell it deletes the formula and puts in a True or False result.
I am running a 8 ball pool league and I am using a excel file to keep all the results in. We have over 200 people so the list is quite long.
Here is my problem. I have a list of 200 player with columns from left to right stating which days they are playing on. In each of these columns I note if that player won 3-0, 2-1 or lost 0-3, 1-2. The 3 and 0 or 2 and 1 are in separate columns. I am trying to write a formula to look for the players who have won 3-0 the most times to show how consistently well he has played.
I have tried =COUNTIF(A2:Z2,3) and this works fine, but the problem is, is that it counts the 0-3 that he lost as well, I only want his 3-0 win. His win score is always the first column so if he wins 3-0 I want it to count all the 3-0 wins and leave the 2-1 win or the 0-3 and 1-2 losses.
I am looking to find a cell value on a spreadsheet that has cells with multiple values listed in it. For example. If I CTR F for i.e. (745) on a spreadsheet that has this value in a cell that contains i.e. (684, 362, 745, ABC) all in one cell I get a no results. Ultimatelly, I'd like to run a Vlookup function on this file.
I'm trying to validate the data entered into a series of cells each cell can contain a different set of data but the value N/A is also permitted. For example:
Cell A1 could contain a date from 2013-01-01 thru 2013-12-01 but the value N/A is also valid Cell A2 could contain a decimal from 0.01 thru 302502.23 but the value N/A is also valid Cell A3 could contain an integer from 3 thru to 9000 but the value N/A is also valid
When the acceptable values are entered then I want to be able to carry on otherwise I want to pop up with an error.
I am wanting to use a vlookup or perhaps an array(?) to bring through multiple cell values based on being the maximum value. So say there are multiple employees (all with unique ids) under one manager (also with unique ID), I want to find the employee with the highest value in a particular column and return that employees details, such as name, employee number and the value itself, which are all in the same row. I have attached a basic example of the data involved.
I need to separate multiple values separated by comma in a single cell into individual rows. However, I also need that the data in the adjacent columns of the original cell also to be repeated in the new rows. The problem is illustrated in the attached file.
that the table is just a sample and most of the tables I work with contain at least 15,000 rows,
