This input is exported from a system we have and may vary in length and values from time to time.
What I want to do is pull all the data for specific currency pairs (probably for 5 of the 10+ currency pairs it displays) onto another worksheet. And for each tenor for that currency pair, i want to perform 10-20 lines of analysis on the next sheet.
So, if GBPUSD was a currency pair I was interested in, I would need 10-20 lines for GBPUSD 1w, 10-20 lines for GBPUSD 1m, 10-20 lines for GBPUSD 2m, etc.
How can I pull this? While maintaining flexibility so if the tenors, currencies, and # of lines change?
I have a column of IDs (260, 266, 405, 437, etc.). There are several instances of the same ID throughout the column. I would like to remove all but one instance of an ID so that I see what IDs I have. Is there a way to identify what IDs are there and then remove all but one of them?
I have a sheet where in one column, the word "high" will be repeated. I need to find these multiples and then list down in a seperate column the text relevant to each of those multiples (contained in an adjacent cell). I.e if A1 & A3 hold the text "high", take the corresponding values in b1 & B3 and list them in new column C (C1 & C2 continuing in sequential order). I have searched the forums but i can' t seem to find anything.
I have four different sheets with email addresses in an excel file. There are some email addresses which are present in more than one sheet. I wanted to find these email addresses and remove its multiple instances.
I can't use the simplified version of vlookup, as I can't enter each email address to see if there is another instance.
Is there a way where the excel sheet can search within itself for duplicate values and if not delete/at least point them out, so that I can delete them manually?
I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.
I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.
Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.
Sub SearchAllXLSFiles()
Dim lCount As Long Dim wbSource As Workbook Dim wbDestiny As Workbook
The problem - I'm trying to find a formula that will find text between 2 characters with unlimited instances in a single string and combine the found text to a single string. The character enclosing the text i'm after will likely be a % symbol. This formula would then be replicated down 5000-10000 rows.
eg. 1 - This is %an% example %sentence% to show what I %am% looking for eg. 2 - This %is another% example of what I %need%
Find Multiple instances of Numeric Criterion in Row & Return To a Single Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows. Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across each single Row in the Dynamic Range "Data" and have the Results returned to a New Sheet in a single column.
NEW Sheet: The Numeric Criterion is housed in G5. The matched criterion should be returned to the New Sheet starting at G7. Duplicate instances in the same Row should ALL be returned to the same cell in Column G on the New Sheet.
Sample Data Layout: Columns I J K L M N O P Q R Row No.76 1 0 1 1 0 1 1 1 0 1 Row No.77 2 2 3 2 1 2 2 0 0 0 Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario: Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet: Row No.7 Column G (Cell G7) 1111111 Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be returned to the same cell G7. In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it should be returned to cell G8.
I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel. About 1200 lines now, one line per person. I add a dozen or so lines (i.e. people) at a time in a different colour. When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps). Then I delete the double entries one by one. Pretty stoooopid, in'it?
How can I do this better, faster and more accurately with Excel to find just two duplicated data ( NAME and ID NUMBER) in a person's line (there are 15 columns altogether)? Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?
The goal here is to select the worksheets in one list box and transfer it to another. Multiple Selection is allowed. I am having 2 question i am struggling with.
Question 1 - When passing the sheet names from List Box 1 to List Box 2 (using Add button) i would like the selected items of List Box 1 to be deleted after transfer. I thought i could access this using the command below but it does not work properly -> error message.
Question 2 - When the user is done the ListBox 2 need to be stored. When I save then close my workbook and then reopen it, i would like to be able to access the selection that the user has made before closing the worksheet. How can I store the content of ListBox 2 to achieve such a result. I guess i could use a use a hidden Worksheet where i could store the selection into some cells but i would like to know if there is a pure VB solution.
i have a list of 5 teams in a named range ("teams") within a worksheet.
On a userform I have 5 combo boxes.
What I would like to have is an easy way to remove a used name in the list for the next combo box.
i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.
i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.
only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.
I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.
I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.
So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.
I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.
is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"
Why than going line by line on a document that has 60k rows!
I have a document with NAMES in column A and ROLES in column I. If the user has more than one role..their name will be listed in "A" for each role in "I". I'm trying to purge down a file that has 60k + rows. Lets say i want to see all the people and all their roles if they are in "PROGRAMMER_ANALYST" role...can this be done by macros? or formula?
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv() lastRow = Range("A1").End(xlDown).Row pmpt = InputBox(Prompt:="What text are you looking for?", _ Title:="Text", Default:="i.e.: Finished Goods Inventory") numrows = InputBox(Prompt:="How many rows to delete (counting original):", _ Title:="Number of Rows", Default:="i.e.: 1") Set cell = Range("A1:A" & lastRow). Find(pmpt) If Not cell Is Nothing Then For l = cell.Row To cell.Row + numrows Range("A" & l).EntireRow.delete Next l End If Do
I am trying to write a formula which finds the last instance of a number greater than 30 in a column (B). The values are not sorted from smallest to largest as they correspond to a time series (A) which needs to be preserved. Ultimately, I want the formula to return the time at which this value occurs, but I think I can do that bit using INDEX
I have a formula to give the first instance (row number) of a number above 30, which seems to work, but beacuse I don't fully understand how it works I can't modify it to give the last instance. The formula is:
=MATCH(1,INDEX(--(B2:B883>=30),0),0)
Having only just learnt how to use MATCH and INDEX, I thought each should have 3 arguments, so I'm confused about how the 4 arguments here work. Also I can't find much on using logic functions within INDEX. Can someone explain this formula, and suggest how I might go about finding the last instance please?
I'm trying to develop code that will perform the function in the title. I want to use an if statement that looks at cells across the worksheet and where it finds certain text it should colour the entire row. I would also like to be able to input the text via a user box. I don't necessarily want the code
In my workbook I have two tabs, the first tab is intended to call on data located on the second tab so I can evaluate & Display it in different ways. Here's what's worked so far. Where I'm stuck is attempts to try and combine the two.
- the second tab is named AW_Items_Import
Examples:
1) Looking on the second tab to count the number of times a object (identified on the first tab in Cell B13) appears =COUNTIF(AW_Items_Import!J:J,B13)
2) Looking on the second tab for items that fall within a set value range, the ranges specified on the first tab in cells C14 and E14
What I want to do, is combine 1) and 2) so I can isolate a search to a name specified on the first tab, THEN count the number of times that item falls within a set range, the range also specified on the first tab.
I have a large dataset with a company name in Column A and a Town in Column B, e.g.
ABC Company London ABC Company London XYX Company Swindon STJ Company Bristol STJ Company Bristol STJ Company Bristol DEF Company London DEF Company London DEF Company London
and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:
ABC Company London London ABC Company London London-1 XYX Company Swindon Swindon STJ Company Bristol Bristol STJ Company Bristol Bristol-1 STJ Company Bristol Bristol-2 DEF Company London London DEF Company London London-1 DEF Company London London-2
I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?
What I want to do today is, I want to get the extension (Type) of a file from the string. I have managed to get formula for that.
PHP Code:Â
=IFERROR(RIGHT(C12,LEN(C12)-FIND(".",C12)+1),"")Â
But the problem is, if the name of the file also have any dots, it is not giving correct result.
For Eg, if the string is ABCD_V1.0.mpp, I am getting 0.0.mpp not .mpp
NB: The file name may or may not have dots. So cannot tell that, it is always the second instance. Need to find the last dot and trip the things before that
Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious. But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence. One week a client may not appear at all, or they may appear from 1 to 6 times. I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.
I have an excel file with around 15 sheets. While i open it, i am getting 5 instances of the same file, that means after i open it , i could see 5 copies of the same file. If i make changes in one file, in others also its getting reflected.
how to label/define what I need so I'm not sure what needs to be provided...
I have two columns of data- phone number, city. Each row is an individual record of a call from that phone number to that city. And, I need to ID, for the eventual end goal of deleting, those phone numbers that called multiple cities.
I just installed excel 2007 and encounter a strange problem. When I open 2 or more excel instances from windows explore by double-clicking excel icons, those instances are not linkable, they seem absolutely independent to one another (in View/switch windows only sees one workbook). However when I open second file from within the first instance (Ctrl+O), I see 2 in the view/switch windows and it works fine.
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
What I'm trying to do is input a formula in col G which will look for instances of the city named in col F in both cols A and C. This should then return the total of these, from cols B and D that have the letter "F", into col H. Therefore, in the attached example, cell G2 would return "1", G3 would be "0" etc.
Should I be using VLOOKUP or COUNTIF, or maybe a combination of these or something totally different?CityCodeCount.xls
This is a good deal harder than my last issue. I have a few pieces of data:
A62:A80 - Array of Integers named X (2 through 20, to be exact) D62:D80 - Array of Strings named Y (on a Drop Down) AD45, AG45, AJ45 - Formula Holders
Now the tough part of explaining what I want... assume the string in question is "Pie": If Y contains "Pie" once, I want AD45 to take the value of the corresponding X. If Y contains "Pie" twice, I want AD45 to take the value of the first X, and AG45 to take the value of the second.
If Y contains "Pie" three times, I want AD45 to take the value of the first X, and AG45 to take the value of the second, and AJ45 to take the value of the third.
So, in the end, the formula holders will either have a number between 2 and 20, or be blank. Note that it is possible for users to put more than 3 occurrences in Y even if it's user error.
I'm not really asking for the full formulas, but I simply don't know where to start on this, on what functions would be useful in pulling out the values I want.
One idea I had is that:
AD45 searches the array for "Pie" and takes the first value of "X" that matches the find. Blank if it doesn't.
AG45 checks if AD45 is not blank, and searches the array for "Pie" starting at the next index.
AJ45 checks if AG45 is not blank, and searches the array for "Pie" starting at the next index.
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for "A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.
If from a macro I open "workbook A" from within "workbook B" then "workbook A’s" VBA project remains in memory and in the VBA project explorer. If I then run the macro again I have 2 instances of workbooks A VBA project showing, and so on. I read on this forum that if you have a public variable that links the workbooks then this is the reason but I have created a test workbook with the following code
Sub test() Workbooks.Open ("C:Path Data.xls") Workbooks("Path Data.xls").Close SaveChanges:=False End Sub
If I run the macro test the workbook opens and then closes but its project remains open. How do I remove the projects when I close a workbook?
I have 2 spreadsheets with many rows (in the tens of thousands) and columns (about 20 or so) of data.
I am trying to take the ID# from one spreadsheet and append it to the other, based on a name match. Typically I would use a vlookup, but, in this instance, most companies appear more than once and they often have many different ID#s associated with them. I am looking to return each unique ID# that is associated with each company, and display them horizontally next to that company's name.
Since this is including a lot of private data, I have included a small, generic example of what I am looking to search off of and output. Sample Spreadsheet.xlsx
I have seen examples of how to return multiple values using index functions, but, they all seem to only be able to handle one name at a time, and it displays the values vertically from that. Since I am trying to do this for thousands of rows of data, this won't work for me. I need to be able to have a function/formula of some sort that I can apply to each and every of the thousands of rows of data simultaneously. And, on top of that, I'd like to be able to display the 2nd, 3rd, etc ID#s for each company in additional columns of data.
