Aggregate Number Of Good And Bad Results Per User On Different Sheet
Jul 4, 2014
I have a database file where users name can be located in 2 neighbor columns. Against each user name (in next 2 cells on the right) there is a evaluation (good or bad).That database will be growing and new additions will be added each month. That means one user can appear several times on a different row with different evaluation.
In the same file I have a separate sheet where I need after running a macros to have aggregated list of users (in one column) and against each user I would like to see how many how many good and bad score he have. It will be good if users are sorted in alphabetical order.
I run a golf society and need to set up a golf aggregate sheet that will allow me to enter the players weekly scores and the sheet will then look for the lowest score entered for each player and replace it with their score of this week.
I have a book with 250 worksheets, with one cell of data per sheet. It is the same cell in every sheet (C7). I need to somehow aggregate all of this into one column on the first sheet, 250 rows.
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
I would like to create a user form that will display the results from a lookup formula. The userform would have 2 textboxes, so from the formula below BZ109 would be textbox1 and CA109 textbox2. Once data is entered in these textboxes you would hit submit to return the results in a message box. What would the code be for the sumbit button?
i have 10 columns, the last one is an amount. What i'm looking for is to aggregate the rows with the same key(columns 1 to 9) just into one and get the summation of the related amounts in the column 10.
Example:
row1: a b c d e f g h i j 100 row2: x x x x x x x x x x 100 row3: a b c d e f g h i j 100 row4: a b c d e f g h i j 100
the desired output is:
row1: a b c d e f g h i j 300 row2: x x x x x x x x x x 100
I would like to build on my Excel VBA but not sure where to start. P.s I want to aim towards reporting side of things so make reports look good, copy data from sheets onto another sheet, formatting all done through vba, macros
Just wondering the better way to 'get' a score for some data.
For example, I have a data set where there are a number of records are interrogated for validity across X rules. The returned count of errors of course could include one record for all X tests.....
what is the better way of weighting these to get one score
To go through all the database files (.txt format) in a designated folder in order to update an Excel Sheet with the required information from those database text files. Then to summarise the information in a simple table.
The text files are individual Market Exports from a game I play, and are titled as "Region-ItemType-YYYY.MM.DD HHMMSS.txt"
eg. "Heimatar-Tritanium-2009.07.02 133353.txt".
I need to know how to sift through the text files to find the most recent one with matching "Region-ItemType" parts in the file name, and refresh the Excel Sheet with the updated data.
After that, I need to be able to combine all of the cheapest sale prices for each item, with a minimum amount for sale (differs per item), while also being limited to location the item is being sold at, into a single sheet of the important information I wanted to extract in the first place.
At the end of the whole process, I need to have a single Excel Sheet with every different item's lowest price for each different place, displayed in a table I can use to make formulas from.
So if I have an export from a place called "place1" and one from "place2", looking at the same item called "item1", I should have two rows of tabulated data, one for each place's lowest price. And this should update at the touch of the "Refresh All" button, or... However you'd have to do it
I have attached a copy of one of the database text files.
Aggre ABC1Brand Sales 2A5 3B 2 4C7 5A1 6D1 7 8Above are my data 9 10Brand Sales 11A6 12B 2 13C7 14 15Above is the result I want. 16 17 Excel tables to the web >> Excel Jeanie HTML 4
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80 and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be Outage %age= 50/100.80 Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
Cell A1 can have only 0 or 1. Cell B1 can have any numeric value. Cell C1 takes the value of B1 if A1 is 1, otherwise it is 0. So, C1's formula can be either "=A1*B1" or "=If(A1=0,0,B1)". Lets say this formula is continued down to the 65000th row. My question is which formula is faster? The first one or the second one?
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.
I have column E listing all of the favorites and column G the underdogs, each row represents one game, there are 34 all together. I want a formula to pick the team based on a random generation. That part i have....
=choose(randbetween(1,2),e7,g7)
but here is the rub.
I only want the total number of underdogs to be 20% and the favorites to be 80% of all of the picks.
I am not sure it can be done as each formula must look to what the other formulas are doing...aside from it being circular i think it just impossible or i am not as advance as some of you.
I have an excel document displaying graphs that i would like to link to from a menu page (via a number of buttons). The problem is that i have about 12 of these graph sheets (along with 8 other sheets) and i'd rather not have them cluttering up the worksheet tabs list at the bottom of the screen.
Does anyone have any suggestions about how to handle this? Is a macro to open a hidden sheet and then close it once it is navigated away from a reasonable option? Or does Excel protocol suggest that they should remain visible, despite their cluttering..
I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.
The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.
how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?
Imagine that A and C are basically the same. So the value that I really care about it (10 + 18) = 28 B and D are the same, so I care about (16+20) = 36
The output I need is:
A&C - 28 B&D - 36 E --- 16
Can I use SUMIF to basically say: Sum this IF =A OR or =B ?
Can I apply an IF function with concatenate to merge the A's with C's and B'd with D's?
The spreadsheet is large and this process will apply to many different groups of brands.
I have a list of employees and their e-mail addresses. There is also a column denoting whether they should be part of an email distribution list or not (if yes, denoted by an asterisk).
I need to aggregate all of the asterisked e-mail addresses in one cell. It also needs to be "active", i.e. having an IF statement for whether or not it has the asterisk and therefore should be included. Last requirement: the addresses need to be separated by a semi-column, then space (for easy copy and paste into Excel) like this:
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.
In creating a chart that calculates home prices I need to be able to just enter "Good", "Fixer", "Remodeled", etc...and have that automatically use one of the numbers that I have set and add it to my total. I have created a box with variables for "Good", "Fixer", "Remodeled", etc...How do I make it so I can put any of those variables into the condition of the house row and make it add the dollar amount specified into the totals box? This is the very simple code that I am using to find the totals so far: =SUM(C5:J5)
Here's what I need:
Also, the plug in values in the first picture WILL CHANGE and I don't want to have to change the formula. So I need something to point to those cells.
I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.
Probably missing something obvious here but I'm trying to determine the number of different results in column B where the citeria in column A is the same. For example, for the attached table the result for V00013 would be 4.
i have this file that has about 12000 rows of numbers (a individual number can appear more than one). i'm trying to use a lookup to find if that number appears and if it does then bring back the amount next to the number.. however because the vlookup sees that the number more than one in the list it will bring back #N/A every time. Is there a way to get around this??
I want to flush N Number of Rows by giving a Number in a cell. For example, in a Template, viz., loan amortization Calculator, provided by Microsoft, the number of Rows is restricted to the number shown against "Number of Months" and all the rows flushed remain filled with the result exepcted.
I am using a spreadsheet to determine if students are meeting Growth Targets between a pre test and post test.
Here is my formula
=If(D8>=C8,"yes","no")
D8 is the Growth Target and C8 is the Post test Score.
After doing this, I need to calculate the percentage of Yes results. This year it will be easy, as it only applies to 25 students. Count the yes's and multiply by 4. Next year, it will apply to number in the neighborhood of 150 students.
