My columns are labeled A B C D etc... If the product I am creating the table for falls into one of those categories I place a 1 in the cell. For instance, ProductA falls into the A and B categories, so column A will get a 1 and column B will get a 1. This is so I can sort all of category A's Products etc...
I am looking for a formula to put in a column that would Say what categories ProductA falls into. ie Since ProductA falls into the A and B catergories but not the C and D catergories, This final column would say something like AB, or A,B or something.
Alternatively, I could make a single column entiltled 'categories' and put something like 'A B' in ProductA's column. In this case I would need a way to sort all A's or sort all B's and not just sort all 'A B'
I am trying to consolidate an .835 raw text file that is full of receipts and payments into rows by specific values/characters. Once opened within notepad/text format, I figured out that the ~CLP is the referencing value/character used to break up each individual payment.
I need to somehow use the ~CLP as the value(s) to sort the text into new rows, with each row pre-leading with the ~CLP.
I am working on a Golf League score sheet, and each week there may be a different amount of players that play. I have a weekly worksheet (attached) that contains the full list of golfers that had played that week and I only want to sort the columns for those golfers who have a score greater than 0 (this would be the ones who played that week). You can see that in week 1 I had more players than in week 2. So in my weekly worksheet I sort these sub grids by Score first. However I ONLY want to sort the rows that have a score greater than 0. Can this be done in VBA or am I stuck doing this manually every week?
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB: Private Sub Worksheet_Change(ByVal Target As Range) Dim X As Long, FillCT As Long For X = 1 To 4 If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
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 have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000 Jung Joe $700 White Jon $100 Jones Jim $200 Jones Jan $300 Jung Joe $800 White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount Jung Joe $800 Jung Joe $700 Jones Jim $1000 Jones Jim $200 Jones Jan $300 White Jon $200 White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
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 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 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
