My data is in colunm set #1. I want to create from the data set in column set 1 the data set shown in colunm set #2. I want to automate the process. The program needs to regognize if a name appears only once or more than once. If it appears more than once the data needs to be merged as I highlighted from yellow to green.
View 4 RepliesI want to do is use a userform to automatically create a graph from the data that is shown in the worksheet. The data in the work sheet will grow and shrink all the time. Am i right in thinking I am meant to create a dynamic range? I know how to do it but am not too sure what I am meant to be naming. And secondly I am not too sure how I set up the button to produce the graph.
View 4 Replies View RelatedIn Sheet1, I have 1000 columns with data from A1:A100 to ALL1:All100. I want to copy each indivdual column and paste into one colunm in Sheet2.
View 10 Replies View RelatedIn worksheet "Details" search down column "A" looking for any text/numbers. If there is something entered in the cell then look at column "M" for the same row and if that cell is empty inset "0%" in the cell.
Example:
Cell "A1" contains 12345. Cell "M1" is blank. The macro would inset 0% in "M1".
Cell "A2" contains 54321. Cell "M2" contain 40%. The Macro does not modify that row.
Cell "A3" is blank. The macro does not modify that row.
Cell "A4" contain 65478. Cell "M4" is blank. The macro would inset 0% in "M4".
I'm writing an IF statement referring to a cell that contains the character "C" and comparing it to a blank cell, and it's returning True, while it should be returning false because they're not the same.
View 6 Replies View RelatedI have set up a sheet recording an each individual's turnover for my business i.e., hours worked, etc and have set up a data validation table for drop down options of Jan, Feb, Mar,etc....
Rather than creating more work for each month on the worksheet, how can I enable the drop down to contain the data specific to each month?
The attached spreadsheet is an example of a report that is dumped into Excel. It is very messy and needs a lot of formatting. I would like to create a column for the highlighted contract numbers.the contract number changes with each header.
View 7 Replies View RelatedI have 2 columns full of information feeding from 2 different sources on our internet database
I would like to collect this information into 1 column to create a drop down list
As these are expanding tables feeding from the internet I don't think copy and paste into one column will work (I'm not sure about this but I would prefer a formula to avoid any issues).
I was going through a thread that is open now about a similar subject: Create Workbooks & Worksheets For Each Group In Table. I tried getting it to work myself but was unsuccessful. I want a code that will create a new workbook for every new Vendor. I tried doing it myself but was getting compile errors... If this is not what I should be using let me know, it works the way I want with just worksheets, i thought it would be an easy conversion to workbooks
Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("A1", Range("A65536").End(xlUp))
'Delete any sheet called "UniqueList"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("UniqueList").Delete
'Add a sheet called "UniqueList"
Worksheets.Add().Name = "UniqueList".....................
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.
Ex.
Data1 Data2 Result 1 Result 2 Result 3
one100one100two200three1
one350one350two450three2
one500one500two600three4
two200
two450
two600
three165
three236
three450
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.
I'm new to Excel (4th day in class) and I can't find the answer to what I'm looking for on the net. Part of the problem is that I don't know what to call it, so I have a hard time looking it up.
Very simply put, let's say there are examples numbers 1,2,3,4 put in cells A1 to A4 (vertical) and more example number 50,60,70,80 are put in cells A6 to D6 (horizontal)
What one formula can be written in B1 that I can copy down to B4 that will make B1=A1+A6, and B2=A2+B6, and B3=A3+C6, and B4=A4+D6
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.
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
*
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.
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
=IF((P3<$Q$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed")))
Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs.
Originally my formula worked fine as:
=IF(P4<$Q$1,E4-S4,"Not Changed")
But I realized that i needed a certain category of product ignored. Products are labeled in column G.
I'm working on a project where column A will always have numbers added every month. How can I get column B1 to automatically retrieve the last number shown?
Example
Month 1 Month 2
Column A Column A
1254 1254
5871 5871
5654 5654
5301
5024
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
I have an spreadsheet that has a colunm of numbers. I have three totals (values-no formula) that make up the total of that column. But i need to know which numbers make up each of those three totals. I was able to track one of the total but it was too time consuming. I know there are probably several different ways in wich this number can be add up. Is there a way to do this with a macro?
View 3 Replies View RelatedRecently I come across a file with only 1 sheet, in which is a table only occupied an array of A1:Q49. In Normal View I can see the row / column header of A-Q and 1-49 as usual, but nothing outside that range (only grey colour). Also, the empty cells outside A1:Q49 are all grey too. How can I do that?
View 2 Replies View RelatedI'm creating a macro that will involve some form of if-else/case-switch, as well as a loop (probably), but I'm not too sure how to go about it.
Basically there are records in columns. Lets say Column A has numbers in it, for example
Cell A1 has "Three"
A2 has "Five"
A3 has "Two" all without quotes
I basically want a macro that will loop through the whole column, so if A1 contains "Three" (not case sensitive), then set B1 to "3" without quotes. If A2 contains "Two", then the macro will set B2 to "2" without quotes.
Of course this is sample data but you get the jist. It needs to loop through one column, so as to fill in the other column. You can assume that column A will always be filled, so the macro is being created to automatically fill in column B based on what is in column A.
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies View RelatedEach sheet has the same basic formatting. A1 contains a name. B1, C1, D1 are column headers. B2:B is data. C2:C is data and always stops at the same row B2:B range does. The only differences between the sheets is that they might not stop at the same row. I want a macro that merges A1 vertically as shown in my spread sheet to the end of column B and C. I want a border around the merged data, as well as around the B data and the C data individually.
View 3 Replies View RelatedI am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.
So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:
Example Spreadsheet.xlsx
I need to search the beginning of all of the numbers in the apn column on Tab B with the numbers from the APN 1 column. When a match is found then that row will be copied to a new tab named after the Description row on the Tab A tab.
View 4 Replies View Related