Sum Large Database Using Criteria
Jan 4, 2013
I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.
In a new tab I want to formulate the total of ProductA on day 240.
A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5
In another Tab summary of Product Totals:
Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15
I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)
View 9 Replies
ADVERTISEMENT
Aug 30, 2009
The format of the numbers are:
####-##-###-####
I have 2000+ numbers is coloumn B.
There are a lot of sets of numbers that are still text! It's highly annoying and I need to analyze the data in Access.
I tried the paste special advice and the 3 ways microsoft suggests. I have a feeling its not working because of the required format that I need.
I need to do this project tonight!
Background: these are imported numbers, I had to combine other excel databases... By the way, is their an easier way to combine excel sheets into 1?
View 12 Replies
View Related
Aug 24, 2014
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx‎
View 5 Replies
View Related
Jul 18, 2014
i have database where are some data for several shops i need macro which will copy data for exactly that shop which i will choose in summary sheet, for better understanding i also attached excel file.
View 1 Replies
View Related
Aug 20, 2009
when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.
The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments
each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2
then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2
then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
[url]
I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label
and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.
YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.
Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website?
View 9 Replies
View Related
Jan 22, 2007
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.
View 11 Replies
View Related
May 29, 2009
I do have an excel workbook containing two sheets:
Sheet1 contains a list of 30 criteria, every one with its own check box (TRUE/FALSE).
Sheet2 contains a database of items, which columns contain the 30 criteria mentioned above. (So sheet1.criteria1 can be the only value of sheet2.column1)
I am trying to program a macro, which can be assigned to kind of a "submit"-button in the first sheet. Its purpose should be to open a new third sheet and listing all the items from sheet2, which contain values checked=TRUE in sheet1.
View 10 Replies
View Related
Oct 2, 2007
Cell A1 is HEAT_CODE
Cell A2 is W5H
In cell
=DMIN(Database,"Cost",A1:A2) return the proper value
I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)
the above returns #VALUE! error.
View 6 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Feb 1, 2007
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
Nov 7, 2008
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
View 9 Replies
View Related
Aug 6, 2003
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
View 9 Replies
View Related
Oct 17, 2013
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
View 5 Replies
View Related
Jan 20, 2009
I use Microsoft office 2008 for MAC i currently have 4 xls witch contains clients
The one has 11.206 the other 11.971 and the other 4267 people
THe 4th and last list has 3462 and i need to check witch of the people in 3462 are not contained in the other 3 xls
I tried to copy - paste some info in order to be replaced by excel but nothing
View 9 Replies
View Related
Apr 29, 2014
I have 5 sheets all with different data within the H column. I want to find the top 10 highest results across all 5 sheets and then pull the worksheet name where those results come from. It would be two separate formulas, one for the LARGE value and the second for the name.
This is all I have.
O2:O11
=LARGE(Sheet1:Sheet5!$H$3:$H$102,1)
to
=LARGE(Sheet1:Sheet5!$H$3:$H$102,10)
I then have this which was the best I could find after about 30 different formulas.
=MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}&"'!$H$3:$H$102"),O2)>0,0)
It gives me the right worksheet but it only gives me a number. I'm assuming it's because of COUNTIF but I'm not sure what else to use.
Most examples I see have the names they want right beside the values. So in my case H3 is 22 (and the largest value within H across all sheets) and if I followed their examples G3 would be Sheet1. So because it's side by side it'd be easily pulled. But the only name references I have are the worksheet name itself or A1 of Sheet1:Sheet5 so I'm not sure how to rework their formulas to suit mine.
View 4 Replies
View Related
May 1, 2009
I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):
I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this?
View 12 Replies
View Related
Jul 14, 2009
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
View 2 Replies
View Related
Sep 4, 2009
I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?
View 2 Replies
View Related
Oct 7, 2006
I am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?
View 4 Replies
View Related
Apr 2, 2009
I've been browsing through postings for a while now trying to figure out a solution to my excel formula problem. While there is a lot of useful info, I have not found anything that will get me where I need to be. I am hoping that someone here will be able to me out. My project is this.
-A workbook with about 20 sheets.
-Sheet one will be a master list of serial numbers with blank fields for dates, account numbers etc.
-Sheets 2-20 will be used to assign those serial numbers to individuals.
-On each individual's sheet there are fields for dates, account numbers etc. next to the serial number.
-I need the master sheet to look through the individual sheets and insert the date, account numbers etc. into the master sheet.
I can get the IF function to work with one sheet but cant figure out how to do this across multiple.
View 4 Replies
View Related
Apr 4, 2009
I want to set up a database which contains customer comments and phone numbers, what I want to do is every 30 days or so, have the cells highlighted so that they remind me to call them.
View 4 Replies
View Related
Feb 6, 2010
uses CSV files for importing product data, problem is both have it formatted differently. What i ideally need is a master file that i can change whenever a price needs changing or product adding, this feeds down into 2 csv files that are formatted for the relevent softwares and then i can use a macro to upload these automaticly at end of day. ive tried using just excel but had no luck, i tried using access and custom reports but keeping getting errors as page size is 35 fields long and doesnt want to export as a csv,
View 10 Replies
View Related
Dec 12, 2012
Using an existing database with nonfixed boundaries I am creating a new one with uniform boundaries.
The simple spreadsheet I will use in this comment isn't what the database actually looks like but represents what I am trying to achieve.
StartFinishAssigned #
0151
15252
25403
40552
55602
60804
80953
951002
Here I have the old data, organised into sections that have the same assigned number. (Note: the assigned number may be the same as the previous cell). I wish to change it into this structure:
StartFinish#
020
2040
4060
6080
80100
It will be organised by the start and finish instead of by the assigned number. Instead I would like the number to reflect the lowest assigned number that occupies a part of that area.Is it possible to create a forumla which would achieve this?
View 7 Replies
View Related
Oct 12, 2013
I have spreadsheets supplied by clubs to add data to a database. I am not a spreadsheet user,
Today I have a spreadsheet with the columns A to M repeated in the width of the spreadsheet.
How do I reduce that to one display?
View 7 Replies
View Related
May 11, 2014
From the data on my database I want a button to export certain columns (B,C,D):
1. that on the (column J) have number smaller than (cell A1)
2. and create PDF's based on a column that has 3 options (AA, BB, CC)
So all the data from database that make 1. true to be exported on 3 PDFs where the first PDF has all the AA data the second all the BB data etc..
Is this possible?
View 1 Replies
View Related
Jul 9, 2007
I have a big database that has different name in column A(something like Y1, Y2, Y3, Y4, Y5, Y6, Y7, Y8...). What i'm trying to do is to group them! In group A i have Y1,Y7, in group B i have Y2,Y3, in group C i have Y4, Y6 and etc! I think the solution is something like index...but i never worked with that in VBA! So each name of group A should copy on a new sheet, the next rows should come group B and etc! Is there a way to do that in VBA?
View 11 Replies
View Related
Nov 16, 2008
I have the following ....
View 14 Replies
View Related
Mar 9, 2009
Add Project to 'database' I recorded my marco.
View 3 Replies
View Related
May 12, 2007
I'm generally trying to figure out in my head most of the pluses and minuses for using Excel Worksheets as a database. In My case it means adding data from a UserForm to Excel cells and upon these data to analyze the info from the worksheets to other worksheets like charts, tables etc. Here's my view:
Pluses:
1. VBA and Excel integrate well as a whole, easy to use, well know interface
2. There're many available options to manipulate with the database once it's arranged properly in the excel worksheets
3. Don't need to give extra money software like Microsoft SQL etc.
Minuses:
1. Slower operating because it takes some more memory and processing time to reference the data in the cells. Here I mean operations between the UserForm(s) and the Worksheet(s)
2. You can have DB not bigger than 256*65000
3. XLS files are generally much bigger as a size -> slower work as a whole
4. Maybe (i'm not sure here) there're less tools and options than a real SQL software
What options I have:
1. Use some SQL DB which is free
2. Use Text Files to write and read from
View 9 Replies
View Related
