VBA To Get Data Sorted Into Groups
Sep 20, 2013
Explaination of Data:
I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.
I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.
The idea is to see which Site Code sells more with a particular Campaign.
As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.
The sample file is uploaded on Google Docs. [URL] .........
View 1 Replies
ADVERTISEMENT
Mar 5, 2013
I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.
I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.
View 9 Replies
View Related
Oct 7, 2013
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
View 3 Replies
View Related
May 22, 2012
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies
View Related
Jan 8, 2010
Does anyone know of an Excel object class that you can put objects into with a key and the process it in key-sorted order? If not, is there a library that I can reference to get such a thing?
View 6 Replies
View Related
Aug 19, 2008
I have data in a spreadsheet that need to be re sorted. Here is the data:
Name Address Address Address Phone
XYZ 123 Main NA WI, 12345 123-123-1234
ABC 123 South NA AZ, 1111 456-456-4567
is there a way (i.e marco) that can reorder this info to look like this?
XYZ
123 Main
NA
WA, 12345
123-123-1234
ABC
123 South
NA
AZ, 1111
456-45-4567
View 9 Replies
View Related
Aug 22, 2013
I have data that I need to be dynamically sorted in the highlighted manner. The values on the selection criteria are dynamic.
View 14 Replies
View Related
Mar 9, 2008
I have a matrix of mixed numbers (going across many columns and rows) in Sheet1. They are a mix of positive and negative numbers, In Sheet2 I need 2 lists to be automatically displayed, taken from the data in Sheet1.
The first list (column) is for positive numbers only, the second is for negative numbers.
There is one filter: only numbers over a specified value (for the positive list) must be used from the data, and only numbers under a specified value (for the negative list) must be used from the data. The 2 filters can be stored in any cells in Sheet2 (the worksheet where the lists will be), lets say, for ease of use, A1 and B1.
But here's the tricky part: I need the location reference of each number in the matrix displayed next to the number in the list!!! Eg: in the matrix, I have names running down the left (A2 to A25) and the same names in the same order running across the top (B1 to Y1) - just like any basic matrix! If A6 is called "John" and L1 is called "Pete" and their value (at cell L6) is 10, then I need them to be displayed in the "positive list" amongst the others (sorted automatically into numerical order) with the words "John" in the cell to the right of the score, and "Pete" in the one after that to the right: so it will look like:
A2 B2 C2
10 John Pete
(remember that these 2 lists are on Sheet2, not on the matrix worsheet! A1 and B1 contain the 2 filters for the positive and negative lists).
Of course, this must only happen if the filter in A1 (the positive filter) is 10 or under. If it was anything above 10, then this one would not make the list.
As for duplicates in the matrix coming up twice in the lists, that's not a problem this wont matter! It's ok for the lists to have constant duplicates displayed eg: 10 John Pete will also have next to it 10 Pete John.
View 9 Replies
View Related
Oct 24, 2008
How do I split a worksheet by sorted ranges and then save each range as a new workbook?
For example:
How would I split the data on the left into the ranges on the right of the image below?
View 9 Replies
View Related
Jul 23, 2006
I Currently have some VBA sourced through here which adds to the end of the first instance of a value in column A the values in column's B and C and repeats adding values in new cells for B and C until the value in column A changes.
Now I need to transfer this sorted data to another worksheet (destination.xls) and add it by the reference number in column A to the end of the row with the same reference number.
I have experimented with vlookup with limited success and am looking for a more robust solution.
The data from the spreadsheet called source.xls appears starting in column EE. This will be the same starting position for all rows I have coloured the data for ease of recognition purposes only.
I have attached 2 sheets as examples of what I am trying to achieve.
View 9 Replies
View Related
Feb 22, 2014
I am trying to compare the frequency of occurrence of multiple unique values within a data set and graph the results as a pareto. I have the formulas working to identify and count the occurrences, and the chart is designed and displaying the data, however I cannot get the graph to display in descending order.
I am unable to sort the data because it is generated from a series of formulas, the returned values of which cannot be sorted. (its hard to describe, see the attached workbook)
I would like to know how I can display the graph so that it shows the bars (primary axis) in descending order (highest value to left, lowest to right) since I cannot sort the data. i.e. For the attached workbook, I want the bars to display in descending number of occurrences: 81,15,5,5,3,3,2,2.
[URL]
View 2 Replies
View Related
Mar 27, 2014
I'm hoping to make a dynamic sorted list for each Mfg (H - T) using the garbled data input in columns B & C.
See attached : sorted list.xlsxā€ˇ
View 5 Replies
View Related
Jun 17, 2014
My excel sheet contains a set of date-marked expenditures. It's all one running list - no starting a new column for each month or anything like that.
I'd like to include a separate column/sheet showing total expenditure for each month. I've tried multiple formulae, along the lines of "Sum the B cells but only those for which the corresponding A cell is in the right value range". Or in programming terms, "for i from 1 to infinity, if x < A(i) < y, add B(i) to sum." Since other formulae on the sheet are independent of item ordering (don't require the data to be sorted chronologically), a plus would be if the solution can be, as well.
A separate thing I'd like to do (which I'm guessing has a similar solution) is to include grand (running) totals by expenditure type. A separate column of (non-numerical) expenditure codes is next to the data.
View 5 Replies
View Related
May 24, 2013
I am using the formula below to find the # of hours that staff worked.
=SUMPRODUCT(('Data 1'!$B$2:$B$601=B7)*(LEFT('Data 1'!$A$2:$A$601,2)=LEFT(C7,2))*'Data 1'!$D$2:$D$601)
B7 = Last Name
Left(C7,2) = First 2 Letters of the First Name
$D$2:$D$601 = Hours Worked
My only problem is that some staff show up in the list on "Data 1" twice as they work in 2 separate stores and I am only getting the hours worked at the first store. Is there a way to have excel add the hours for both stores? My data is sorted alphabetically so they will be one above the other.
View 4 Replies
View Related
Feb 21, 2014
I am trying to compare the frequency of occurrence of multiple unique values within a data set and graph the results as a pareto. I have the formulas working to identify and count the occurrences, and the chart is designed and displaying the data, however I cannot get the graph to display in descending order.
I am unable to sort the data because it is generated from a series of formulas, the returned values of which cannot be sorted. (its hard to describe, see the attached workbook)
I would like to know how I can display the graph so that it shows the bars (primary axis) in descending order (highest value to left, lowest to right) since I cannot sort the data. i.e. For the attached workbook, I want the bars to display in descending number of occurrences: 81,15,5,5,3,3,2,2.
View 2 Replies
View Related
Apr 30, 2013
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
View 8 Replies
View Related
Oct 29, 2007
Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.
Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.
View 8 Replies
View Related
Feb 25, 2007
I have attached sample of a series of massive spreadsheets that I am working on.
Item Numbers in Column A
Flag in Column D
Values in Column P
I want put the total of the values in P for each group of item numbers in column C where the flag is YES.
Unfortuantely I prefer not to use excel's subtotal functionality or array formulaes here because of the logistics of how these are used.
View 9 Replies
View Related
Apr 22, 2008
I have this an excel file which contains about 500-800 entries. These entries have about seven 0's which denotes where the data starts for a each group. In order to separate the data, I want to copy and paste this data to other workbooks. What would be the best way to select the rows from one 0 to the next?
View 3 Replies
View Related
May 16, 2014
I am trying to improve a spreadsheet that I have to use everyday. I have a list with varying number of entries (different length every day) that has a number of columns including a column that contains machine codes (say 1,2,3,4,5...).
Each machine is programmed by a different person (say Andy,Ben,Craig,etc.).
One programmer can program more than one machine so for instance Andy is interested in programs for machines 1,3,5 and Ben in machines 2 and 4.
Every day we send a list of jobs that require programming which is pre-filtered for each programmer so if I send the list to Andy he only sees rows of data with machine codes 1,3 and 5.
I am trying to make a drop-down where I pick the programmer's name and the list automatically gets filtered for the machines for that specific person (e.g. 1,3,5).
I believe this could be done with advanced filtering but the problem is that the list length changes every day and the data is pasted into the spreadsheet from a different CSV file so I cannot define a set range for advanced filtering because it does not accept blank rows...
I am attaching a screenshot : list.jpg
View 1 Replies
View Related
May 28, 2014
I have a spreadsheet with data in a table with order numbers in column A. Although each order is given a unique number, the data populates with multiple rows, one for each line of the order, all with the same order number depending on how many lines where on the order (which is generated via other software and cannot be altered). Therefore the data, when sorted by order number, appears in groups:
[URL] .....
Is it possible to conditional format each line in groups of the same order number to make it easier to differentiate between each individual order which will still work when the data is sorted or filtered?
View 2 Replies
View Related
Dec 4, 2009
I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
View 4 Replies
View Related
Aug 5, 2009
I have ~1,000 rows of data utilizing columns A:C. The data is "grouped" by data that is in column C. Layout is as follows for example
Row01.....Column A......Column B.......Column C
Row02.....some data.....some data.....apples
Row03.....some data.....some data.....apples
Row04.....some data.....some data.....apples
Row05.....some data.....some data.....apples
Row06.....some data.....some data.....oranges
Row07.....some data.....some data.....oranges
Row08.....some data.....some data.....oranges
Row09.....some data.....some data.....oranges
Row10.....some data.....some data.....pears
Row11.....some data.....some data.....pears
Row12.....some data.....some data.....pears
I have been trying to come up with some code that will "select" groups of data and then perform an action on those cells. As an example:
View 5 Replies
View Related
Feb 21, 2009
I have a worksheet filled with employee data. I.e. EMPLID, COUNTRY, MANAGER_ID, etc. I want to extract these employees into a new workbook (spreadsheet) by the employee's manager. So each workbook would contain every employee row data for only one manager.
View 9 Replies
View Related
Jan 13, 2008
I'd like to be able to have a macro that will create a pivot table based on a range, open up each of the total rows within the pivot table (which will create a new sheet), name the worksheet the title of the total row corresponding to it then copy each worksheet created by the pivot and paste into another sheet in a required format.
View 5 Replies
View Related
Jan 3, 2014
I need to sort groups of data by a single cell in the group, compared to other single cells in other groups of data. The groups are always the same number of rows but vary in the number of rows. There is an example file below. The red is the company name and the green is what I need to sort the group by, as compared to the other groups. This example only has a few, the real file has thousands of groups. I want to sort the companies by the totals in green...they are currently in alphabetical order. There must be a way to do this without going the VBA option.
View 4 Replies
View Related
Jul 15, 2014
I have a fairly large set of data on one sheet containing numbers for 60 different people. The data is set up similarly to the set below:
John Smith 75
John Smith 80
John Smith 62
Jane Doe 49
Jane Doe 89
Jane Doe 66
So on and so forth until the 60th person. My question is: Is there a way to average the numbers for each different person at one time? What about median and mode as well? Do I need to set up a different sheet for that?
View 11 Replies
View Related
May 12, 2009
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
View 2 Replies
View Related
Oct 2, 2009
I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.
I am after code which will identify duplicates in column A within a contiguous range of data.
For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered.
Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data.
Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - DUPLICATES - Many Macros.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA29=ABCD29 PN3215 301SOUTHERN APPROACH9107 313NORTHERN APPROACH9105 322GENERAL UNDERSIDE OF WESTERN STONE ARCH9111 331WESTERN ELEVATION9104 343GENERAL UNDERSIDE9110 35 36 PN7583 371SOUTHERN APPROACH9111 382NORTHERN APPROACH9112 391GENERAL UNDERSIDE9113 Sheet 9 [HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jan 13, 2010
way to transpose groups of data where each group is a different size. To clarify, my data looks like this (all one column):
data 1
data 2
data 3
data 4
data 5
data 6
data 7
data 8
data 9
data 10
and I would like to get it to look like
data 1 data 2 data 3
data 4 data 5
data 6
data 7 data 8 data 9 data 10
That is, to transpose each group of rows before a space.
View 9 Replies
View Related
