Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 RepliesI have two sets of data for students. One set contains all the students with certain test scores, taking up columns A to N. The other set contains about 80% of those same students with a different set of test scores. Students are sorted by ID numbers. How do I combine the second set into columns O to V so that student ID numbers match and it inputs the rest of the student data into the sheet.
I know there will be some blanks because not all students will be there but I need them to match up, even with the blanks.
I created a macro that extracts two columns of numbers from one workbook and need to match them to another set of numbers in another workbook. For instance i have numbers like...
18314 907
18272 64
11005 907
11005 324
..Now i need to match these numbers up with the others to find the hyperlink associated with them.
I am trying to merge my boss's contacts. He has one enormous set of contacts in GMail (5000+) and one enormous set of contacts in Excel (5000+) I've exported both sets into excel but how to I merge them?
The headers for each set are in a slightly different order (Home Address, Office Address, Email ...vs.....Office Address, Home Address, Email) Do I need to make sure the order of the headers match in both the GMail Sheet and the Outlook Sheet and is there a faster way to do that than just rearranging them manually?
My second question is how do I merge them? There are a lot of over-laps of people but some info might be different in Outlook than in GMail. For example I might have John Smith as a contact in both Gmail and Outlook but two different email addresses for him under the header "Email 1" how do I make sure one email doesn't eliminate the other? I just want to merge both sets of contact info for each person into one super contact.
I'm attempting to create a mailing list for the company I work for, but the software that has the data record is very limiting. The two tables I received from the program are listed:
Customer
Number of Sales
name 1
#
name 2
#
name 3
I tried to illustrate that the address list has a different customer composition than the sales list; the address list has customers that the sales list doesn't have. This means I can't just sort alphabetically and drag the addresses over. My goal here is to create a datasheet that incorporates both the address and number of sales so that we can send the top 500 customers a mailing. How can I do this?
I have 3 seperate tabs of data that i want to put into a combined sheet, how would i do this, i assume it's just like a copy/paste exercise but not sure how to do this using VBA?
My tab names are;
FND Data
UL Data
Life data
I just need the entire range copying from each tab into the 'Combined' tab
I have an requirement that retrieving the data from two different data bases. I want to compare those two and prepare a report using VBA. I am able to get the data to excel separately, but now I need to join these two sets based on the key columns
View 2 Replies View RelatedWe have customers who order items in a specific order (Product X first, Product Y second, Product Z, etc...)
As it stands, no one orders in the same way and we want to try to standardize this process.
I have a list of about 600 products, such as : Product XProduct YProduct ZProduct 300...
I need to know the top 10 products ordered together. (the idea is that this variable will be changeable, and I can see the top 11, 12, or even more)
My idea is to generate a list of arrays in a new sheet with 10 products in them from the list; ie {Product A, Product B, Product C...Product K}, (noting to alphabetize the array entries and then remove duplicates form the row)
With this list, I will use each entry to check with each customer if they're order set array equals this list (again, alphabitized and duplicates removed to ensure comparison).
My question:Is this the most optimal way to do this? ... I am not even sure it would get me where I want to be. As it seems like an amazing amount of coding and formulas; but through my searching I haven't found this unique example.How can I generate my unique, alphabetized arrays for comparison (ie: 10x10, 9x9, 8x8...)
In the attached workbook, I have identical sets of data in column A separated by an blank cells. I need a macro for user form that searches for an item indicated in the textbox of the form and then delete the whole row in all sets of data.
It is important to keep the blank cells between the sets constant, 4 blank cells between each set, except the first set that starts with 3 blank cells.
I have 1 original and 1 new IF statements, but need to combine into 1. Is this possible?
Here are my IF statements:
=IF(I15="WSP - DT",I29,IF(I29=0,IF(I30=0," ",+I29+I30+I39+IF(I25=0,0,(I25-1)*1900)),I29+I39+IF(I25=0,0,(I25-1)*1900)))
=IF(I13="Base Suite",IF(I15="Self Serve - Web",AK81),IF(I13="Base Suite + Key Account Advantage",IF(I15="Self Serve - Web", AK84),IF(I13="Key Account Advantage (Stand Alone)",IF(I15="Self Serve - Web", AK87))))
The names in " "s are set up in drop downs.
If a value fallw within these ranges, then the next cell should calculate the answer according to the percentages outlined.
for exmaple:
If somethng costs 250.00 then the next cell should display - 250 x .50 and the answer would be 375.00
1-500 = 50%
501-1500 = 45%
1501 - 1600 = 40%
1601 - 2500 = 35%
2501+ = 30%
and automatically calculate the answer.
I know there's an IF formula, but I can't seem to figure out how to do multiple IF's ~
I am attaching a worksheet for which I need to calculate "National Points". I need to select 5 lowest values from "Tech" and "Speed" (Row 3 headings) events to the left of the totals column but a minimum of three "Tech" values need to be used. I have used the min, small 2, small 3 for the "Tech" set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. It seems to depend on if the 4th and 5th value come from the "Tech" events or "Speed" events. I have calculated the values manually and am unable to get them to correspond all the time. Can anyone help?
View 9 Replies View RelatedI’m trying to get the total number of participants in two different sets, with selection criteria attached to both (I’ve attached an excel file, as the example with those scoring +ve highlighted in yellow). The first set A (b1:b15) I want to select all values above 5, and the second B (c1:c15) set above 10. There other provisor, is that the A and B are further split into two groups (one above and one below 20).
In summary, I need to count all the A>5, B>10 (And both have to be above 20 in column D), but not to double count the ones that have Both A>5 and B>10. I don’t want any duplicates to be counted, so summing the total of A and adding it to B won’t work. In essence it’s an attempt to use the A Union B function that you’d observe when using venn diagrams.
Is there a simple way/formula of doing this?
I have to sets of values that I have graphed....number and dollar amount.
The dollar amount is in the hundreds of thousandths and the number is in the hundreds. I want to have two verticle axis. one on the left hand side for the dollar and one on the right side for the number.
I am trying to figure out how to compare two sets of dates against one another in vba. The data is spread over eight columns. I need to be able to find the smallest date in the first four columns and then compare it to the smallest date in the other four columns, then go to the next smallest and so on.
View 4 Replies View RelatedI manually reconcile two sets of data every month by sorting by PO# and then manually matching up the onse that are the same and then calculate if there is a variance in $ amount.
The 1st set of data is a list of invoice PO#'s and invoice $ amounts and the 2nd set of data is order PO#'s and order $ amounts. What I need to do is reconcile the orders to the invoices by PO#. It is very common to have a PO# on each list but have a different $ amount and I only need to know what the difference is between the order amount and the invoiced amount. I also need to know what is on the invoice list and not on the order list and vise versa....
I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do: ....
View 9 Replies View RelatedI'm trying to compare two sets of data on the same sheet. Each set has 4 columns...A - D and F - I. I would like to take the data from D and I (range 20 to 50) and compare them to see if any differences exist in the two columns. If there is something different I would like to bold and highlight the cells (A-D) in that row, and the same goes for the second set of data (F-I).
I'm trying to do this with macros so I can add it into my existing code.
There are two sets of values.
The first one are located at Sheet1(a2.a6) and Second one are at Sheet2(A1.A1000). how I can write a VBA code to find the First value in Second set of values. If it found in second set, Sheet1.B1 will be entered as the found value from Second set of value otherwise "NOt found".
This task should go till the no of values in first set (in this example five times)
i have an excel sheet with columns including city & order value. i want to group rows with the same city name, and then subtotal the order values for each city. how do i do this?
View 10 Replies View RelatedI have tried to accomplish the following for about two years now but without any success.
This is for a 6 numbers drawn from 49 Lotto without replacement.
I have a list of 6 number combinations in an Excel sheet named "Data" in Cells "B3:G50" ( the combinations will always start in Cell "B3" BUT the Cell "G50" will change depending on the number of combinations to evaluate ).
I have a sheet named " Statistics" where the criteria to use is stored. The criteria to use is as follows :-
Cell "E3" = Total Numbers Drawn ( 6 for example, this could be less or more ).
Cell "E4" = Total Numbers Selected ( 9 for example, this could be less or more )
Lets assume that the first 3 combinations are as follows :-
01 02 03 04 05 06
01 02 03 07 08 09
03 05 06 07 08 09
The maximum number used on this occasion is 9. What i would like the program to do is calculate the unique combinations of 6 numbers from 9 which will be used for the basis of the program.
There are 14 categories of Sets and Subsets for each 6 number combination. I would like to get a grand total of the combinations covered for each of the categories below. The grand totals for each category will go in the sheet named "Statistics" in Cells :-
Cell "D09" = 2 if 2
Cell "D10" = 2 if 3
Cell "D11" = 2 if 4
Cell "D12" = 2 if 5
Cell "D13" = 2 if 6
Cell "D14" = 3 if 3
Cell "D15" = 3 if 4
Cell "D16" = 3 if 5
Cell "D17" = 3 if 6
Cell "D18" = 4 if 4
Cell "D19" = 4 if 5
Cell "D20" = 4 if 6
Cell "D21" = 5 if 5
Cell "D22" = 5 if 6
I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.
I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.
Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.
The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.
Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...
T3OCcxw.jpg
example.xlsx
I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result
VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range
[Code].....
I have 3 different series: S, J, and D. Each series has a list of different dates, and I need to plot the results for each date, while comparing the 3 series. I have created 3 different charts to display the results for each series - list of dates. However, I'd really like to chart all 3 series on one chart, so that you can see how the 3 compare.
I believe my obstacle is that the list of dates are not consistent between the 3. I tried to get around this by creating one table with all the dates, and leaving blanks where necessary (on Sheet 2). However, this created a problem because the blanks showed on the charts as 0s.
I have three different sets of numbers on a r12m that I have to add and divide by different numbers and not quite sure I have the formula right as when I do it manually on a calculator it comes out different.
The formula I have put together is
=sum(O4:z4)/10)+(sum(o5:z5)/200)+(sum(06:z6)/1000)
This is the three rows of data
221121
2333443201142
402448314034292331351663
Using the formula above the result is 0.81 but if I do it manually on a calculator it is 1.6 (by doing each line individually and adding results)
I have six columns.
Column A: dates
Column B: data with relates to A
Column C: dates
Column D: data with relates to C
Column E: dates
Column F: data with relates to E
I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data? For example, say this is what is in my spreadsheet:....
I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.
Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
Create one X-Y SCATTER graph (with Smooth Lines and Markers) with the Average Movement Time (MT) on the Y axis and the Index of Difficulty (ID) on the X axis for each group member during STATION 1: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK and STATION 4: PAPER BASED VERSION OF FITTS' RECIPROCAL TAPPING TASK: WEIGHTED
ID stands for index of difficulty, the graph is supposed to indicate that as the index of difficulty increases (3) movement time increases and as it decreases (2) movement time decreases.
Lab 8 Data Q 4 attempt.xlsx‎
I am trying to maximize fantasy football points based on projected points scored, salary and positional limits.
I have multiple sheets dedicated one per position (i.e. sheet named "QB", sheet named "RB", etc.). The relevant columns are: Name; FPT (projected points); Cost
NOTE: I have combined all the data into "sheet1" if necessary.
I need a VBA program or Solver to diagnose the largest possible combination of projected points based on the following constraints:
Constraint 1: Total value must be less-than or equal to $1,000,000
Constraint 2: Total players used must be equal to 10
Constraint 3-?: Of the 10, 2 must be QB, 2 must be RB, 2 must be WR, 1 must be TE, 1 must be DEF.....the other 2 can be RB, WR or TE
Obviously, each player can only be used once and I'm not sure if Solver can abide by that constraint, nor do I know how to apply the others. I'm thinking VBA could work, but I'm not familiar enough with the language to hammer out the code.
I have a small macro that downloads some data from a website and tweaks it Daily fantasy cheatsheet.zipa little, but I hope that won't affect any computations involving those values.