I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
Store 1 1111 16:40 2222 13:00 Store 2 1111 15:05 3333 16:50
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
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")
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.
Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).
I am trying to loop through a column and cut and copy cells containing one of ten or more criteria (text strings) to the next worksheet, which already exists.
The basic loop is set up, and I understand that an array for the multiple criteria is needed but am falling at this hurdle, as well as struggling with the routine to copy the row to the next available row on the next worksheet.
I have omitted to post my effort so far, as it's embarrassingly basic, but can anyone help with the code?
Example attached. I need to filter rows based on a start date and stop date, columns C and D. So for example the filter date is 01Mar07 (located in A5). As this date in this cell is changed the rows are filtered accordingly. I need to filter rows so that any row with a start date which includes Mar 07 is shown and I need to include all rows that have an end date in Mar 07. This would result in the inclusion of an event that started in Feb and Ends in march being displayed. Additionally, I would need to clear the filter. I'm just starting out, I'm sure this is easy for you all the excel experts., and you may probably have a better method to approach this.
Option Explicit Dim lastrow As Long, t As Long Sub Method() lastrow = ActiveSheet.UsedRange.Rows.Count For t = lastrow To 1 Step -1 If Cells(t, 8).Value <> "" Then If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _ 6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20 End If Next t End Sub
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
I'm trying to create a formula (used in sheet 2 column B) that would generate the results in sheet2.B based on the contents of sheet 1. sheet2.B2 would contain a formula searching for the both "Y" in sheet1.C and the text in sheet2.A2 (in this case "E&P"). When each match is found, the contents of sheet1.A should be returned to sheet2.B, as shown in the mockup. There will be multiple matches (at least 15-20) for each search criteria, and I don't know how many there will be ahead of time.
I've tried various formulas, and they either have incorrect syntax and return every row in a range, or only return the first match correct match each time instead of all correct matches. I found some UDFs posted online that claim to do this, but I've gotten them into the spreadsheet and they generate # NUM errors. I would prefer to work with Excel-native formulas if possible, because I'm going to eventually hand this off to someone else to maintain and they may not have the level of expertise to deal with UDFs.
I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.
I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....
Name ProductSize QuantityPrice per unit AX35510 AX451015 AX551210 BX601010 AX35510
I now want a resulting table that will combine all rows where name, produc, size and price are identical and have one row with the same data apart from the quantity that will have been added - so for the table above the resulting table will be:
Name ProductSize QuantityPrice per unit AX351010 AX451015 AX551210 BX601010
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program Clinic Title Start Date
formula that will automatically give me that answer?
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating Ford Bronco 64 Chevy Corvette 94 Dodge Intrepid 83 Chevy Chevette 34 Dodge Viper 72 Ford Escape 21 Ford Expidition 53 Chevy Impala 67 Ford Fairmont 11 Dodge Dart 33
I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to
A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula).
B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting).
C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st
D) delete the copied rows from "current".
e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size.
I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work.
Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.
I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.
So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5?
I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
A B C D E F __________________________________ 1 ROOM: 101 2 3 4 101 XX 1.2 0 0 P 16 101 YA 0 0 1.1 L 23 101 JJ 3.2 2.1 0 L 55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.