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 RepliesI am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
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.
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))
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View RelatedI'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
how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
My store uses a retail price "code" wherein a price like 99 in expressed as a
code like "PP"--exchanging the number digits 1-0 as letters.
Is there a function to do this automatically?
In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
View 2 Replies View RelatedI 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.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
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
I have multiple names in each of 10 groups. Each name will have a number beside it. I wish to have a separate worksheet that will contain only the name with the highest number beside it, from each group.
View 5 Replies View RelatedI have an Excel sheet with one column (column A) and I have 503 000 data rows. In the first 503 rows I will need to insert the data group1. The next 503 rows, I will need to insert the data group2. The data always increment by 1 every 503 rows.
I could do it manually for few groups, but I need to create 1000 groups (503 000 rows). So you can imagine the load of work if I would do that manually.
Does anyone can explain me how to create a macro or anything that could automate this?
Im using Excel 2007 that support more than 1 million rows vs the old Excel versions which is a max of 64 000 rows
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.
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 Relateduses 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,
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?
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?
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?
I have the following ....
View 14 Replies View RelatedAdd Project to 'database' I recorded my marco.
View 3 Replies View RelatedI'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
I am trying to find functions that allow me to categorise game attendance by team and day. EG: Out of the list, I would like to summarise the min, average and max attendance for the Broncos on Fri, Sat, Sun and Mon games (and for each team)....
View 9 Replies View Relatedin summarizing my data set. specifically, I have names of organizations listed in once column and in one of the others I have status of organization (open, closed), number of transactions and volume of transaction by months for each organization. In a summary sheet I need a formula that would look up for a specific organization in the long data base and match it with its status and count the number of "opened"(or "closed") organizations matching that specific name as well as sum the total volume of transactions for that specific organization. If I stated my request unclearly i can explain it better or attach a excel sheet.
View 9 Replies View RelatedI am working on a database for a client. The database has a few worksheets with an overview (contents) worksheet. What I am trying to do is create a hyperlink from the contents page to the corresponding worksheet. I can do this no problem...my problem is that if I add more data to the worksheet the hyperlink is lost. I am linking the sheets via cells so if a cell is moved down to add a new cell the hyperlink is no longer valid. Is there a rule I can create to keep the hyperlink and not have to adjust it every time new data is added.
View 9 Replies View RelatedI am trying to set up a database to collect information on students behavior. We need to collect the frequency of 3 things-time of day, behavior, and correction. Can I do this in Excel? How? Is there a way to write a correlation that will count the occurrence of words?
View 9 Replies View RelatedHas anyone ever tried to make a database out of Excel? I know that Access would be a better choice, but i have never done anything in Access and have no clue where to start. i know Excel, but i'm worrying that i will end up having too much data and my program will be slow.
View 9 Replies View RelatedI am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.
View 9 Replies View Related