Is it possible to do more than one search simultaneously. Let me elaborate. I have a data of 40,000 students in DATA A and searches each student in DATA B which is also around 40,000 students. Instead of doing a For Next Statement, searching one student at a time, is it possible for it to search all the students simulatenosly because searching 40,000 students in a data base of 40,000 students takes FOREVER?
View 9 Replies
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
I have approx. 50 Defined Name and which were divided into 2 parts, i.e "name.2012" & "name.2013" means each files had ".2012" or ".2013" but the issue is I want to change or replace ".2012" or ".2013" with any other name or number in simultaneously. Just like inside excel sheets, Find and Replace method.
View 2 Replies View RelatedI use a two monitor setup driven from a Radeon 9550 graphics card, but the problem I have occurs on a single monitor as well. When I open multiple workbooks or worksheets I can only view one at a time the other remains embedded behind the open window. Nothing I know of will let me view both at the same time.
If there are two open worksheets full size on the screen and I minimize one they both minimize. If I decide to close one workbook/sheet down all open workbooks/sheets close down as well. Can I view two workbooks at the same time? Can I close one down while keeping one open?
I'm trying to sort selected rows by two columns, but simultaneously instead of sorting by "Column A and THEN Column B". In my case, this is numerical data where there is ONLY data in one column or the other, never both.
For example:
5 _
10 _
_ 1
_ 6
_ 4
Should sort to:
_ 1
_ 4
5 _
_ 6
10 _
freezing columns and rows it does not seem possible to freeze MULTIPLE rows AND MULTIPLE columns simultaneously in the same spreadsheet.
In my spreadsheet I would like to freeze columns A, B, C as well as rows 1 - 8.
Is it possible to make a copy of a chart that is edited simultaneously as when you edit the original chart...somewhere else in the same workbook.
View 1 Replies View RelatedI am trying to run a loop on a few columns simultaneously, from the last row to the top (well, until the 2nd row).
So I have a few columns of data, A through I, and the number of rows they populate changes all the time.
So how would I run a loop that will check for the following:
If a certain row in Column F is greater than the previous row in column F by 1.5times, then in that same row, if number in Column B is greater than number in E, the code should put the the value of E in a new column, lets say M (in the same row)
I know that sounds very confusing, but is that even possible? I am playing with loops, and I can only do very simple loops where I define a range, like
Spreadsheet is made up of Col A - Col AK:
Col A = Purchase Date
The info is continuous:
Row1 12/1/06
Row2 1/2/06
Row3 3/5/07
I need monthly totals to display simultaneously. Do not want to use Subtotals option.
I am installing over 1000 phones for a business. I was provided a list created in excel (not by me) of all of the phones with their locations and phone numbers. Upon successful installation I simply search the spreadsheet for the phone number and place an "X" in the completed column. I do know how to filter the spreadsheet to print only those with an "X" that I submit weekly to show progress.
Now I am given a separate list of specific phones that someone wants completed by a certain date. I have copied this list to " Sheet 2" of the spread sheet. Is there anyway I can have exel mark and "X" in the completed column on Sheet 2 when I update Sheet 1? Can I update the list on sheet 2 with those that are already "X"ed as complete on Sheet 1?
I have a workbook of 6 Sheets, what i want is that if i scrolled or zoomed one of the sheets i want all other sheets to be scrolled and zoomed accordingly identically.
to explain more: say I'm on "sheet1" and I scrolled down so i'm seeing range(K225:X250), now if i switched to any of the other sheets i want them all scrolled to the same range. so whatever sheets i choose i want to see range(K225:X250) in the same viewing properties "zooming".
there are two colums, one for product name (unique names) and other one is for their prices (may be same for more then one product). I want to arrange these columns by the their price vlaues in assesnding/desending order and to have their unique corresponding product names in respective columns. Provide me any solution, probably using any excel formulas. presently i have tried this using Large and Vlookup funstions...but it fails.. when there are same price is available for two product names, then it takes only first one. I WANT TO GET THIS DONE AUTOMATICALLY, AS THIS IS TO BE POPULATED ON SOME OTHER SHEET AS A PARETO. waiting for your vlauable responces ASAP.
View 2 Replies View RelatedI have 31 identical spreadsheets in one workbook. Is there a way to apply Freeze Pane to all 31 sheets simultaneously?
View 4 Replies View RelatedI have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
I need to copy information from one excel file and paste it into the same location (A1-A1, B6-B6, etc) in many other files all at once. Essentially I have several thousand files that need to be updated right now. I need to copy and paste both the cell contents and the formatting.
View 1 Replies View RelatedI am trying to run 2 for each loops inside another loop, and I was wondering if there is a way to move to the next value for all loops at the same time.
So when it reaches "Next starting, final, scal", all ranges move to the next cell before performing the formula.
I am trying to use Vlookup and match on a large volume of data. The Vlookup true function was having trouble when an exact match occurred. I changed the table to allow for all options (sizes in this case) but the Vlookup with false came up with #N/A. The Vlookup key was the result of a formula. I then typed the same data and the formula was successful. I then copied and pasted Value the entire contents of the cell. I still received the #N/A. I then pressed [F2] to edit the cell and then pressed enter and this was successful. I have over 60,000 cells to edit and can not do manually. Any macro run the [F2] function on a range of cells or another way.
View 9 Replies View RelatedI have a workbook with a list of drawing numbers (with customer, date, author etc.). New drawing numbers and their associated row of information are entered onto the sheet...
..HOWEVER, multiple users can be at least viewing and potentially entering information simultaneously.
I have written code where multiple people can view the same read only file, but not edit it and it be updated.
It seems quite unlikely, but is there anyway of 'live' updating a workbook whilst multiple people are viewing it? Or at least notifying other users when the sheet has been updated?
I am working on a document at work with multiple worksheets. It would be most useful for me to be able to split the screen so that I can work on both worksheets at the same time without (a) having to switch back and forth, (b) modifying the workbook so that everything is on one sheet, or (c) opening the file twice and sizing the windows to my screen.
My goal is to be able to see the information on each sheet while modifying one sheet or the other.
See the file attached (there are the same columns on all sheets, apart from on "BLANK"). The "Sort" sheets 1-4 contain rows that are repeated various times on the "Raw data" sheets. The "Sort" sheets are mutually exclusive; there is no repetition of rows among them. When I make an entry into an "ACTION" or "REASON" cell on any of the "Sort" sheets, I want that entry to be replicated on all identical rows throughout the "Raw data" sheets. Matching of the rows is to be via Criteria 1 and 2. I would preferably like the replication to be as I make the entries.
I am currently looking into writing a private sub for each "Source" worksheet. This would activate when a change was made to that sheet's ACTION and/or REASON cells, group that sheet with the three "Raw data" ones and change the relevant cells/rows (assuming that finding the relevant cells/rows within this function is possible). Or the macro could just loop through the rows of the three "Raw data" sheets and copy IF, but this would be long. Does this sound like the way forward or am I barking up the wrong tree? Should I be looking into VLOOKUP or pivot tables? Or is my data structured badly in the workbook from the outset? How straightforward will this macro be? Has anyone any pointers, magic routines in their libraries or links to relevant info?
I am posting this thread as a follow up to the previously posted thread entitled "IF Function and circular Reference". The reason I am changing the title because the topic we ended up talking about was not reflecting the previous title. My problem is that I got a range of independant cells (M7:M70) and the same number of dependant cells (BA7:BA70) as can be seen in the attached file. What I am trying to do is to make solver to find the value in the independant cell (e.g. M7) that makes the value in the adjacent (i.e. same row) dependant cell (e.g. BA7) equals 0. I can use solver for one row at a time but not for the whole range of cells. The question now is how can I use solver to do this task for the whole range M7:M70 simultaneously?
View 5 Replies View RelatedThere are 4 lists containing accounting data and within each list the data rows beneath the headers are named ranges.
The parent input list is on the Payments worksheet with headings in A3:R3 and the data rows underneath are a named range entitled Payments.
The child lists contain formulas to pull the required information from the parent Payments list. These lists are identically sized and headed with headers in A1:K1. The data rows underneath the headings are named ranges entited Debits, Credits and VAT. The worksheets on which each resides have the same names.
I am looking for a way to automatically contract / expand the child lists as each row is added / deleted from the parent Payments list, ie so each list always contains the same number of rows.
The formulas already within the child lists will then ensure that the apprpriate data is copies to them from the parent Payments list.
the named range Payments will always contain at least a single row to ensure the retention of the required formulas and validation.
Note also please that there is a macro which automatically resizes the range Payments to a single row when a command button is pressed.
I have a workbook with 20 worksheets. All tabs are identical in format and layout. On B3, I have a drop down list (this may be irrelevant, but it's to select one month of the year) using simple data validation. Now, I initially had a formula in B3 equal to B3 of the first sheet, so that if I changed the month in the first sheet it will change on all sheets, it will do it the first time without touching the drop down lists on the subsequent sheets.
However, if I changed the date on any other sheet other than the first sheet, it will erase my formula and replace with the value I selected from the drop down list.
Can you guys please help me to device a way (code or formula; open to anything) where if I select, say September, on ANY sheet, it will change all the others to that selection (September) as well?
PS: B3 is actually a merged set of cell merging cells B3:D3. I know this is important because code and merged cell don't really mix.
I am sure that most of you will probable know what i am doing wrong here straight away so rather than me waste another 1/2 hr stuffing around. I am trying to find count the number of times two events occur simultaneously.
=SUMPRODUCT(--('5. Financial'!AX4:AX134=A5)*('5. Financial'!BQ4:BQ134>0<0.1))
The first part is fine I think but in the second part I am trying times only when cell is between zero & 10%.
I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).
delete
EFGHIJKLMNOPQ8BallFrqPct
BallFrqPct
[Code]....
how does one use a single scrollbar control to change the scroll position of two textboxes simultaneously?
The reason why this is required is because the textboxes are both multilines and the data in each is related. So there isn't much point in the user scrolling through textbox1 and then trying to find the same scroll position in textbox2 by changing it's scrollbar.
I've tried changing the Curline property of the textboxes within the scrollbar change event but this is obviously a poor method as it involves changing the focus every time the user scrolls...
Is there any way to do this part of code without calling out each element individually?:
VB:
Dim aSequence(7, 1) As Integer
'setup Evaluation sequence
aSequence(0, 0) = -1
[Code]......
My Workbook contains several UserForms in which employees will be entering hours and activities on a daily basis. In the UserForm, they select their department and enter hours for each specific type of work/activity that shows up for that department in the userform. They click the submit button and all the data (date, name, department, type of work and hours) is added to a database table in another sheet, which in turn feeds a number of reports.
I need to be able to have anywhere from 20 to 100+ employees enter their hours on a daily basis, many of them at the same time, all from different locations (within the network of the client's system), using the same UserForms.
We've experimented with Google Drive, but that seems not to be the solution. It kind of works, but is not secure (it installs a copy of the entire workbook on the users system and then synchs the data back to the server upon "save") and way too cumbersome. I've heard of SharePoint, but not every client has that installed (and frankly I don't know how it works).
I have several hundred text files which I would like to reformat and save as excel without needing to open each file individually.
I'm entirely new to working with macros, so, I was able to use macro recorder to save the steps to reformat the file, but would like setting up the part of the macro to automatically open and reformat all of the files.
I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).
In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client
(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)
I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.
Is there any way to do it, without re-entering the formula to each one?
