Create A Function That Numbers Rows With Respect To Data Groups In A Column
Dec 1, 2007
I need to create a function that numbers rows with respect to data groups in a column (column labeled "Type" in this example). The result would be that shown in column A in this table.
How do I write the function?
#TypeName Date
1CarsJohn1/12/2008
2CarsJane11/10/2007
3CarsMary11/2/2004
4CarsBob12/7/2003
1TrucksMike12/12/2007
2TrucksSandy1/3/2007
3TrucksDale12/14/2006
4TrucksVince4/8/2005
*
View 10 Replies
ADVERTISEMENT
Apr 10, 2008
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst)
2. Copy their system info to each respective worksheet
I've attached a sample workbook.
View 2 Replies
View Related
Feb 15, 2010
In the attached workbook I'm trying to populate Column E with sequential numbers (as shown) based upon a changing range (defined as a named range called 'range'). Is it possible to write a formula in the cells in Column E that will do this?
View 2 Replies
View Related
Jun 26, 2008
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs()
Dim Startpoint As Integer
Dim Endpoint As Integer
Dim count As Integer
Dim xStart As String
Dim xEnd As String
Dim NumberSets As Integer
Dim yStart As String
Dim yEnd As String
Dim DataSet As Integer
Dim Data
Startpoint = 11 'The first set always starts in row 11
Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10
NumberSets = Range("L7").Value 'number of times I need the loop to work
count = 1..........................
View 2 Replies
View Related
Jun 17, 2014
I have a table of 5 columns (each of 50 length) that is automatically refreshed (i am getting the data from web)after an interval of 5 minutes. I want the first column to be sorted alphabetically after every 'refresh' command, that has been executed by the excel. i have tried recording the macro and then running it on keystroke of 'ctrl + m'...the table surely gets sorted out but i can't be pressing ctrl+m every 5 minutes. auto-sort that is required to be executed after the auto refresh command..
View 6 Replies
View Related
Mar 3, 2012
Is this possible? If
A B C
0 0 0
0 0 0
1 4 5
2 4 8
0 0 0
0 0 0
0 0 0
2 6 9
8 9 3
3 5 6
0 0 0
In the raw data, the 0s split up the data into different steps. I am trying to create a function that selects the data from the ABC column and puts it into new columns for each step. I.e:
New Columns with:
1 4 5
2 4 8
and
2 6 9
8 9 3
3 5 6
In the raw data files, there will be 10 steps, with a varying length of data.
View 2 Replies
View Related
Mar 25, 2008
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If
End Function
This function only returns a "#value" and I don't quite know how to troubleshoot it.
View 9 Replies
View Related
Aug 20, 2014
following issue:
The following table is given:
flower
20
rose
flower
21
rose
[Code] ........
Which needs to be turned into:
flower
20
22
rose
flower
31
32
blossom
tree
1
3
apple
The last column is the one that dictates when a new range of numbers start. There should be one range of numbers for Rose, One range for Blossom etc.
View 4 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
Mar 30, 2007
I realize that the easier resolve to this request is to use Access & we are moving to that application.
I have included a worksheet as an example.
We have a 'work in progress' (WIP) worksheet that contains 12 rows of data per job. Need to know how (or if) you can sort by the "Job #:" value while maintaining the group of information necessary for each individual job.
Not a normal sort because the values aren't entered in typical side-by-side row & column format.
View 10 Replies
View Related
Feb 14, 2013
I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.
The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"
View 8 Replies
View Related
Sep 11, 2012
Is it possible to create a string with a range of numbers
1
3
4
5
6
8
10
Result: 1,3:6,8,10
Or
1
2
3
4
Result: 1:4
Or
1
3
5
7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells
[Code] .........
View 9 Replies
View Related
Jul 1, 2008
I have a data set in an Excel spreadsheet entered as one column of data. It is names with addresses, phone numbers, job title, etc. I want to select and transpose each person in the list so when I am done the person's name is in column A, Company name in column B and so on. The problem is the information listed is not the same for each person - so there is a different number of rows for each person. The names are in bold text though, so I need to select from one cell with bold text to the row BEFORE the next row with bold text and transpose the data for each selection. I found this forum by finding an old question here that is similar at Copy based on Bold Paste-transpose provided in that post and it produced no result.
View 4 Replies
View Related
Mar 25, 2014
I'm working on a long list of cells that have multiple entries of the same part number at different point on the list. I want to create a function that consolidates those part numbers in two columns to the right of the raw count.
PART
QTY
CONSOLIDATED PARTS
CONSOLIDATED
QUANTITIES
View 3 Replies
View Related
May 9, 2008
I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
View 2 Replies
View Related
Feb 25, 2014
I'm looking to do some simple maths with ranges in Excel but I'd like a way to be able to input all these formulas automatically although I am pretty sure a single formula per column could do it all automatically. I've got to put the sums in AD to AF on the first line of each location, but the maths is for the entire location's range.
[URL]
View 3 Replies
View Related
May 20, 2014
I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
[Code]....
View 1 Replies
View Related
Sep 11, 2008
I want to delete rows in whole list and numbers of rows to be deleted I have in for example C column. How to do it?
View 9 Replies
View Related
Jan 26, 2012
I regularly build a pivot table using VBA. I now want to group countries into regions within the pivot table, and I want to automate that using vba.
For simplicity, assume I have the countries Germany, Netherlands, Belgium, Luxembourg, Norway and Sweden; and I want to create the Groups BeNeLux and Scandinavia. (In reality, I have 150 countries, one group of 10 countries, 2 countries I want to show individually, and the rest of the world I want to group together)
I know I could select the cells using something like cells(d2:f2).group, but that seems wrong....
I have attached a file showing how far I can get with vba and where i want to get.
I have also tried things like ".DataRange.cells(2).group by:=3" but that never led to anything....
grouping reqs.xls
View 2 Replies
View Related
Oct 3, 2013
I attempting to create a formula that will create random numbers in a column without repeats. For example, I have a spreadsheet with two columns. The first column I want to contain the random numbers and the second column will contain names. I just need the formula to create the random numbers once.
View 2 Replies
View Related
Jan 8, 2009
I have a spreadsheet with many workbooks. Each workbook contains sale figures for all products for each product range over a period of time. There is one workbook that totals the figures from other workbooks.
This spreadsheet is updated by another department on a weekly basis with new figures added to each workbook (products within each product range are also likely to increase).
Currently, the data in each work book is grouped by months (with a row summarising the month's figures). I can create graphs to visualise monthly performance, but also need to have a weekly performance graph/chart. Thus, the data also needs to be grouped by weeks.
I have tried creating subgroups for the weeks within the month groups, but it seems impossible (?!) to do when a week subgroup comes right in the middle of a month summary (e.g. Dec 2008 Summary comes in between Week 29 Dec 2008 - 04 Jan 2009). I have attached an example spreadsheet in case my description was too convoluted!
View 2 Replies
View Related
Dec 6, 2006
I have to make a macheine cycle chart. I have a column containing Step Names and a column containing step time in whole seconds. I need to paste the step names across Row 1 and whole numbers counting to X down colum A. I then need to have each second represented by coloring the cell, for example, Step 1 is 1 sec. and Step 2 is 2 sec. if "Step 1" is in B1 I need B2 colored then for "Step 2" in C1 i need C3-4 highlighted and so on for a variable number of steps. This is my first question so I hope I was clear enough. With this much programed I will be able to make simple changes to tweak it to fit my needs.
View 2 Replies
View Related
Aug 8, 2007
I have some code which, which I have modified it to create check boxes for all rows of data, but when I run it, it begins to wipe out the data, and create an endless column of numbers and check boxes. Here is the code:
Sub SelectCell()
Application. ScreenUpdating = False
Dim Z As Range
Set Z = Cells(1, 1).EntireColumn. Find("*", SearchDirection:=xlPrevious)
For i = 2 To Z
Range("A" & i) = i
Set cll = Range("A" & i).Offset(0, 1)
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 8) = "CheckBox" And shp.TopLeftCell.Address = cll.Address Then
shp.Delete
Exit For.......................
I have tried to set it to recognize how many rows are filled with number, give that to variable Z and then apply the code but it simply does not work.
View 7 Replies
View Related
Feb 9, 2013
I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:
Supplier
Invoice Date
GL Date
Invoice Amt
[Code].....
View 1 Replies
View Related
Feb 23, 2010
The format of the text in which I need to extract numbers is as follows:
23411268 - 23411270
Need to extract the following:
23411268
23411269
23411270
These numbers have to be listed in three seperate rows.
View 14 Replies
View Related
Jan 1, 2010
I am trying to solve is as follows, I have groups of 20 numbers in columns, one per column and would like to compare the groups to determine if any group is duplicated on another line.
I had been looking at using VBA to copy the numbers to an array, sort from lowest to highest, concatenate and use that to compare each group. If possible however if there is a suitable formula it would be preferable.
I have attached a small sample to illustrate.
View 8 Replies
View Related
Jul 17, 2014
Product Group Arg.Braz.Mex.Peru
Red paint Paint5324
Blue paintPaint5684
WallpaperWallpaper6585
LampsFurniture 4631
ChairsFurniture 1645
Green paintPaint1356
SofasFurniture 3358
SUM(Paint,Argentina)SUM(Paint,Brazil)SUM(Paint,Mexico)SUM(Paint,Peru)
SUM(Wallpaper,Argentina)SUM(Wallpaper,Brazil)SUM(Wallpaper,Mexico)SUM(Wallpaper,Peru)
SUM(Furniture,Argentina)SUM(Furniture,Brazil)SUM(Furniture,Mexico)SUM(Furniture,Peru)
I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.
This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories.
View 4 Replies
View Related
Jul 11, 2012
I am trying to separate a huge group of phone numbers from one cell into individual cells in a column. the numbers are currently separated by a semicolon. Is there an easy way to perform this action?
View 5 Replies
View Related
May 8, 2013
I have a workbook called PETB. I would like to extract the following account number ranges in Col A to sheet 2 (the numbers are all 6 digits) for eg 273000
1) All numbers starting with 2730 and ending in a zero and 02 to be extracted together in one group for eg 273000, 273050, 273250, 273002, 273052 to be extracted below each each
2) All numbers starting with 2730 and ending in a 4 to be extracted together in one group
3) All numbers starting with 2730 and ending in a 6 to be extracted to together in one group
View 2 Replies
View Related
May 21, 2013
I have SEVEN numbers and FOUR groups.
What I would like to do is list ALL the PERMUTATIONS of those SEVEN numbers in FOUR groups.
So basically, the groups would look like...
7000
4210
4201
4111
4003
3400
3310
3301
3301
3220
[code].....
...and EACH group would add up to SEVEN etc.
I think there are a total of 840 different PERMUTATIONS available, from anything from 0 numbers in a group to 7 numbers in a group.
I need ALL these groups listed individually .Is there some code that will do this?
View 4 Replies
View Related
