Order Sets And Arrays
Feb 12, 2014
We 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...)
View 1 Replies
ADVERTISEMENT
May 31, 2014
I need to calculate a Sums of Products with unusual requirements. Please refer to the attached picture. The Orange Cells under "Noise Weight" are the Formula Cells. The Values in those Cells need to evaluate as shown in the equations below. I want this to be dynamic, so that if I expand the FM Region (Add Columns between "Noise Weight" and "FM15") AND expand the Severity Region (Add Rows between Severity and Noise Weight), I will not have to update Formulas manually. I know for sure that the standard SUMPRODUCT Function cannot handle this.
PRODUCT 1 = D29*E31 + D28*F31+D27*G31.....+D15*S31
PRODUCT 2 = D29*E32 + D28*F32+D27*G32.....+D15*S32
PRODUCT 3 = D29*E33 + D28*F33+D27*G33.....+D15*S33
...
....
PRODUCT 7 = D29*E37 + D28*F37+D27*G37.....+D15*S37
View 11 Replies
View Related
May 8, 2013
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
View 1 Replies
View Related
Apr 10, 2008
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 Replies
View Related
Nov 29, 2008
I looked at threads realted to printing but wasn ot able to find something similar to what I need. So here is my problem:
I have a file with more than 100 worksheets (each sheet contains the invoice for one store). I would like to create a macro that would enable me to determine the order in which worksheets would be printed. How to do it?
Idea #1: the printing order would be based on the value in cell L1 that would contain the route number for each store. Stores belonging to the same delivery route will have the same value in L1. So, the macro should first print all sheets with 1 in cell L1, then print all sheets with 2 in cell L1 and so on...
Idea #2: Creating a separate data sheet with the list of all stores and their corresponding route number. Let's say info is contained in range A1:B150, where Column A contains the name of the stores and column B contains the route numbers. The macro then should look at that list to determine the printing order of the subsequent worksheets (the name of the store in column A would be the same as the name of the worksheet corresponding to that store).
Idea #3: sorting my 150 worksheets manually. It does not solve my problem fully, though, because stores do not always belong to the same route. So the manual sorting should be carried out daily and would not save time at all.
View 9 Replies
View Related
Mar 1, 2008
I need a way to re-order an excel worksheets columns based on another worksheet.
Data is extracted from a database into excel however users can configure the columns in the initial system. to how they like This causes problems when the data is copied into an excel spreadsheet I have created as the data copied will not be in the same column order as is required.
How can i reorder the columns without physically having to cut and paste the columns to match?
I have attached a sample spreadsheet.
You will see on the spreadsheet that 'fixed columns' is the order that i require the data however 'variable columns' is not in the same order.
View 3 Replies
View Related
Jul 8, 2014
I 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.
View 2 Replies
View Related
Jul 10, 2014
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.
View 5 Replies
View Related
Oct 17, 2013
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.
View 4 Replies
View Related
Nov 8, 2013
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?
View 2 Replies
View Related
Apr 1, 2014
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
View 5 Replies
View Related
Jun 9, 2014
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 Related
Dec 13, 2009
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.
View 2 Replies
View Related
May 15, 2008
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.
View 14 Replies
View Related
Jan 28, 2009
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 ~
View 5 Replies
View Related
Mar 7, 2009
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 Related
Jan 21, 2010
I’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?
View 13 Replies
View Related
Jul 6, 2012
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.
View 1 Replies
View Related
Jun 11, 2014
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 Related
Dec 6, 2006
I 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....
View 10 Replies
View Related
Feb 23, 2009
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 Related
Aug 28, 2009
I'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.
View 9 Replies
View Related
Jul 30, 2006
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)
View 9 Replies
View Related
Feb 10, 2007
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 Related
Jul 22, 2007
I 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
View 9 Replies
View Related
Feb 3, 2010
I have been trying to solve this problem by breaking it down to it's core elements and it seems to hang up at a certain spot but I can't figure out why.
formula is: {=LARGE(IF(AND(Array1+8<20,6-Array1<0),Array2,0),1)}
where: Array1 is a sequence of numbers, say, 2 5 8 11 14 17
Array2 is a corresponding sequence of numbers 1 2 3 4 5 6
the resulting array should return the numbers 0 0 3 4 0 0
and my answer should be 4
instead my answer is 0
when I break it down and select six cells and use the formula:
{=if(Array1+8<20,Array2,0)}
my result is: 1 2 3 4 0 0
and another six cells and use the formula: {=if(6-Array1<0,Array2,0)}
my result is: 0 0 3 4 5 6
when I select a single cell and use the formula: {=large(if(Array1+8<20,Array2,0),1)}
my result is: 4
when I select a single cell and use the formula: {=large(if(6-Array1<0,Array2,0),1)}
my result is: 6
I've tried this method several different ways, even using "Yes" & "No" as my result and then try to match them up. I've tried doing this not using named array and just selecting the cells themselves for the formula. All with the same results.
View 4 Replies
View Related
Jan 4, 2010
How do I deal with arrays in vba? Below is a dummy code just to check the array I specified is working:
View 5 Replies
View Related
Sep 8, 2007
I have a worksheet that has 20 columns and 500 rows. I created a VBA macro to loop through the data to hide rows that do not meet certain criteria. After the data is selected I copy and paste those selected rows to another sheet. The macro works well but I would like to use an array to contain the data that I copy and paste to a new sheet. I have been trying to find information on multi deminsion arrays but I have not been able to fully understand how to get the information into the array and then how to get it out again. Most of the examples that I have seen are for two maybe three columns (dimensions). I am hoping that someone could point me in the right direction to get started on this. I also have a few books on Exel VBA but none of them seem to address my question.
View 9 Replies
View Related
Nov 18, 2009
I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.
Sub test()
Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer
MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
ArrayCount = UBound(MyArray)
For i = 1 To RowCount
Range("B" & i).Value = MyArray(i - 1 Mod ArrayCount)
Next i
End Sub
View 9 Replies
View Related
Nov 10, 2013
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].....
View 1 Replies
View Related
