I have a formula that looks in 2 columns for criteria and then does a count if both sets are matched.
=SUMPRODUCT(--(data!$AC$1:$AC$10000="Y"),--(data!$AV$1:$AV$10000='Summary Sheet'!B9))
Is it possible to do a search with 3 criteria?
I need a search where a third column has also to match. eg data!$AW$1@$AW$10000="Y"
Is this possible and if so what would my formula now look like?
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
Is there a way to force Excel to look at only the first characters in a field when searching?
If I use:
MySearch = Range("C3").Value
Cells.Find(What:=MySearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
It will find any instance of the characters entered in C3; however, I want it to find only fields that START with those characters. E.g. if I enter TRA in the search box it will come up with CITRATE when I want TRACLEER, etc.
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
I have a set of data that looks like this:
A | B | C | D
---------------
100 | 2 | 0 | 0
100 | 0 | 0 | 7
100 | 6 | 0 | 0
101 | 8 | 0 | 0
101 | 0 | 5 | 0
101 | 0 | 0 | 1
Column A is a 'User Number' and columns B-D contain values.
I was hoping to use this data to create a new range where each 'User Number' only has one row, and the values in the other columns are summed for each user.
This would ideally look like this:
A | B | C | D
---------------
100 | 8 | 0 | 7
101 | 8 | 5 | 1
I need to combine data from multiple rows to single rows, for certain fields only. The database is large and can described as having an ID column, followed by columns 2 to n that do not need to be combined, followed by columns n+1 to N that do.
I've attached a sample dataset along with the desired outcome. For the purposes of illustration the sample uses n=3 and N=5 but the actual dataset has much larger dimensions.
I created a nice excel file with some cohort analysis regarding the number of months customers of different cohorts stay with a company. Basically I do a little manipulation on the raw sign-up data sheet and then run a pivot on it.
I want to see the cohorts in two ways:
1. number of customers.
2. $ from customers.
I have the first 1 and I want to get the second one without going through the same procedure again (i.e. manipulating the raw data). In order to get the $ terms, all I need is to multiply a $ column in each row in the columns that indicate the number of months that passed since a customer joined (see the attached example file please). When I try to do it with calculated fields in the pivot table, Excel multiplies the total number of customers with total $ from them all put together, which is of course wrong.
Any way to achieve what I want without having to created another data sheet just for $ terms?
I believe the attached excel file: dummy_for_cohort.xlsx
I'm doing a study of wind turbines, which are spread over a test field. There are 6 turbines, each checked once a day and produces a value. I look up what the weather man says it should be, "Reported" field.
Compare141622263647
DayWind1Wind2Wind3Wind4Wind5Wind6Reported
2872 6918243519
2867122327303133
2854111324324619
28422232527284624
28319233136454627
282411182841448
2819121622264814
28016242530344549
The "Compare" field values must match all values for each turbine. The speeds identify the location with the best match speed against output of power generated. As each value in "Compared" is the best speed for that type of turbine.
What I am trying to do is to automate a copy-and-paste situation for literally a few thousand items. I am creating .xml files for a scenery builder library for MS Flight Simulator, and unfortunately there isn't an application specifically for FS scenery that will import the data I have and export it into the format I need. In other words, I need to create a form in Excel into which I can automate the importation of data from text files (such as in the first example below) to populate fields (as in the second example), and later create a new text file from the compiled data.
This is an example of the format of the data I need to import:
C3745848A201404D875D85A92A7CFA0C Shell
C3745848A201404D875D85A92A7CFA0D Texaco
C3745848A201404D875D85A92A7CFA0E TAG HEUER
[Code].....
Writing a macro in excel where you can use the data in your excel file to copy data to microsoft project.
I have an excel file of task name, deadline, time remaining, and assigned to and I want to copy those 4 columns to a correlating column in project. So basically I would loop through these fields on each line til I reach a blank which could be entering a varied amount of tasks.
The full macro would change ms project start date to today's date then copy the 4 columns from excel to project to add a new task for each line in my file and then save the project file.
I've seen some links on macro with project but most are more advanced then this or are exporting data from project to excel.
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex:
A1 - A4 have "totals" derived from an external source, and the fluctuate daily.
I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time.
I've started with a macro that I would attach to BeforeSave as follows--
Is it possible to fill an embedded pdf (AcroPDF) fields with data from excel?
For example: I have an embedded pdf file, it has a field name "NAMEFIELD", on the click of a commandbutton, I would like Sheets("Sheet1").Range("A1").Value to be input into the PDF file text field "NAMEFIELD".
I have written code to add datafiels to pivot table but I need to remove the ones that are their prior to adding.
Does anyone have a technique using vba to do this.
I have pivot tables with one row field and two or three data fields. I want to be able to quickly hide rows that do not have entries in *all* data fields.
I thought of auto- filtering my data source, but don't think that would be practical as I want to have several tables powering off of the same source data (with many columns), and also the data source is auto-filled with API... Is there a way to traverse the rowfields with vba and hide them if there isn't an entry for every data field? Or a better way than both of those?
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
i have two date fields....one date requested eg 02/06/09 (cell C10)...another date completed eg 03/06/09 (cell R10)
a third field (cell S10) contains the formula: =IF(R10-C10=0,"less than a day",R10-C10)
so if a request was actioned on the day then it shows as less than a day, otherwise will show how many days it took
but when this formula is draggeddown all other cells show - less than a day
how can i make these cells blank whilst still holding the formula?
also - is there a quick formula to add to show only the amount of wrking days a request took to complete?
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies View RelatedI have a table of data from which I would like to extract the information which is listed horizontally.
Column A Column B Column C Column D
Code Name Address Locality
SMITJOHN John Smith 123 Bell Rd Perth
On the 2nd worksheet I have a form which I have designed to which I would like to extract the information from the 1st worksheet.
I am hoping that it is possible to start entering a code based on some reference on the Name. Eg SMITJOHN
Once the code is true or correct it will populate certain cells on the 2nd worksheet automatically.
I work in transport and am looking to streamline some of the paperwork and am trying to do it within the means I have available to me.
The format of the 2nd worksheet is;
CODE: SMITJOHN
NAME: John Smith
ADDRESS: 123 Bell Rd
LOCALITY: PERTH
Any way to export information from an Excel spreadsheet and import it into the IPTC / EXIF files of images? (e.g. there will be an image called 12345.jpg, and in the spreadsheet there will be the number 12345.jpg and some caption, keyword etc. data to import.
View 2 Replies View Relatedthis are function which button needs to be able to do after clicking on it.
• When the value of only one of Description or Unit/Price is entered, a warning message appears.
• When the message is confirmed, return to the entry status for the new data.
• When the values of both Description and Unit/Price are entered, save the values in the list of product (worksheet Product), and increase Product No. by 1 and erase values from NewProduct worksheet this is the screen how it should look but button which should be able to do all the thing above at once is called List Entry
[url]
after creating this function all the values from the table product need to be update into another table which sould look like that:
[url]
Is there anyone how could write code which will do thing above?
in the attachemnt is the excel file which shows what I have done so far, as i am not a good programmer,so I do not know if it's right
I have an excel sheet that has two text fields "A2" 2 digit (Month) and "B2" 2 digit (Day of week), both are currenlty in a text format. I need to force the users of this sheet to enter values in both of these fields prior to saving. Is there a simple macro that I can run to check these fields.
View 8 Replies View RelatedI am having a really difficult time with this code. I am trying to fill in some fields on an webpage in an effort to eventually retrieve some data. I have read lots of posts on the topic and somehow I'm still confused. My code crashes on this line and gives an Automation error, Unspecified Error message: With appIE.document.forms(0). No matter what I try, I can't get past this line. I have also tried: With appIE.document.all
Sub PropInfo()
Dim appIE As SHDocVw.InternetExplorer
Set appIE = CreateObject("INTERNETEXPLORER.APPLICATION")
appIE.Visible = True
appIE.navigate "http://gisims2.miamidade.gov/MyHome/proptext.asp"
Do While appIE.Busy: DoEvents: Loop
Do While appIE.readyState <> complete: DoEvents: Loop
With appIE.document.all
.Item("cmd").Value = "FINDADDR"
.Item("cmdTemp").Value = "FINDADDR"
.Item("searchtool").Value = "ADDR" 'Search by' dropdown
.Item("stnum").Value = "2417" 'house #' field
.Item("stdir").Value = ""
.Item("stname").Value = "ponce de leon" 'street name' field
.Item("sttype").Value = "BLVD" 'street type' field
.submit
End With
End Sub
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
I am trying to figure out how to do the coding for a command button in a userform I've created. It has 5 textbox fields. I want the command button when clicked to transfer the data in those 5 fields to 5 specific cells in a hidden sheet. Then I also want that button to launch a word document.
Anyone know how I can go about this or where I can go that explains the specific coding?
I've tried searching with mixed confusing results & read through http://www.contextures.com/xlUserForm01.html#Top & http://www.theofficeexperts.com/down...ExcelDownloads examples.
I am in the process of comparing the data between 2 different sheets and the data on each sheet contains 2 columns of information. I am trying to find a way to compare one sheet to that same item on the second sheet and see if anything is missing, however the second sheet contains more information between the two columns and the rows are never the same during my comparison. I've tried Match, Lookup, Indirect, and if/then with an And functions and because the rows do not stay the same the data is not accurate. Here is a brief example of what I am working on:
SHEET 1
A B
1 Red Delicious Apple 125 Count Apple
2 Red Delicious Apple 125 Count Fresh Fruit
[Code]....
So in these comparisons, the names in Column A match identically, so if I try to say take the name from sheet 1 and match to that in sheet 2 and if the wording in Column B matches then put a "Yes" value. But you can see on Sheet 2 by the time it gets to rows 14, 15, 16, the Baby Food Chicken has a 3rd item in Column B in comaprison to Sheet 1, so any type of match doesn't work.
I am using Excel 2003.
I have a spreadsheet with a heap of raw data in it. I need to perform a search based on a customer name, then return certain columns from the raw data IF the customer name is matched in one column, AND a special flag name is matched in a second column. The customer name is always a single entry for the purpose of the query, however the special flag must be checked against a possible list of values in a table. I know this is hard to explain, and I can't add attachments to my posts, so I have created a sample document and placed in in dropbox:
[URL] ........
- The macro should then try to find this data on the worksheet "Sheet2" in the same workbook. This data on Sheet2 is essentially a table with four columns "Number" (Col A), "Country" (Col B), "Consol" (Col C) and "Bypass" (Col D) but can have around 70k rows
- It should look for Number first, and if its not found, the macro should insert the message "Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it matches the value input for Country on Sheet1, then it insert the message "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "Y" but the Bypass field input on Sheet1 has "N", then put "Multiple Records - Update OK" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows and it does not match the value input for Country on Sheet1, and if all Bypass fields in Sheet2 have "N" but the Bypass field input on Sheet1 has "Y", then put "Multiple Records - Bypass - Do Not Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is the same for all the rows that have a Bypass value of "N" and it does match the value input for Country on Sheet1 then put "Multiple Records - No Need to Update" in cell A8 on Sheet1
- If it finds at least 1 row for the Number but the Country is different for all the rows that have a Bypass value of "N", then put "Multiple Records - Refer" in cell A8 on Sheet1
[URL] ....
TestBook.xlsm
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.