Combining Row Results Based On Specific Criteria
Jun 20, 2007
If I have the following table:
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
View 9 Replies
ADVERTISEMENT
Apr 18, 2014
I have a table based on transport numbers who sometimes have doubles. Example:
Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1
So it works as following:
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)
View 4 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Dec 1, 2006
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
View 9 Replies
View Related
Jun 30, 2009
Note: It should also be noted that, when "splitting" the macros below up into two different macros, they work, but then I have to play 2 macros when I only want to play one.
Just when I thought I was getting this macro stuff, I've come back to reality. I tried to combine these 2 macros together (taking out 1 "sub" and 1 "end sub") and when doing so, I got the error message
"duplicate declaration in current scope"
I thought about removing some "Dim" lines, but realized that the Dims say different things. For example in macro 1, it says "Dim Rng As Range" and in macro 2, it says "Dim Cust As Range".
Don't I need both? Do I put in an "exit sub"?
View 12 Replies
View Related
Jul 16, 2007
I need to determine the MAX date in a column based on specific criteria. Column A6:A500 = Task Names, Column N6:N500 = Dates (there are some blanks).
What I need is to determine the latest date in column N6:N500 based on which task I choose from column A6:A500.
View 9 Replies
View Related
Jul 6, 2014
I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.
screen_print.JPG
[Code] .....
View 1 Replies
View Related
Dec 21, 2011
I am getting an error i have list (sheet4) i want to lookup the data from different table (Name range) based on the location in column A i am using the small function copy down, in cell C6 i want the range ROWS($A$2:B6) go back to ROWS($A$2:B2) since the criteria in column A has changed from China (A5) to US (A6)
Sheet4ABC1Locationproductsamount2ChinaA2954.63ChinaB1565.284ChinaC3772.055ChinaD2027.76US#REF!#REF!
Array FormulasCellFormulaB2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:A2)),1)C2 =INDEX(INDIRECT(A2),SMALL(ROW(INDIRECT("2:10"))-ROW($A$2)+2,ROWS($A$2:B2)),2)B3
[Code].....
View 1 Replies
View Related
Jan 3, 2013
I'm trying to create a formula that tells me the following:
If the item is marked as closed (vs. active), then how many renewals/terminations replacements/etc are there? These are two difference columns in a worksheet.
View 2 Replies
View Related
Nov 10, 2006
I have a report which is split into page sized chunks, in column P I have an identifier Planned, Booked or Running.
Is it possible to build a macro which says something along these lines
If P1 = "RUNNING" Then
Print that Page
Else
Go to P44
If P44 = "Running" Then
Print that page
Else
Go to P87
and so on until the cell selected is blank then stop.
View 5 Replies
View Related
May 20, 2014
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies
View Related
Oct 20, 2011
how can i sum base on the Criteria in Columns G, in this sample the sum should be 67.
Sheet1ABCDEFG1V. GoodGoodFairBadN/AReported29853N/A8Bad31085328N/A410953N/A8Good510853N/A8Good61085378Good77853N/A8V. Good810853N/A8Good910753N/A8Reported108853N/A8Good11585308N/A1210653N/A8Good1367Excel 2010Worksheet FormulasCellFormulaG13=67
View 9 Replies
View Related
Apr 22, 2013
I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified
Location
Location
Location
[Code].....
I have been able to analyse the average results for the whole dataset by using the following formulas:
=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed
=ROUND(AVERAGE(D2:D4),2) Average response for medical centres
=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph
But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.
=IF(A2:C4,"camden"),COUNTIF(D2:D4),1
=COUNTIF(D2:D4),1,IF(A2:C4,"camden")
View 6 Replies
View Related
May 7, 2012
Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.
I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.
Next, in column C, I have...
Employee2's Name
Employee2's Number
SPACE
[Code]....
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
So, all I would have left in column C is
SUB TOTAL
SPACE
View 9 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Jan 9, 2011
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
Book1.xlsx
View 8 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
Nov 30, 2008
I have received a 'data dump' from a local county assessor. It is formatted for their use in displaying on their website. This particular file associates a parcel number with the legal description. However, with longer legal descriptions, they have replicated the parcel number and continued the description. The fields in the sample I have attached include (A) as the parcel number (which is common through all the files), (B) a county id, (C) the order that this specific portion of the legal description shoud be, and (D) the legal description, itself. In the sample you will see one specific parcel number that repeats itself more than 20 times, while other parcel numbers only appear once. What I hope to do is have one row for each parcel number with no replications. I hope for (A) be the parcel number, (B) be the county id, and (C) be the combined legal description. There should be approximately 275,000 unique parcel numbers.
A second option would be to move the lower continuations up, into the same row. For the example above refering to the 20 rows of legal description, I can work with 1 row and 20 columns(E through Z).
The provided coding was perfect, my problems were found to be the environment I was working. Once the data was prepared properly, the coding worked very well.
View 14 Replies
View Related
Jun 3, 2012
I would like to use VBA to combine specific sheets. I have a workbook containing about 15 sheets but want to combine 6 specific sheets. All the sheets have the exact same headers and number of columns but the number of rows are different.
I found this VBA code that works but it combines ALL of the sheets in the workbook. How to modify this to combine specific sheets. How to combine all visible sheets if that's possible.
Here is the code I found...
Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
[Code] .........
View 1 Replies
View Related
Nov 11, 2013
I want to take Book 1 & Book 2 then combine them into one new book (Book 1 & 2 combined) only if data matches. So in the this example if columns A,B,C,D match Columns H,I,J,K then append the matching row from Book2 to the end of the matching row in Book1 and then save in Book 1 & 2 combined. So if I opened Book 1 & 2 combined and then started a macro that did it automatically. i have tried several times but got know where.
Book 1 & 2 combined.xlsxBook1.xlsxBook2.xlsx
View 2 Replies
View Related
Jul 1, 2014
Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.
Code in plain english would look like this:
For EACH cell in range A1:BZ500 meeting the following criteria:
Cell value is a date
AND
Cell's date is at least a week or more in the future
AND
Cell background (Fill) = RGB color code: (R:191 G:191 B:191)
DO the following:
Call repeatingsub
View 11 Replies
View Related
Jan 31, 2009
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).
Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.
This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.
View 9 Replies
View Related
Aug 4, 2008
I am trying to write a macro that returns specific results from a longer list based on an entry in a cell.
For example if I have “Colours” typed in cell A1 in Worksheet 1 and there is a list on Worksheet 2:
Colours Black
Colours Yellow
Colours Green
Animals Dog
Animals Cat
Animals Tiger
Then I would like the blow answers returned in cell B1 on worksheet 1
Black
Yellow
Green
View 9 Replies
View Related
Mar 20, 2007
I am trying to solve a small problem that i have, I am not that good with VBA and need somehelp.
heres the situation.
Sheet 1 is filled in col.1= title, col.2= table# (ex. 1,2,...11)col3.= grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10). I will enter a title then pick a table then a grid location.
On sheet 2 also known as table 1. col.1 =grid location (ex. a-1, a-2...a-10,b-1...b-10....d-10) col. 2 = title.
I want to create a macro or function that can evaluate sheet1 and pick out when col.2=table 1 and col.3 = grid loc. A-1.... through D-10 then give me what is in col.1.
this will have to be done for 11 sheets 1 sheet for each table.
View 9 Replies
View Related
Feb 14, 2007
I have a spreadsheet that holds salary information. At the moment it shows against each person the highest salary for a person of the opposite ***. What I would like to do is have another cell that shows the names of all people who have that salary.
E.g. if the highest comparable salary for someone of the opposite *** is 30000 then I would want a list of all the people with that salary.
Is it possible to do a look up that would show all the results that match certain criteria?
View 11 Replies
View Related
Jan 8, 2008
in column A i have account name but it can be up to 4 lines (rows)
in column B i have the fund name which goes with the first account name row
so...
View 9 Replies
View Related
Apr 30, 2014
I'm trying to evaluate if 2 of the 3 criteria are met using an IF function in Excel 2010.
I have 3 columns of data. Each column has a number ranging from 0-15.
Example:
B2 = 0
C2 = 3
D2 = 4
Since B2 = 0, I'd like to have the equation return a 'Yes' because there are values greater than 0 for 2 of the three cells.
The hangup is that any of the three cells could potentially equal 0, so the equation must account for C2 or D2 being zero.
View 4 Replies
View Related
Jun 19, 2009
I have a conditional format that does not seem to be working for me. Cell B2 has a drop down optionSelect, No, Yes); Cell B3 is supposed to be conditionally formatted to return the following results if the criteria is met:
If B2 is equal to No or Yes then colour should become Yellow
If B3 is >0 then colour should become Blue
The problem is when B3 is greater than 0 it does not change the cell colour to Blue.
B3 Conditional Format #1 is =AND(ISTEXT(B2),B2<>"Select")
turn background to yellow
B3 Conditional Format #2 is =AND(ISNUMBER(B3),B3<>0)
turn background to blue
See attached for spreadsheet with conditional formats
View 3 Replies
View Related
Jul 14, 2009
I have a spreadsheet schedule that pulls quantities from another spreadsheet's cells. if the part number of the reference row matches the schedule cell's part number (row), and the date of the reference cell row is 2 workdays after that of the schedule cell's column, the quantity is put in the right place in the schedule. that part works. the problem I have is that sometimes there is more than one quantity with the same part number and date (due to the parts belonging to different jobs). when this is the case, only one of the quantities is picked up by the schedule. i want the schedule cells to sum the quantities of the reference file with matching criteria. i also don't want to have to change the reference file at all in order to do this. below is the function that works (besides summing the duplicates). the part after the " " is what i would need to alter.
HXS is the reference file where D is the part number column, I is the date column, and 5 is the index column referencing the quantity.
C is the part number column in the schedule
K is the date row in the schedule ....
View 10 Replies
View Related
Jan 11, 2009
I have been reading through trying to find a similar post but the nearest i could find was one dealing with numbers rather than text. This is how the workbook looks.
Column A contains a list of numbers based on vehicle types. Column B contains a list of vehicle names.
A B
1 Car,
1 Taxi,
2 Bus,
2 Lorry,
3 Skateboard,
3 Bike,
1 Motorcycle,
What I am looking for is the best way to combine the text from column B depending on the value of column a. the finished results would then end up as: "Car,Taxi,Motorcycle,", or "Bus,Lorry," etc. This is just a simple list.. The one I am working with has around 2500 entries. I have been trying to do this using the CONCATENATE function and IF statement but it's just not working..... My head is ready to explode
View 2 Replies
View Related
