Let's say I have a value (N) in Cell A1 of Sheet 2 in Workbook A.
What I want to do is to Find the row number of the first cell and last cell that contains the value (N) in Sheet 1 of WorkBook B.
I believe this rightmost column number information is available. I just could not find it!
View 3 Replies View RelatedI have a worksheet with about 20 columns of info. First of which is a primary key. Call it "Job Number." I would like to create a macro that runs down the list of primary keys searching for a specific Job Number I ask for in the new worksheet, in A1. It then copies and pastes the entire row where it found that job number into the new worksheet starting at row 6.
View 7 Replies View RelatedI have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories:
Requirement: 10358
Title: Customer requirement 1
Text: This describes the requirements for a product for the customer[code].....
The text of the categories may begin in column A or B
What I need is a macro that will search each worksheet for a category, e.g., "Configuration:", copy the row where the keyword "Configuration" is found, and then have that row transposed and pasted to another WorkSheet (e.g., "Extracted Data") cell.
Data extracted from the next Worksheet would begin a new row in "Extracted Data"
Example:
Requirement1 Title Text Verification Method-Level ...
Requirement2 Title Text Verification Method-Level ...
Requirement3 Title Text Verification Method-Level ...
The Requirement# is best extracted from the WorkSheet tab since some of the worksheets are missing this information.
I can provide an example spreadsheet, however, I was not able to figure out how do that in this post.
I found a thread similar to this problem:
find and copy row
However, it only finds, copies and pastes for one keyword.
I am looking for a solution to an otherwise very tedious problem.
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories: ...
I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
Items in Column A1 are calculated by (B2/4+5)*1.4 Items located under the columns 2000, 3000, 4000, etc... 10,000 are calculated by taking the top number, eg 2000/(A1 cell value)+the column B number. 2000/7+0 = 286 (rounded numbers)
I need to find an way to look up for x number (2000,3000,4000, etc...) find the smallest number in that column and then return the value in column A1.
Cell
A1 Number >2000300040005000600070008000900010000
70 2864295717148571000114312861429
84 24236148059971883795610751194
[Code].....
Starting with one worksheet with end date as one of the columns. I filtered the worksheet with end date > (certain date) and copied the results to a second work sheet.
The original worksheet has all courses with end dates from 1998 to 2009. The second worksheet has all courses with end dates > 2004.
How do I find out which courses have end < 2004. But do not also have an end date > 2004.
What I am trying to do is determine which courses are not longer being used since 2004.
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies View RelatedI have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
View 1 Replies View RelatedI have attached an example. If I have a set of numbers such as the one attached, is it possible to create a formula that will show me all the combinations of numbers that add up to 55.52? In the attached I have highlighted in different colours all the number combinations that add up to 55.52. The numbers highlighted in blue appear within more then one combination. Is there a formula that can do this for me, instead of randomly adding numbers hoping they add up to 55.52.
examples.xlsx‎
I need a VBA code to find the nearest biggest number and nearest lowest number between the data of D2 to H2. In the attached file, I have mentioned my required output (Column A and B - blue highlighted)
View 2 Replies View Relatedto update these values via a form in this sheet. I can find the correct row to be edited by entering a value from column A and B. The problem is if I want display the values of that row first and then change it. If I want to change row 10 data how can I bring back the value in ROW 3 AND THE COLUMN VALUE? The next step would be to do the actual update if I want to change ROW 10 to "Ooi" and a sales value of 200?
This is what I have done so far:
Dim myRows As Integer
With Sheets("Mrt")
'Retrieve history information for row
For myRows = 4 To 49
If comboxDay.Text = Range("A" & myRows).Value And textboxdescription.Text = Range("B" & myRows).Value Then
textboxbedrag.Text = Range("C" & myRows).Value
chkBTW_Ja.Value = Range("D" & myRows).Value
txtNota.Text = Range("S" & myRows).Value
End If
Next
End With
Picture attached to show how sheet looks like.
I work for a store and we're migrating to a new Point of Sale system. The new POS uses the entire UPC code while our existing one uses only partial UPCs, so I have to update from the partial to the full. ex. Partial 3378093 - Full 0033780935
The partial UPC will always be included in the full UPC, the difference is the dropped leading zeros (and possibly other numbers) and the check digit number at the end. What I have is a spreadsheet with with two worksheets (Old, New) - one has our old UPCs and other information, the other has the new, full UPCs and more information we'll need. What I want to do is write a function that will search through the full list of new UPCs to find the one with the old UPC inside it and return the full UPC. InStr seems the right function to do this - correct?
The part I'm having trouble with is writing something that will search through the full column of new UPCs using InStr. I've searched through the forum for a similar situation but the few I've found aren't working or appropriate. I don't want to include any code I've written because 1) it doesn't work and 2) it would be way too embarassing!
With reference to the attached workbook, I need to write a VBA code which will look up the value in cell T4 from worksheet "tdploader" on the "cashbalance" sheet and return the date in row 1 based on which column the value is found e.g. H1 in the attached workbook example. The date should be returned back to cell P4 on the "tdploader" sheet. I have tried to use the Find VBA formula to no avail.
View 1 Replies View RelatedI have a workbook with two sheets. I have one macro that saves the text of the active cell in sheet 1 for later use, the macro then switches to sheet 2 and using the find function I want it to search for the variable I just setup but I keep getting run-time error 91.
Here is what I have:
[Code] ......
I get the run-time error 91 with the Find lines of code yellow and the arrow pointing at the last row. I've tried all kinds of variations (with and without Activate) of the find function and as long as it is searching in sheet2 for the variable that was made in sheet1 it will NOT work. If I do this same search on Sheet1 I get no error. Also if I change the string name in Find to the same exact text just with quotes on it it will find that text without problem in Sheet2. But if I use a string I get an error.
Once I get this to work I want to save the address where the string is found in Sheet2 and use that address later as well so how to get an address from a Find result.
Should be an easy fix for experts, but I'm not one of those...
I'm trying to create a "scoresheet" in Excel where a racer has a name and an ID number on the main tally worksheet, with columns relating to their scores for each race. Subsequent worksheets have results from each race (one race per sheet), with the individual referenced by their ID number.
How do I have the main tally sheet "find" that person's score for each event in separate columns so that it can add up their totals? I tried an IF formula to find each score in the subsequent sheets, but that didn't work.
Is there a simple formula to do this? I've been achieving the results by importing the worksheets into a Filemaker database (where the ID number "matches" and updates each score), but that's a pain, and I'd rather do it all in Excel.
I have two worksheets to track students registering for one day workshops, which are offered monthly. In my first worksheet called 'CourseCapacity' I track the number of students that can register for each workshop. It looks something like this:
Col A - "Title"
Workshop101
Workshop102
Workshop103
Col B - "Capacity"
5
10
7
My second worksheet tracks the current number of students registered for each workshop for each month. What I want to do is read the course capacity from the first worksheet and display it for the monthly offering of each workshop.
Col A - "Title"
Workshop101 - 1/23/2010
Workshop101 - 2/20/2010......................
I need to do a find and replace on cell E13 on every worksheet in the workbook - a several hundred pages. The sheets are individually named not Sheet1, Sheet2......
First of all I need to find say "oldchippy" (without quotes) in cell E13 and if its there replace it with "oldchippy2", if its not there leave the contents as it is.
I’m having trouble trying to get a form, once activated;
- to use a specified cell value (offset from the target cell),
- to search another sheet for that value,
- then return values, (offset from the find value),
- to a series of textboxes.
Later, if the user if edits any of the textboxes, to perform the same procedure, pasting those values in the appropriate cells.
I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.
Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.
I need a formula that will look up to find a specified date on another worksheet, and input the data in the cell that is 4 columns to the left of it. Can this be done?
View 9 Replies View RelatedI want to find a text "Style#" in a worksheet and return the value next to "Style#". The value is supposed to be returned in, say, column H, for each row I found the text "Style#".
Below is the sample:....
I have a 4 tab worksheet containing Control, RawData, F, Dan. I need to know how to copy calculated values for one specific customer from the RawData tab to certian locations on the Dan tab matching the customer number on RawData tab.
RawData Tab colum (V) cells (2-99) will always contain one customer value at a time, and the calculated values will always be in cells E100, H100, and H101, but I need to copy the data in specific cells to the row/cells that matches the customer number on the Dan tab that contains all my assigned customers.
for instance if i'm pulling a report for customer 1-222-222 I need to know how to place the calculated values from the RawData tab to the row designated for customer 1-222-222 on the Dan tab in the required cells.
I have two sheets, sheet1 and sheet2. I would like it so that every time I click on the tab of sheet2 cell A1 of that sheet displays the row number of the last line in sheet1 that has the value "COST" inside column A, what would the coding look like?
View 9 Replies View RelatedI have an excel file, which contains following data:
Month
Department 1
Wages 10
Expenses 20
Preparations 15
Depreciations 3
All other 7
Transport 10
Total 65
Department 2
is there an easy way to find for example the transport cost of department 2 (=14)?
I've taken some time to learn about building an excel data entry form. I'm looking to add a find button.
1) users would type in something in the ID text box, then click on "find". The code will then try to find that record in the worksheet(just sheet1).
when it is found, i want the forms to auto-populate values from that row.
right now, the search works except when you are trying to search a value that that is NOT in the worksheet. I'm getting an error message that i've never seen before.
[Code] .....
form based input.xlsm‎
1. Find a text "State" in column 5 of a active sheet and copy that particular row to a new worksheet "Summary" including the header row.
2. Loop through other worksheets and add results to "Summary worksheet" without the header row.
I would like to put together a macro that would compare a list of part numbers against a database, and recommend alternative vendors for the part. The actual VBA I think I can handle, my problem is with the formula, array or whatever is needed to return the results I am looking for.
I have attached a sample workbook to this message that contains two worksheets. The "Sample Database" is an example of the format of the database (in Excel format) that I will be looking against. This data may be a hundred columns wide, and thousands of rows long.
The "Vendor Reference" worksheet shows an example of the data that would be returned if the "perfect" formula were entered into the cells. (I'll explain what I mean by "perfect" in a moment.) It also has an area designated for testing formulas to see what is returned. It's blank, because none of my tests returned anything but errors.
You will notice the column headings on the "Sample Database" worksheet. Columns A and B will always contain the part number and product description. The remaining columns will contain vendor specific data, with perhaps 25 different vendors, and 3 columns per vendor. These columns would be "PV", (for Primary Vendor), the vendor number, (010299, for example), and the Vend Part#.
If there is an asterisk, "*", under the "PV" column for that vendor, that means they are the "Primary" vendor. The cost from that vendor is listed under each vendor number. There may be instances where 2 different vendors are listed as primary. If that's the case, the formula can simply return the FIRST one found.
Here's what the "perfect" formula would return: .....
I need to find ALL occurrences of a date on a worksheet, but could not find a macro on our forum.
I found this macro on the web.